CODEWITHSUNDEEP
excelvba
Lzypenguin
Lzypenguin

Reputation: 955

How to activate a macro by clicking in a cell with a formula?

I am trying to activate a macro by clicking in a cell.

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Selection.Count = 1 Then
    If Not Intersect(Target, Range("B37")) Is Nothing Then
        Worksheets("DaysEditor").Activate
        Sheets("DaysEditor").Columns("C:LY").Hidden = False
        Sheets("DaysEditor").Columns("C:EX").Hidden = True
        Sheets("DaysEditor").Range("A1").Select
    End If
End If
End Sub

This code works on a cell that doesn't have a formula but will not work on cells with a formula in them.

Upvotes: 3

Views: 449

Answers (2)

K.Dᴀᴠɪs
K.Dᴀᴠɪs

Reputation: 10139

A few minor tweaks I would change with your code. Firstly, if you are going to make multiple calls to a worksheet, I suggest that you either make a variable of it or use it within a With <obj> statement.

Worksheets() is essentially a function. Every time you use it it has to get the function's "value" - hence not the most efficient way of doing things.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Target.Address = "$B$37:$C$37" Then
        With Worksheets("DaysEditor")
            .Columns("C:LY").Hidden = False
            .Columns("C:EX").Hidden = True
            .Activate
            .Range("A1").Select
        End With
    End If

End Sub

Generally I advise against using the Activate method. But since you appear to actually want to be on the worksheet when the code runs then it's not a big deal here. Just remember that Activate is not necessary for the remainder of your code to function.

One last tweak is that I moved your Activate method after you hide/unhide the columns. Probably not a big deal either, but no need to watch the screen redraw unnecessarily.

Upvotes: 2

Lzypenguin
Lzypenguin

Reputation: 955

Thanks to @K.Davis he helped me figure out that the problem was the that cell I was attaching this to was a merged cell and that was the problem. I changed the Selection.Count = 1 to Selection.Count = 2 and the code works perfectly!

Upvotes: 0

Related Questions