Vba experts: Executing a macro when certain cells are selected

[bamaisgreat]

Is there away to execute a macro after selecting a few certain cells???

 

[Andrew Poulsom]

Yes, you can use the Worksheet_SelectionChange event procedure in the module for the worksheet? Which cells do you want to use and what macro do you want to run?

 

[jdsouza]

You may want to set a flag to true as soon as the pointer enters the Worksheet_SelectionChange event and set it to false when your macro has completed doing its thing.

In a module enter
Public fRecursive as Boolean

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Target.Address = "$J$15,$K$15:$K$16,$L$20" Then
fRecursive = True
' Rest of code here or call another sub


End If
fRecursive = False
End Sub

 

[Andrew Poulsom]

You may want to set a flag to true as soon as the pointer enters the Worksheet_SelectionChange event and set it to false when your macro has completed doing its thing.

In a module enter
Public fRecursive as Boolean

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Target.Address = "$J$15,$K$15:$K$16,$L$20" Then
fRecursive = True
' Rest of code here or call another sub


End If
fRecursive = False
End Sub

What is the purpose of that boolean variable? Nowhere do you test whether it is True of False.

 

[bamaisgreat]

I would like when I select A4, J10, S13. Regardless if the cells have text in them or not. Thanks macro is finalizing_by_qc

 

[Andrew Poulsom]

Try:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Address = "$A$4,$J$10,$S$13" Then
        Call finalizing_by_qc
    End If
End Sub

 

[jdsouza]

Yes, missed out the

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
if fRecursive then
exit sub
end if


thanks!

 

[bamaisgreat]

Is it possible to have it were if they select say b4 instead of A4,J10,S13 it will not run unless they select them in that sequence

 

[Andrew Poulsom]

The event won't fire if B4 is selected. It will only fire if they select A4,J10,S13 in sequence.

 

[bamaisgreat]

awesome,, Thanks Andrew