CODEWITHSUNDEEP
excelvba
ZerOne
ZerOne

Reputation: 1326

VBA trigger change of cell

I created a dropdown list like in this instruction. Now this cell needs to be triggered in my macro.

I already read some of the other entries to this topic, but in my VBA excel macro this code doesn't work:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$B$2" Then
    Call Macro1
  End If
End Sub

Sub Macro1()
  MsgBox "The value entered in Range B2 is " & Range("B2").Value
End Sub

If I change the value of the cell B2, this code doesn't get executed and no messagebox will be displayed.

Upvotes: 0

Views: 1170

Answers (3)

user6432984
user6432984

Reputation:

If you plan on changing any range values make sure and toggle EnableEvents. If you don't you risk recursively firing Worksheet_Change and crashing your project.

Here is a common design pattern

  • Turn off EnableEvents
  • Check if Target (cell that was changed) is in the range that you are validating
  • Pass Target as a parameter to another sub routine
  • Turn on EnableEvents
Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False

    If Not Intersect(Target, Range("B:B")) Is Nothing Then
        Macro1 Target
    End If

    Application.EnableEvents = True
End Sub

Sub Macro1(Target As Range)
  MsgBox "The value entered in Range " & Target.Address(True, True) & " is " & Target.value, vbInformation, "You Entered"
End Sub

Upvotes: 0

Hello World
Hello World

Reputation: 308

In addition to the above answer, if this is a merged cell and b2 isn't the first cell in the merge, this also won't work. However if that isn't the case and your code is in the right place, it's working fine

Upvotes: 1

DeanOC
DeanOC

Reputation: 7262

I copied your code and put it into the sheet that I was editing and it works fine. If you try to put this code somewhere else, e.g. into ThisWorkbook, then it won't work because the eventhandler Worksheet_Change won't fire.

Upvotes: 1

Related Questions