PDA

View Full Version : Disable print button in worksheet command bar - Excel 2007



SHazlett
04-22-2011, 09:33 AM
When I open the worksheet I want to disable the print button within the Office Button menu.

Here is the code that I am currently using:

Sub DisableMenuItems()

With Application.CommandBars("Worksheet Menu Bar")
.Controls("Print").Enabled = False 'Print Button
End With

End Sub

I am getting the error Runtime error '5'

Bob Phillips
04-22-2011, 09:50 AM
Try



Sub DisableMenuItems()

With Application.CommandBars("Worksheet Menu Bar")
.Controls("File").Controls("Print").Enabled = False 'Print Button
End With

End Sub

But what about Ctrl-P, and the print button?

SHazlett
04-22-2011, 09:52 AM
What would you use to disable all three?

SHazlett
04-22-2011, 09:54 AM
I just tried your code and it gave me the same error. Any thoughts?

Bob Phillips
04-22-2011, 11:19 AM
Dim ctl As CommandBarControl

Application.CommandBars(1).Controls("File").Controls("Print...").Enabled = True

Set ctl = Application.CommandBars.FindControl(ID:=2521)
ctl.Enabled = False

Application.OnKey "^P", "dummy"