Event Triggered by Selecting Any Cell in Workbook

[Sean]

Hello:

I have a workbook that creates new named sheets as part of it's function. I need an event that will run a sub whenever any cell in the workbook is selected.

If you can help me write some code that would trigger an event that would include these dynamically created sheets, that would be an option that would solve the problem as well.

Thanks in advance!

 

[Hui]

Sean

You should be able to use the WorksheetSelectionChange event

Sub Worksheet_SelectionChange(ByVal Target As Range)
' Your code here
End Sub

 

[NARAYANK991]

Hi Sean ,

In case you wish to have a procedure which will run on even the newly created sheets , then the solution would be to use the following event procedure :

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
' your code will come here
End Sub

since this involves inserting the procedure only once , in the Workbook section.

Using the procedure given by Hui will work provided you insert that procedure in every worksheet in the workbook.

Narayan

 

[Sean]

Hi Sean ,

In case you wish to have a procedure which will run on even the newly created sheets , then the solution would be to use the following event procedure :

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
' your code will come here
End Sub

since this involves inserting the procedure only once , in the Workbook section.

Using the procedure given by Hui will work provided you insert that procedure in every worksheet in the workbook.

Narayan

I think this is on the right track--my goal is to fire the code when a certain cell is selected on a sheet--such as:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
With ActiveSheet
If ActiveCell.Value="See Goals" Then
MsgBox("Test Complete")
End If
End With
End Sub

In this attempt, when the sheet is selected I only get success when the cell is already selected--If I select the sheet THEN select the desired cell the event has already fired--Maybe there is a different way to write the IF statement??

 

[NARAYANK991]

Hi Sean ,

I have no idea of how your data is laid out , but something like this should work :

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
            If Target = "See Goals" Then
              MsgBox ("Test Complete")
            End If
End Sub

Remember , when you have the activesheet available as a parameter named Sh , and the activecell available as a parameter named Target , use them.

In this case , since the sheet itself is not relevant , we can use just Target.

Narayan

 

[Sean]

Hi Sean ,

I have no idea of how your data is laid out , but something like this should work :

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
            If Target = "See Goals" Then
              MsgBox ("Test Complete")
            End If
End Sub

Remember , when you have the activesheet available as a parameter named Sh , and the activecell available as a parameter named Target , use them.

In this case , since the sheet itself is not relevant , we can use just Target.

Narayan

Thanks! This works perfectly--

 

[jeffreyweir]

Sean, a more robust and generalised way of triggering a macro based on a particular cell being selecteid is to give the cell in question a name (i.e. assign a named range to it), and use this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("SomeNamedRange")) Is Nothing Then
    MsgBox "Test Complete"
End If
End Sub

This has the advantage that you can change the text in SomeNamedRange to whatever you want, and the code will still work. Plus it lets you assign macros to trigger when say a particular cell is selected that is blank.

Note that instead of using this:
Range("SomeNamedRange")
...I often use VBA's shorthand method of referring to ranges:
[SomeNamedRange]

...which would make the above code look like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, [SomeNamedRange]) Is Nothing Then
    MsgBox "Test Complete"
End If
End Sub