VBA Excel Button Macro Error

Question

I am programmatically placing a button on a worksheet and it places fine, however when I click it i get an error saying "Cannot run the macro. The macro may not be available in this workbook or all macros may be disabled". I believe I've set it up fine but here is my code if anyone spots anything would greatly appreciate it.

Sub ButtonGenerator()

    Application.ScreenUpdating = False

    Dim wsCRC As Worksheet
    Set wsCRC = Worksheets("CRC")

    Dim lcolumncrc As Long
    lcolumncrc = CRC.LastColumnInCRC

    'Button Declarations
    Dim ShowHideDates As Button

    wsCRC.Buttons.Delete

    'Show/Hide Dates Button Set Up
    Dim SHDrange As Range

    Set SHDrange = wsCRC.Range(Cells(5, lcolumncrc + 2), Cells(5, lcolumncrc + 4))
    Set ShowHideDates = wsCRC.Buttons.Add(SHDrange.Left, SHDrange.Top, SHDrange.Width, SHDrange.Height)

    With ShowHideDates
        .OnAction = "wsCRC.SHDbtn"
        .Caption = "Show Hidden Date Columns"
        .Name = "ShowHideDates"
    End With

    Application.ScreenUpdating = True

End Sub

Sub SHDbtn()

    Dim wsCRC As Worksheet
    Set wsCRC = Worksheets("CRC")
    Dim ShowHideDates As Button

    Dim CurrentDateColumn As Long
    CurrentDateColumn = GetTodaysDateColumn()

    ActiveSheet.Unprotect

    If ShowHideDates.Caption = "Hide Old Date Columns" Then
        wsCRC.Range(wsCRC.Cells(5, 10), wsCRC.Cells(5, CurrentDateColumn - 6)).EntireColumn.Hidden = True
        ShowHideDates.Caption = "Show Hidden Date Columns"
    Else
        wsCRC.Range(wsCRC.Cells(5, 10), wsCRC.Cells(5, CurrentDateColumn - 6)).EntireColumn.Hidden = False
        ShowHideDates.Caption = "Hide Old Date Columns"
    End If

    ActiveSheet.Protect

End Sub

CLR · Accepted Answer · 2017-07-14 08:08:11Z

2

You're referring to a worksheet by the label you've given it within your code, not as a sheet itself.

Try changing:

.OnAction = "wsCRC.SHDbtn"

to

.OnAction = "CRC.SHDbtn"

or even

.OnAction = "SHDbtn"

answered Jul 14, 2017 at 8:08

CLR

12.3k1 gold badge13 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

SBozhko Over a year ago

Nice, this has got rid of that error, however when I press the button, i get the "Object variable or With block variable not set" error. I've only adeed the CRC. as you showed in the 2nd line of code in your answer. Any idea what this could be?

CLR Over a year ago

What line is highlighted when you get the new error?

SBozhko Over a year ago

'If ShowHideDates.Caption = "Hide Old Date Columns" Then'

CLR Over a year ago

You declare a button called ShowHideDates but you don't actually Set it to the button in question. If ShowHideDates is the actual name you've created the button under, then I don't think you need to declare it, just reference it, such as CRC.ShowHideDates maybe?

SBozhko Over a year ago

Its alright, I've got it to work now, thanks for the help though!

Collectives™ on Stack Overflow

VBA Excel Button Macro Error

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related