How do I attach a toolbar I made to only ONE workbook?


Posted by GLITZ on December 19, 2000 3:54 PM

I have created a toolbar that has 3 macros on it. I want to use these macros in only one specific spreadsheet.

Here's the problem: The tool bar is in every sheet I open.....!

I know I can "turn the toolbar off" when I'm in other sheets. That's just not what i want to do everytime.

Any tipz on what I can do different?


Thank you!!!!!!!!!!!!!!

Posted by Duane Kennerson on December 19, 2000 8:52 PM

I don't know if this is what your looking for but you can use this
code in the sheet activate to enable or disable control buttons.
The .Controls(1) is asking for what number the button is located
on the toolbar. If you wanted to disable all three, you would use
three lines of code changing the .Controls to 1, 2 and three in each
line. On worksheet deactivate, you would simply change the .Enabled to True.
Hope this helps...
Duane
CommandBars("Your toolbars name").Controls(1).Enabled = False

Posted by GLITZ on December 19, 2000 10:00 PM


THANK YOU Duane


Can a toolbar be exclusive to a single worksheet and only that worksheet?

instead of active in everyone, until disabled....

Posted by Duane Kennerson on December 20, 2000 1:04 PM

Is it an attached toolbar or is it one that is created by excel everytime you open the workbook???

Posted by GLITZ on December 20, 2000 4:04 PM

I'm not 100% sure.....It's one I created and it seems to be in every workbook unless I turn it off?

Is that just the way toolbars are?



Posted by Duane Kennerson on December 20, 2000 6:00 PM

There are a couple of ways to put toolbars in excel. One is to create a toolbar using custom toolbars which will make
the toolbar show up when excel is open. Another way is to use VBA to program a toolbar to be created when a certain workbook
is opened and then program the toolbar to be deleted when the workbook closes. I have never tried to programmatically create
a toolbar only when a certain sheet is open but I'm sure it can be done with a sheet activate and sheet deactivate. Try fiddling
around with this code...

Put this in a worksheet activate

Dim cbrCommandBar As CommandBar
Dim cbcCommandBarButton As CommandBarButton
'This will create a custom command bar for the program

On Error Resume Next
Application.CommandBars("Your toolbars name").Delete

Set cbrCommandBar = _
Application.CommandBars.Add
cbrCommandBar.Name = "Daily Sheet Toolbar"
cbrCommandBar.Position = msoBarTop
With cbrCommandBar.Controls
Set cbcCommandBarButton = _
.Add(msoControlButton)
'Set properties of command buttons
With cbcCommandBarButton
.Style = msoButtonIconAndCaption
.FaceId = 71
.TooltipText = _
"Week 1"
.OnAction = "One"
End With

faceid is for what icon will show up on the toolbar.
there are over 3000 face id's so good luck on picking one.
The tooltiptext is what you want the help to say when a
user place the cursor over the button.
The .onaction is the name of the macro that will be done
when the user pushes the button.

Place this code in the worksheet deactivate...

'remove the custom command bar before exiting excel

On Error Resume Next
Application.CommandBars("your toolbars name").Delete

hopefully this helps. You may have to tool around with it
a little. Try creating one button at a time till you figure
out the code.
Good luck
Duane