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!

 

[Joe Smith 84]

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

View attachment 84674

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.

I performed the same steps again, but this time it took me to this:

 

[Peter_SSs]

this time it took me to this:

Do you mean after you got that same error message and clicked 'Debug'?

Could you please copy/paste the code for the macro Security_Setup_Step_Q_Select_new_Client

Could you also post the range A1:B22 of the worksheet using XL2BB?

See my signature block below has more details about posting vba code and XL2BB

 

[Joe Smith 84]

Do you mean after you got that same error message and clicked 'Debug'?

Could you please copy/paste the code for the macro Security_Setup_Step_Q_Select_new_Client

Could you also post the range A1:B22 of the worksheet using XL2BB?

See my signature block below has more details about posting vba code and XL2BB

Do you mean after you got that same error message and clicked 'Debug'? Correct

I tried going the XL2BB route, but it created a whole bunch of issues with my excel, so I had to remove the add-in.

Below is a screenshot of the information in those cells. Very basic stuff. The idea is to hide and unhide rows when cell B8 is clicked on (unhide), and then hide the same rows again if cell A8 (hide) is clicked on.

Here's the code for the macro though:

Sub Security_Setup_Step_Q_Select_new_client()
'
' Security_Setup_Step_Q_Select_new_client Macro
'
' Keyboard Shortcut: Ctrl+Shift+Q
'
    Rows("8:21").Select
    Selection.EntireRow.Hidden = False
    Range("A8").Select
    ActiveCell.FormulaR1C1 = "u"
    Range("A1").Select
End Sub

I'm really hoping there's a solution to this, it seemed like such a basic thing to do in my mind.

 

[Peter_SSs]

As I mentioned earlier ..

.. my signature block below has more details about posting vba code ..

Please review that again. I have added the vba code tags for you in post 13 this time.

Can't tell much from an image. Never-the-less see if this makes any difference.
Replace the cell addresses, MyMacro2 etc with the correct addresses/macro names. (Again, I can't copy them from an image. )

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

 

[Peter_SSs]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: VBA to run various macros when clicking on different cells in Excel
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[rollis13]

How many other macros do you have in that sheet's module ? and what macros do you have in ThisWorkBook's module ?

 

[Joe Smith 84]

How many other macros do you have in that sheet's module ? and what macros do you have in ThisWorkBook's module ?

I have 4 macros in total on the sheet I'm working on. No other sheets in the workbook contain any macros

 

[Peter_SSs]

Did you test the suggestion in post #14?

 

[rollis13]

I have 4 macros in total on the sheet I'm working on. No other sheets in the workbook contain any macros

Unless they are all called "Security_Setup_Step_xxxxxxx" do you mind showing them !!! do you think that we have a crystal ball ??!!