Trigger Macro By Slecting Cell

[MikeG]

I know how to trigger a macro when a Cell's value changes, but can a macro be triggered simply by selection of a cell? If so, what is the code?

Thanks,

MikeG

 

[Datsmart]

Here is an example of VBA running on a particular cell change:
http://www.mrexcel.com/forum/showthread.php?t=521405

Input your code into the Worksheet Module of the Worksheet you are working with.

Right click sheet tab
Click "View Code"
Paste the code into the panel that opens. ("WorkSheet" Module)
Alt-Q to quit the VBA Editor.

 

[jasonb75]

Just change the procedure name

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

use Intersect or similar to define ranges if needed, same as you would with a value change.

 

[MikeG]

Here is an example of VBA running on a particular cell change:
http://www.mrexcel.com/forum/showthread.php?t=521405

Input your code into the Worksheet Module of the Worksheet you are working with.

Right click sheet tab
Click "View Code"
Paste the code into the panel that opens. ("WorkSheet" Module)
Alt-Q to quit the VBA Editor.

Thanks - but I want the macro to trigger as soon as a cell is selected - not when the value in it is changed.

MikeG

 

[MikeG]

Just change the procedure name

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

use Intersect or similar to define ranges if needed, same as you would with a value change.

Thanks jason and datsmart. Got it running.

Mike

 

[Kenneth Hobson]

The quoted link is fine. Just select your Selection Change event as suggested.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Target.Count > 1 Then Exit Sub
  If Intersect(Target, Range("D4:D79")) Is Nothing Then Exit Sub
  Application.EnableEvents = False
  If vbYes = MsgBox("Current Value: " & Target.Value & vbCrLf & _
    "Click Yes to increase to: " & Target.Value + 1, vbYesNo + vbInformation, _
    "Increase?") Then Target.Value = Target.Value + 1
  Application.EnableEvents = True
End Sub

 

[MikeG]

One follow up.

Is it possible to specify this to work if any cell in a range of cells is selected? For example, I tried this and it did not work:

If Target.Address(False, False) = "C8:C30" Then

Does it only work for one cell?

Thanks

 

[Datsmart]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
'Only look at single cell changes
      If Target.Count > 1 Then Exit Sub
    'Set the range to trigger the InputBox
     Set rng = Range("C8:C30")
    'Only look at that range
    If Intersect(Target, rng) Is Nothing Then Exit Sub
         'Your code here...
End Sub

 

[Kenneth Hobson]

Use Intersect.

Even my code exit if more than one cell in the intersection is selected. That can be modified if needed to iterate over the intersected range.

 

[MikeG]

Thanks to both of you.

MikeG