Run macro on cell selection.

Jay59 · #1 (**permalink**) 03-01-07, 17:25

How do I run a macro when a certain cell is selected. FYI there are 20 sheets with 3-4 cells on each that will need this macro.

shades · #2 (**permalink**) 03-01-07, 22:47

You can use a module in the Worksheet "SelectionChange". Right click on the worksheet and choose "View Code". Paste this code into the VBE. Do this for each worksheet where you need it done.

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Set rng = Intersect(Target, Range("B3"))
    If rng is Nothing Then
        Exit Sub
    Else
        Dim cl as Range
        For Each cl In rng
           .....
        Next cl
    End If
End Sub

Instead of having just Range("B3"), you could use Union of several cells, and then it would be combined into one.

Jay59 · #3 (**permalink**) 03-02-07, 11:54

For some reason I couldn't get your code to work. But from looking at your and researching other options I came up with this.

Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Cells.Count = 1 Then
        If Target.Address = "$L$1" Then
            Macro
        ElseIf Target.Address = "$L$2" Then
            Macro
        ElseIf Target.Address = "$L$3" Then
            Macro
        End If
    Else
    End If
End Sub

shades · #4 (**permalink**) 03-02-07, 12:58

Mine was just an example, and notice I didn't have any actions listed, just the three dots to indicate action code to go there.

As long as yours works, that's what counts. Glad it did what you want.

norie · #5 (**permalink**) 03-03-07, 08:56

If you want this to work on multiple sheets you would probably be better of using a workbook event, eg SheetSelectionChange.

Code:

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

End Sub

Note it's passed two parameters Sh and Target.

Sh is the sheet that the selection change has happened and Target is the new selection.

subu.ns · #6 (**permalink**) 02-20-12, 10:58

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 1 Then
ThisRow = Target.Row
If Target.Value > 100 Then
Range("B" & ThisRow).Interior.ColorIndex = 3
Else
Range("B" & ThisRow).Interior.ColorIndex = xlColorIndexNone
End If
End If
End Sub

subu.ns · #7 (**permalink**) 02-20-12, 12:58

Try the below if u r not able to check with the above...
If any thing else let me know...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range

' The variable KeyCells alerts when ever they are changed.
Set KeyCells = Range("A1:C10")

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then

MsgBox "Cell " & Target.Address & " has changed."
'this line displays the required message

End If
End Sub

If any thing else let me know...
Regards...
Subbu

Internet Services

Web Development

Digital Marketing

Consumer Tech