Dave H.? Disabling Commandbar buttons/controls via VBA?

[Tom Schreiner]

Hi everyone!
Was trying to help out a poster,
see Topic: http://www.mrexcel.com/board/viewtop...4219&forum=2&7
if necc.

example 1:
Application.CommandBars("Standard").Controls _
("Print Preview").Enabled = False
Application.CommandBars.Controls

Hierarchy Summary:
Application.CommandBars.Controls

example 2:
Application.CommandBars("Worksheet Menu Bar").Controls _
("File").Controls("Print Preview").Enabled = False

Hierarchy Summary:
Application.CommandBars.Controls.Controls



This code for example 1 works fine:
Disables any control which begins with the Caption "Print"

Sub DisableCommandBarControl()
Dim cb As CommandBar
Dim bc As CommandBarControl

For Each cb In CommandBars
For Each bc In cb.Controls
If Left(bc.Caption, 5) = "Print" Then _
bc.Enabled = False
Next
Next

End Sub


But I could not for the life of me figure out the code for
example 2?

Anyway, I would like to know how to dig deeper here.

Thanks!
Tom

[ This Message was edited by: TsTom on 2002-04-10 05:52 ]

[Tom Schreiner]

Just reviving this post.
I saw a few browsers here presently that might lend some help.
Thanks
Tom

[Dave Hawley]

Hi Tom

Do you want:

 Application.CommandBars("Worksheet menu bar").Controls("File").Controls("Print Pre&view").Enabled = False

????


If the user wishes to prevent Printing while in a specific Workbook,a better option maybe:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
MsgBox "Sorry, no printing"
Cancel = True
End Sub

[Tom Schreiner]

I agree with you on the simpler code, but the lady seemed bent on disabling her icon buttons and menu items.

I was trying to figure out the looping code for example two.
I could not figure out the syntax.

The first example would disable every control on a commandar that started with "Print"

But the second was a contol embedded in another control

Application.CommandBars("Standard").Controls _
("Print Preview").Enabled = False
Application.CommandBars.Controls

Hierarchy Summary:
Application.CommandBars.Controls


CommandBars("Worksheet Menu Bar")
Controls("File")
Controls("Print Preview")

See what I mean?

My looping code would disable "Print" on the commandbar but not "Print" on the filemenu??

Thanks,
Your overcomplicated posting friend,
Tom

[dk]

What about Ctrl P or File, Page Setup, Print? The Workbook_BeforePrint is a far neater and more effective way of disabling printing and previewing - as suggested by Dave H here and by me in the original post.

Regards,
Dan

[ This Message was edited by: dk on 2002-04-10 06:36 ]

[Tom Schreiner]

I agree with you as well!
I was just trying to gain the knowledge of code access to these controls embedded in other controls. I could not figure it out.
The lady got what she wanted, this is just a question that arose for myself in the proccess of trying to help her get what she wanted.
Not really important.
Thanks guys
Tom