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
THANK YOU Duane
Can a toolbar be exclusive to a single worksheet and only that worksheet?
instead of active in everyone, until disabled....
Is it an attached toolbar or is it one that is created by excel everytime you open the workbook???
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?
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