Run VBA by clicking on a cell instead of using ActiveX Control (Command Button)

[maclachlan19]

Most of my spreadsheet are shared by a number of people that use different computer setups, different monitor resolutions and thru a Remote desktop connection.
The Command buttons I use to run some VBA often grow and shrink, often covering up the data.
I have searched and this appears to be an Excel issue.

My question is can you run VBA without using a command button. Perhaps a hyperlink in a cell of the spreadsheet.

 

[Candyman8019]

for sure. You can try something like the following. As soon as A3 is selected, the script would run:

Private Sub worksheet_selectionchange(ByVal target As Range)

 If Not Intersect(Range("A3"), target) Is Nothing Then
     'do something
End If

End Sub

 

[maclachlan19]

I tried the code above but nothing happened,
I replaced 'do something with the code I wanted to run. Is this right?

Is using a Hyperlink a better option than clicking the cell.

Private Sub worksheet_selectionchange(ByVal targe As Range)
    If Not Intersect(Range("L5"), Target) Is Nothing Then
      Application.ScreenUpdating = False
        Worksheets("New").Unprotect Password:="xxxxx"
        Sheets("New").Range("B5:B5").Copy
        Sheets("Tim").Range("B1:B1").PasteSpecial Paste:=xlPasteValues
        Sheets("Used").Range("B5:B5").PasteSpecial Paste:=xlPasteValues
        Sheets("Serv").Range("B5:B5").PasteSpecial Paste:=xlPasteValues
        Sheets("Parts").Range("B5:B5").PasteSpecial Paste:=xlPasteValues
        Sheets("Leasing").Range("B5:B5").PasteSpecial Paste:=xlPasteValues
        Sheets("Other").Range("B5:B5").PasteSpecial Paste:=xlPasteValues
        Sheets("Dealer").Range("B5:B5").PasteSpecial Paste:=xlPasteValues
 
 Range("E:CF").EntireColumn.Hidden = False
 Dim c As Range

    For Each c In Range("E3:CF3").Cells
        If c.Value = "0" Then
            c.EntireColumn.Hidden = True
          
        End If
    Next c
Worksheets("New").Protect Password:="xxxxx"
Application.ScreenUpdating = True

End If
    
End Sub

 

[Candyman8019]

One typo one the first line. Change targe to target.

 

[maclachlan19]

Thanks, I just changed that but no luck on making this work.

The vba code is located in "Sheet1 (New)", is this right?

I've setup a hyperlink in cell L5 using:
Place in this Document
Type the cell reference = L5

Anything else I might be missing.
Thanks

 

[Candyman8019]

The code should be in whichever sheet L5 (your trigger) cell is located in. You shouldn’t need a hyperlink in this cell.

One other thing to check is that you have application events enabled…and that this is in a macro enabled workbook.

 

[maclachlan19]

The code is in the correct sheet, just checked.
I have deleted the hyperlink from L5

I'm not sure how to have application events enabled. I added some code, but I'm over my head here.

Private Sub worksheet_selectionchange(ByVal target As Range)
    If Not Intersect(Range("L5"), target) Is Nothing Then
  Application.EnableEvents = True
  Application.ScreenUpdating = False
Worksheets("New").Unprotect Password:="xxxx"
Sheets("New").Range("B5:B5").Copy
Sheets("Rob").Range("B1:B1").PasteSpecial Paste:=xlPasteValues
Sheets("Used").Range("B5:B5").PasteSpecial Paste:=xlPasteValues
Sheets("Serv").Range("B5:B5").PasteSpecial Paste:=xlPasteValues
Sheets("Parts").Range("B5:B5").PasteSpecial Paste:=xlPasteValues
Sheets("Leasing").Range("B5:B5").PasteSpecial Paste:=xlPasteValues
Sheets("Other").Range("B5:B5").PasteSpecial Paste:=xlPasteValues
Sheets("Dealer").Range("B5:B5").PasteSpecial Paste:=xlPasteValues
 
 Range("E:CF").EntireColumn.Hidden = False
 Dim c As Range

    For Each c In Range("E3:CF3").Cells
        If c.Value = "0" Then
            c.EntireColumn.Hidden = True
          
        End If
    Next c
Worksheets("New").Protect Password:="xxxx"
Application.ScreenUpdating = True
 

End If
    
End Sub

 

[Candyman8019]

Create a new sub such as.
Private sub test()
Application.EnableEvents = True
End sub

Run that. It will ensure your events are enabled. The events must be enabled for worksheet_selection change to trigger.

 

[maclachlan19]

I figured it out. I was in Design Mode.
Thanks for your help, the code works great.

 

[Candyman8019]

Right on. Happy to help.