Page 1 of 3 123 LastLast
Results 1 to 10 of 27

Thread: custom toolbar options
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Apr 2003
    Location
    United KIngdom
    Posts
    126
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default custom toolbar options

    I have created my own toolbar and just placed it at the top. What i would like to do is have this hidden or disabled on open of workbook. Also i have a login form that loads on open of the workbook and when the users enter their logins (not set up yet) depening who they are can have certain buttons enabled on my toolbar. After the login process have the toolbar unhidden.

    Any help much appreciated.

    Thanks,

    ishi

  2. #2
    MrExcel MVP TommyGun's Avatar
    Join Date
    Dec 2002
    Location
    Clear Lake, TX
    Posts
    4,202
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: custom toolbar options

    Pretty simple to do. You will just need info as to what controls are going to be locked for which users, then you can loop thru that and enable/disable controls.

    Hiding/Unhiding the toolbar is pretty easy too. Assuming that your toolbar is named "CustomBar"...
    Code:
    Application.CommandBars("CustomBar").Visible = True 'Show
    Application.CommandBars("CustomBar").Visible = False 'Hide

  3. #3
    Board Regular
    Join Date
    Apr 2003
    Location
    United KIngdom
    Posts
    126
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: custom toolbar options

    Thanks TommyGun for that. Just one thing i am not entirely sure on looping through the controls on my toolbar.

    I have tried this
    For i = 1 To Application.CommandBars("CustomBar").Controls.Count
    MsgBox s.Name
    Next i

    But keeping getting error "object or with block variable not set."

    any ideas what's wrong.

    Thanks,

    ishi

  4. #4
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    3 Thread(s)

    Default Re: custom toolbar options

    What's your variable s?

    Try:

    Code:
    Dim Ctrl As CommandBarControl
    For Each Ctrl In Application.CommandBars("CustomBar").Controls
    MsgBox Ctrl.Name 
    Next Ctrl

  5. #5
    Board Regular
    Join Date
    Apr 2003
    Location
    United KIngdom
    Posts
    126
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: custom toolbar options

    my variable was dim s as control. Which probably isn't right but with your example i get run-time error 438 object doesn't support the property or method.

    ishi

  6. #6
    MrExcel MVP TommyGun's Avatar
    Join Date
    Dec 2002
    Location
    Clear Lake, TX
    Posts
    4,202
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: custom toolbar options

    Instead of
    Code:
    Ctrl.Name
    use
    Code:
    Ctrl.Caption
    CommandBarControls do not have a NAME property, they are referred to by their CAPTION.

  7. #7
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    3 Thread(s)

    Default Re: custom toolbar options

    Sorry, it's:

    MsgBox Ctrl.Caption

  8. #8
    Board Regular
    Join Date
    Apr 2003
    Location
    United KIngdom
    Posts
    126
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: custom toolbar options

    thanks that works. But i am surprised that the caption is the name that i have given to the buttons on my toolbar. A little confusing.

    ishi

  9. #9
    MrExcel MVP TommyGun's Avatar
    Join Date
    Dec 2002
    Location
    Clear Lake, TX
    Posts
    4,202
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: custom toolbar options

    Nobody said this would be easy.

    Quote Originally Posted by ME
    Pretty simple to do.
    Actually, if you want to talk about confusing Object models, then try to code some things in Word.

  10. #10
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    3 Thread(s)

    Default Re: custom toolbar options

    Quote Originally Posted by ishi
    ... A little confusing.

    ishi
    That's why I got the code wrong. I was mostly concerned with converting your For...Next loop into a For Each...Next loop.

Some videos you may like

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
  •