MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Disable Custom Toolbar Buttons

Posted by JAF on March 01, 2000 4:55 AM

I have created a number of custom toolbar buttons which do various time-saving things for me (sort worksheets, highlight formulas, lock formulas etc.)

What I haven't worked out is how to disable these buttons when there is no workbook open.

Some of the default Microsoft icons are ghosted, but become active as soon as a workbook is opened or a new one created. Does anyone know what code I need to do this?


Posted by Keith Hoar on March 01, 2000 11:49 AM

The workbook will have to be open. What you need to do is put some code into an Auto_Open macro. this macro will automatically run when the workbook is opened.


'Find ID number of Standard Toolbar "Print" button
For i = 1 To 25
If Left(CommandBars("Standard").Controls(i).Caption, 5) = "Print" Then
PrintButton = i
Exit For
End If
Next i

' Disable menu & toolbar functions
CommandBars("Worksheet menu bar").Controls("File").Controls("Save").Enabled = False
CommandBars("Worksheet menu bar").Controls("File").Controls("Page Setup...").Enabled = False
CommandBars("Worksheet menu bar").Controls("File").Controls("Print Area").Enabled = False
CommandBars("Worksheet menu bar").Controls("File").Controls("Print...").Enabled = False
CommandBars("Worksheet menu bar").Controls("File").Controls("Print Preview").Enabled = False
CommandBars("Standard").Controls("Save").Enabled = False
CommandBars("Standard").Controls(PrintButton).Enabled = False
CommandBars("Standard").Controls("Print Preview").Enabled = False

Keith Hoar

Posted by JAF on March 07, 2000 7:58 AM


The above works on a specific workbook, but the macros for which I have toolbar buttons are generic and could work on ANY workbook/worksheet.

Is there any way to disable the buttons when NO workbook is active and to enable them whenever ANY workbook is opened?

Posted by bill.roberts on March 07, 2000 11:45 AM


How about creating a custom toolbar in VBA?