Dave H.? Disabling Commandbar buttons/controls via VBA?
Manage your personal finances in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

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

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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 ]

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  3. #3
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Tom

    Do you want:

    Code:
     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:

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

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,936
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com