MS Excel Error - Cannot run the macro "***". The macros may not be available in this workbook or all macros may be disabled.

Question

I have a workbook that I am getting this error in everytime I try and click a button. I have tried creating a new button and I get the same error.

Also, I have another workbook just like this one but for the previous years data and those buttons work perfect. It is the same code and everything. What can I try? Ideas?

Answer 1

Hi asnoble, can you upload a copy or the Spreadsheet to the Cloud (OneDrive, DropBox . . , etc.) and provide a link here, so I can see if I can replicate this issue . ..

Answer 2

Hi asnoble, can you upload a copy or the Spreadsheet to the Cloud (OneDrive, DropBox . . , etc.) and provide a link here, so I can see if I can replicate this issue . ..

Spreadsheet

Answer 3

Hi Andy, for some reason Excel cannot see your Macro

https://imgur.com/a/m9gdz

In the top left of the VBA Editor, move your Macro into Module 1

Remove Private from the macro definition

Then right click the button and select Assign Macro and Excel will see the macro

https://imgur.com/a/sH2GT

Answer 4

As I read in your conversation with Dave M., you provided your file.

I do not agree with Dave, if you move the code into a regular module will not solve your issue.

As I can see from the first line in the code

If cmbHideShowColumns.Caption = "Hide Previous Years Data" Then

the button was an ActiveX command button before.

I can not say when or why or what happened to your file nor what has changed all command buttons to rectangles that looks like form control buttons...

Do as follows:

Make a backup copy of your file!

Close Excel

Press and hold the CTRL key

Open Excel

Wait for a message to appear and ask for "Safe Mode"

Release the CTRL key

Click Yes

After Excel opens

Click File\Open and select your file

Press and hold the SHIFT key

Click Open

Wait for your file to open (do not update any data)

Release the SHIFT key

Open the VBA editor

Insert a regular module

Paste in this code

Sub AK_ReCreateCommandButton()
  Dim ws As Worksheet
  Dim Sh As Shape
  Dim OO As OLEObject
  Dim Cb As MSForms.CommandButton
  Dim S As String
  For Each ws In Worksheets
    For Each Sh In ws.Shapes
      If Sh.Type = msoAutoShape Then
        S = Sh.Name
        Set OO = ws.OLEObjects.Add("Forms.Commandbutton.1", _
          Left:=Sh.Left, Top:=Sh.Top, Width:=Sh.Width, Height:=Sh.Height)
        Set Cb = OO.Object
        Sh.Delete
        OO.Name = S
        Cb.Caption = "Show All Years Data"
      End If
    Next
  Next
End Sub

Close the VBA editor

Press Alt-F8 and run the macro

Wait until finished

Save the file

Close Excel

Reopen your file and test the behavior

Problem solved?

Andreas.