VBA to run various macros when clicking on different cells in Excel

[Joe Smith 84]

Good day

I've come across VBA code that works really well to trigger a macro in Excel when clicking on a specific cell. See below:


Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Selection.Count = 1 Then
If Not Intersect(Target, Range("D4")) Is Nothing Then
Call MyMacro
End If
End If
End Sub


I'm trying to add 3 more such scenarios to the same code, but I have no experience with coding, so troubleshooting has gotten me nowhere. Please see below example of what I'm trying to do, and correct the code if possible:


Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Selection.Count = 1 Then
If Not Intersect(Target, Range("D4")) Is Nothing Then
Call MyMacro1
End If
End If
If Selection.Count = 1 Then
If Not Intersect(Target, Range("E10")) Is Nothing Then
Call MyMacro2
End If
End If
If Selection.Count = 1 Then
If Not Intersect(Target, Range("G23")) Is Nothing Then
Call MyMacro3
End If
End If
If Selection.Count = 1 Then
If Not Intersect(Target, Range("J33")) Is Nothing Then
Call MyMacro4
End If
End If
End Sub


I was expecting the various macros I created (that work well) to automatically run when the cells noted in the code were clicked on


Any assistance will be greatly appreciated!

 

[rollis13]

There are no errors in your macro; it only has redundant code. No need to repeat the check Selection.Count = 1 . You can easily reduce it to this:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Selection.Count = 1 Then
        If Not Intersect(Target, Range("D4")) Is Nothing Then Call myMacro1
        If Not Intersect(Target, Range("E10")) Is Nothing Then Call myMacro2
        If Not Intersect(Target, Range("G23")) Is Nothing Then Call myMacro3
        If Not Intersect(Target, Range("J33")) Is Nothing Then Call myMacro4
    End If
End Sub

 

[Peter_SSs]

Welcome to the MrExcel board!

Try this structure instead

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Selection.CountLarge = 1 Then
    Select Case Target.Address(0, 0)
      Case "D4": Call MyMacro1
      Case "E10": Call MyMacro2
      Case "G23": Call MyMacro3
      Case "J33": Call MyMacro4
      Case Else: 'Do nothing
    End Select
  End If
End Sub

 

[Joe Smith 84]

There are no errors in your macro; it only has redundant code. No need to repeat the check Selection.Count = 1 . You can easily reduce it to this:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Selection.Count = 1 Then
        If Not Intersect(Target, Range("D4")) Is Nothing Then Call myMacro1
        If Not Intersect(Target, Range("E10")) Is Nothing Then Call myMacro2
        If Not Intersect(Target, Range("G23")) Is Nothing Then Call myMacro3
        If Not Intersect(Target, Range("J33")) Is Nothing Then Call myMacro4
    End If
End Sub

Thanks for the assistance. I get the following error when clicking on any of the cells in my code:

Any suggestions?

 

[Joe Smith 84]

Welcome to the MrExcel board!

Try this structure instead

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Selection.CountLarge = 1 Then
    Select Case Target.Address(0, 0)
      Case "D4": Call MyMacro1
      Case "E10": Call MyMacro2
      Case "G23": Call MyMacro3
      Case "J33": Call MyMacro4
      Case Else: 'Do nothing
    End Select
  End If
End Sub

Thanks for the assistance. I get the following error when clicking on any of the cells in my code:

Any suggestions?

 

[Peter_SSs]

Both codes worked when I tested them.
What line of code in each one is highlighted when you click Debug?

 

[rollis13]

@Joe Smith 84, your issue is in your 'called' macros, not our code.

 

[Joe Smith 84]

Both codes worked when I tested them.
What line of code in each one is highlighted when you click Debug?

The excel file closes and doesn't show any debug screen. When I view the code and select "Toggle" - "Breakpoint", it shows the following:

Is there something in the actual sheet I should look at? Or can I tweak the code a bit?

Apologies, VBA code is really new to me and I appreciate this forum and your assistance A LOT!

 

[Joe Smith 84]

@Joe Smith 84, your issue is in your 'called' macros, not our code.

Thanks rollis13. The macros run fine when I select them, or even when I use the shortcuts I created. The names are quite long though, do you suggest I maybe rename them to something shorter?

Also, the cells I'm electing to be selected are populated with text, which guides the user on where to click to unhide certain rows, might that be something that's causing the issue?

 

[Peter_SSs]

Toggle breakpoint is not relevant at the moment.
When your tried the code you showed this error.

That message has a Debug button. Are you saying that Excel closed when you clicked that button? Instead, it should take you to the code and highlight yellow the line that caused the error.