Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Menu/Right Click Face (ExcelXP)

    I'm building and using menu's (top & right click) from VBA, e.g. for a key this looks like:

    Set MenuItem = MenuObject.Controls.Add(Type:=msoControlButton, Temporary:=True)
    MenuItem.OnAction = "My Code"
    MenuItem.Caption = "&Select this option"
    MenuItem.FaceId = 610

    As you can see, I'm using the FaceId-property to control the appearance of the little icon that precedes the option.

    For several of my menu-entries however, I'd like to use icons that do not exist in the FaceID-list. Using Excel's Toolbar-Customize menu, I can manually do an "edit button image".
    I'm not sure if and how I can do this from VBA. As I'm building and rebuilding the menu's based on the user's actions, I'd require code that would allow me to update this. also, as the icons ("button images") will be created by me (they will be quite simple); how (and where) would I store these for re-use when I'm rebuilding my menus... (I doubt if VBA would allow me to re-design at runtime)

    Appreciate any help.

    EJ

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Menu/Right Click Face (ExcelXP)

    The only way I know to do this, is to place the icons you want to use in a sheet (could be hidden), then when you are creating the button, copy the the appropriate image to clipboard and then paste it. Here is an example code snippet:

    <pre>'In macro copy the image to clipboard (eg)
    Worksheets("Sheet1").Shapes("CustomIcon").Copy
    'Use pasteface to paste the image to a new button
    Set myButton = Application.CommandBars("Worksheet Menu Bar"). _
    Controls.Add(msoControlButton)
    With myButton
    .OnAction = "myMacroA"
    .Caption = "Menu A"
    .PasteFace
    End With
    </pre>


    Steve

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

    Re: Menu/Right Click Face (ExcelXP)

    Another way is to create a custom toolbar with buttons for all the images you want to use. Make this toolbar invisible.

    You can use this to set the picture for buttons on your visible toolbars:

    Application.Commandbars("Menu Bar").Controls("My Menu").Controls("&Select this option").Picture = Application.CommandBars("CustomPictures").Controls (4).Picture

    The advantage of this method is that it doesn't trash whatever was on the clipboard.

  4. #4
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Menu/Right Click Face (ExcelXP)

    So if I create such a toolbar and hide it, it will remain with the sheet always when I save/edit/load etc. and I can 'steal' pictures as indicated to populate my visible menus?

  5. #5
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Menu/Right Click Face (ExcelXP)

    Thanks,

    I'll explore the hidden-toolbar first (as that seems easier), else I'll go for this one!

    By-the-way... how would I get the icon-picture copied off a toolbar (where I've created it with the small editor there) and into my sheet? Is there a copy (&paste) possible?

    Erik Jan

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

    Re: Menu/Right Click Face (ExcelXP)

    Custom toolbars are stored in your personal .xlb file - for Excel 2002 on Windows XP, it is
    Cocuments and Settings<username>Application DataMicrosoftExcelExcel10.xlb
    But you can attach a custom toolbar to a workbook, so that it travels with it: select Tools | Customize..., and click Attach... in the Toolbars tab.

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

    Re: Menu/Right Click Face (ExcelXP)

    You can create button images in any graphics editor, such as Paint. It is a 16 by 16 pixel with 16 colors.

    If you have created a button image in the built-in editor:
    - Select Tools | Customize...
    - Right-click the button.
    - Select Copy Button Image from the popup menu.
    - Close the Customize dialog.
    - Select the cell in the workbook where you want the image.
    - Edit | Paste or Ctrl+V.

  8. #8
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Menu/Right Click Face (ExcelXP)

    Got that... for various reasons, I decided to go for this option (copy faces from a sheet onto the toolbar i.s.o. from a hidden bar). So I created icons, copied them off the toolbar and into a sheet. Gave them a name...

    Here's my (last?) problem...

    Set MenuItem = MenuObject.Controls.Add(Type:=msoControlButton, Temporary:=True)
    MenuItem.OnAction = "My Sub"
    MenuItem.Caption = "My Sub_name"
    MenuItem.Picture = ???

    If I want to avoid copy-paste constructions, there should be another way... Again, my little pictures have names... so I'm looking for a construction like:

    MenuItem.Picture=Range("MyPictureName").<font color=red>Something</font color=red>

    Anyone who can tell me if this direct assignment is possible and how that should be done???

    Thanks,

    EJ <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Menu/Right Click Face (ExcelXP)

    I think you either need to:
    Use a faceid (no custom icons)
    Take the picture off an existing commandbar item (Hans' procedure)
    or copy the picture and use PasteFace

    I have not tried this, but perhaps LoadPicture (requires a path/filename, not an existiing object) could be used

    Steve

Posting Permissions

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