quick access tool bar show/hide custom button on open/close workbo

[JCIrish]

I have a workbook with code that displays a user form when the workbook is
opened. I've also added (using the "Customize" option in "Excel Option's") a
custom button to the Quick Access Toolbar that displays the same user form
when clicked. How can I get the button to show on the quick access toolbar
for this workbook only? Or, am I going about accomplishing these two things
in the completely wrong way?

The relevant VBA statements are simple:

Sub Workbook_open()
UserForm1.show
End Sub

and the macro behind the button on the quick access toolbar is

Sub OpenFormFromToolbar()
UserFrom1.show
End Sub

All help will be greatly appreciated.

 

[Fan924]

I use this in the sheet code to call macros that open and close
toolbars.

Private Sub Worksheet_Deactivate()
Call NoBar16
End Sub

Private Sub Worksheet_Activate()
Call Bar16
End Sub

 

[JCIrish]

Thanks Fan924

I have a 3-sheet workbook so I could try using such a code on each sheet.
But if I don't remove it via code from the quick access toolbar won't it
still be available on all workbooks?

 

[Ron de Bruin]

Hi JCIrish

Right click on the QAT and choose Customize QAT
In the "Choose Commands from" dropdown choose "Macros"
In the "Customize Quick Access Toolbar" dropdown choose "For workbook name"
Add the "macro" to the QAT
If you want you can use the Modify button to choose another icon
Press OK


There is a bug on this moment so read this
http://www.rondebruin.nl/qatbuttonbug.htm