Macro run if and cell in range is selected in VBA

[Celtic_Avenger]

Hi.

Can someone please help.

I have used the made a simple macro to show a msgbox if any cell on th
page is selected, by placing it in the Selection_Change part of th
Sheet Code.

On another sheet I want 3 different macros to run if any cell in
different ranges are selected.

Is there a way to do this?

Thanks

Celtic_Avenger
:confused

 

[Alex J]

Celtic,
Try this. It is not the most sophisticated for implementing the three
macros, but the key here is to use the INTERSECT function to find out if the
Target range (the selection) is within one of your three ranges on the
sheet.

AlexJ

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim ShtRng(1 To 3) As Range
Dim Isect As Range
Dim i As Long

Set ShtRng(1) = Me.Range("A1:B2")
Set ShtRng(2) = Me.Range("D1:E2")
Set ShtRng(3) = Me.Range("G1:H2")

For i = 1 To 3
Set Isect = Intersect(Target, ShtRng(i))
If Not Isect Is Nothing Then
Call DoSomething(i)
Exit For
End If
Next i
Erase ShtRng


End Sub

Sub DoSomething(Idx As Long)
Select Case Idx
Case Is = 1
MsgBox "Macro 1"
Case Is = 2
MsgBox "Macro 2"
Case Is = 3
MsgBox "Macro 3"
End Select
End Sub