Custom buttons on toolbar

[Pat]

Is there a way to have custom buttons made available on the toolbar only
when the custom buttons relate to a particular sheet?

 

[Chip Pearson]

Pat,

You can use the Activate and Deactivate event procedure to make
the items visible or invisible when a particular sheet is
activated.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Bob Phillips]

Pat,

You can do this with worksheet event code to show/hide it. Assuming that you
have a button called MyButton on the formatting menu, here is some code to
do it. This goes in the worksheet code module, right-click the sheet tab,
select View Code from the menu, and paste the code in

Private Sub Worksheet_Activate()
With Application.CommandBars("Formatting")
With .Controls("myButton")
.Visible = True
End With
End With
End Sub

Private Sub Worksheet_Deactivate()
With Application.CommandBars("Formatting")
With .Controls("myButton")
.Visible = False
End With
End With
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Pat]

That worked pretty good!

To add additional controls to the code what adjustment is required?
I have tried the following which is not correct:

Private Sub Worksheet_Activate()
With Application.CommandBars("Formatting")
With .Controls("myButton" , "myButton2")
.Visible = True
End With
End With
End Sub

Pat

 

[Frank Kabel]

Hi Pat
one way:
Private Sub Worksheet_Activate()
With Application.CommandBars("Formatting")
With .Controls("myButton")
.Visible = True
End With
With .Controls("myButton2")
.Visible = True
End With
End With
End Sub

 

[Bob Phillips]

Pat,

You have to handle them one at a time

Private Sub Worksheet_Activate()
With Application.CommandBars("Formatting")
With .Controls("myButton" )
.Visible = True
End With
With .Controls("myButton2" )
.Visible = True
End With
End With
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[JE McGimpsey]

one way:

Private Sub Worksheet_Activate()
With Application.CommandBars("Formatting")
.Controls("myButton").Visible = True
.Controls("myButton2").Visible = True
End With
End Sub