Custom buttons on toolbar

  • Thread starter Thread starter Pat
  • Start date Start date
P

Pat

Is there a way to have custom buttons made available on the toolbar only
when the custom buttons relate to a particular sheet?
 
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
 
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)
 
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
 
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
 
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)
 
one way:

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