Forum Discussion

Anthony Smith's avatar
Anthony Smith
Brass Contributor
Oct 04, 2018

Excel VBA Worksheet Change Event Causes Message Box to Appear Multiple Times

Hello,

 

The below code checks to ensure a certain cell has a value before changing the color back to the sheet's original color. By changing the color from yellow to the sheet's original color, the user is able to print. The issue is that once a value is entered, the message box keeps appearing when anything else on the form is changed. Should the message box be placed outside of the worksheet change event? I am new to programming so any help is appreciated!

 

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Unprotect Password:="Anthem"
  If Range("G50").Value <> "" Then    
        MsgBox "You may now print"    
        Range("G50").Interior.Color = RGB(221, 235, 247)
  End If
ActiveSheet.Protect Password:="Anthem", AllowFormattingRows:=True
End Sub

Thanks,

Anthony 

developer
excel
Macros and VBA

1 Reply

  • Lorenzo Kim's avatar
    Lorenzo Kim
    Bronze Contributor
    Oct 05, 2018
    the event change is detecting any alteration made in a sheet. so every time you make a change - the msgbox will pop up no matter where you place the code. if you delete it, then the only change will be the color.

Resources