CODEWITHSUNDEEP
vbaexcel
Bill Greer
Bill Greer

Reputation: 3156

Worksheet Selection Change is not firing

I have an Excel Macro enabled file. I have 3 methods / functions in my Module1.

  1. Worksheet_SelectionChange
  2. Button1_Click
  3. MyMacro

Button1 fires when I click on my button in the worksheet. MyMacro fires when called from the Button1_Click. My Worksheet_SelectionChange method does not fire when I change cells or when I change worksheets. I would like a method that fires when I exit a cell. I jave tried various samples from the web. They all have a Worksheet_SelectionChange method in their samples. What do I have to do to trigger a method when I leave a cell? Here is my code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = True
MsgBox "TEST"
End Sub

Sub Button1_Click()
BR = ActiveSheet.Buttons(Application.Caller).TopLeftCell.Row
COL = ActiveSheet.Buttons(Application.Caller).TopLeftCell.Column
Dim FileName As String
FileName = Worksheets("942").Range("A" + CStr(BR)).Value
FileName = FileName & "_"
Dim RandomNumber As Integer
RandomNumber = Int((999 - 100 + 1) * Rnd + 100)
Worksheets("942").Range("J" + CStr(BR)).Value = FileName
MyMacro
End Sub


Private Sub MyMacro()
MsgBox " I just left A2", 0, ""
End Sub

Upvotes: 1

Views: 1576

Answers (1)

Ralph
Ralph

Reputation: 9434

You'll have to move the Worksheet_SelectionChange sub to the worksheet (module). Just right-click on the tab and select View Code this is where it should be.

That's because the Worksheet_SelectionChange can be different for each sheet. By placing it on a particular sheet Excel knows when the even should fire.

enter image description here

Upvotes: 2

Related Questions