Member Avatar for MikeG0930

I have a macro that uses "If Then" to choose what macro to run based on the ActiveCell. I would like to use the Select Case method instead, but need help to do it. How would I change this code?

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

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Sheets("MacroTest").Select
'MacroTest.Unprotect Password:="yanks"
If Not Intersect(Target, Range("RecAccess")) Is Nothing Then
Call RecAccess
Else
If Not Intersect(Target, Range("EFS")) Is Nothing Then
Call EFS
Else
If Not Intersect(Target, Range("Consents")) Is Nothing Then
Call Consents
Else
If Not Intersect(Target, Range("Privacy")) Is Nothing Then
Call Privacy
Else
If Not Intersect(Target, Range("Guardian")) Is Nothing Then
Call Guardian
Else
MsgBox "Cell not in named ranges"
End If
End If
End If
End If
End If
Application.ScreenUpdating = True
Application.DisplayAlerts = True
'Sheet1.Protect Password:="yanks"
End Sub

  • 2 Contributors
  • 1 Reply
  • 135 Views
  • 9 Hours Discussion Span
  • Latest Post Latest Post by Unhnd_Exception
Member Avatar for Unhnd_Exception

I don't know if you can get that in a Select statement.

If your wanting to shorten your code you could do this.

If Intersect(Target, Range("EFS")) Isnot Nothing Then
Call EFS
ElseIf Intersect(Target, Range("Consents")) Isnot Nothing Then
Call Consents
ElseIf ...
Call ....
Else
MsgBox "Cell not in named ranges"
End If

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.