Results 1 to 6 of 6
  1. #1
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Custom picture on CommandBar? (VBA Office 2000)

    I have a company logo as a 16 x 16 pixel image and I'm wondering if it's possible to incorporate it as the first "image" on a custom CommandBar, purely as a cosmetic addition/ identifier. If not, then anywhere else on an Excel workbook that would not obscure any cells?

    thanks

    Alan

    Edited - found what appears to be a workable solution on http://dbforums.com/arch/219/2003/2/691497. This works as advertised, but I still need to use a clickable button to hold the picture. This is OK, but if it could be placed purely as a graphic, that would be even better.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Custom picture on CommandBar? (VBA Office 2000)

    Yes, you can. Open the image in Paint or another graphics editing program, select it as a whole (usually Edit | Select All) and copy it to the clipboard.

    In Excel, select Tools | Customize.
    If you already have a custom command bar, make sure it's visible, otherwise, create a new one (click New... in the Toolbars tab.)
    Activate the Commands tab.
    Locate and select Macros in the Categories list.
    Drag the Custom button (the <img src=/S/smile.gif border=0 alt=smile width=15 height=15>) from the Commands list to your command bar.
    Right-click the new button and select Paste Button Face.
    Close the Customize dialog.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Custom picture on CommandBar? (VBA Office 2000)

    You can use VBA to disable the custom toolbar button. You need to have a reference to the Microsoft Office 9.0 Object Library.

    Application.CommandBars("MyToolbar").Controls("MyB utton").Enabled = False

    where MyToolbar and MyButton are the names of the toolbar and button.

  4. #4
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom picture on CommandBar? (VBA Office 2000)

    Excellent. Thanks Hans. I prefer your solution to the one I found - it would be harder for the user to accidentally delete the image. The thing I failed to mention though is that this custom bar is recreated each time the workbook opens (advice from Steve a long time ago when I was having problems with users having older versions of workbooks and commandbars). Is your method workable in this case?

    I'm also wondering, more generally, whether the user will need to have the same set of references as I do for the VBA to work as intended. In this case, the Microsoft Office 9.0 Object Library. I don't know anything about what "carries" with the workbook and what is needed at the client end.

    Alan

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Custom picture on CommandBar? (VBA Office 2000)

    You can disable the custom button after creating it in code.

    References travel with the workbook. In general, VBA tries to solve problems with references. For example, if somebody with Office 2002 or 2003 opens your workbook, the references to the Excel 9.0 and Office 9.0 libraries will automatically be updated to 10.0 or 11.0. And if Office has been installed in a non-standard location, it will probably be resolved, but I can't guarantee that.

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Custom picture on CommandBar? (VBA Office 2000)

    If you paste the picture on a sheet, you can use code like this to copy the button face back in after creating it:

    Sub CreateBar()
    Dim oBar As CommandBar
    Dim oControl As CommandBarControl
    RemoveBar
    Set oBar = Application.CommandBars.Add
    Set oControl = oBar.Controls.Add(ID:=23, Before:=1)
    oBar.Name = "FlexFind"
    oBar.Visible = True
    oBar.Position = msoBarTop
    oControl.OnAction = "flexifinder"
    ThisWorkbook.Worksheets(1).Shapes("ButtonImage").C opy
    oControl.PasteFace
    oControl.Caption = "Flexible find + replace utility"
    End Sub

    (Now where did that code come from I wonder <g>)

    ###Edited###16-12-2003
    This piece of code was taken from my flexfind utility, find it at my website below.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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