Custom picture on CommandBar? (VBA Office 2000)

Topic

New Reply

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%5B/url%5D. 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.

Reply | Quote

Replies

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 ) from the Commands list to your command bar.
Right-click the new button and select Paste Button Face.
Close the Customize dialog.

Reply | Quote

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 ) from the Commands list to your command bar.
Right-click the new button and select Paste Button Face.
Close the Customize dialog.

Reply | Quote

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(“MyButton”).Enabled = False

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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”).Copy
oControl.PasteFace
oControl.Caption = “Flexible find + replace utility”
End Sub

(Now where did that code come from I wonder )

###Edited###16-12-2003
This piece of code was taken from my flexfind utility, find it at my website below.

Reply | Quote

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”).Copy
oControl.PasteFace
oControl.Caption = “Flexible find + replace utility”
End Sub

(Now where did that code come from I wonder )

###Edited###16-12-2003
This piece of code was taken from my flexfind utility, find it at my website below.

Reply | Quote

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

Reply | Quote

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(“MyButton”).Enabled = False

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

Reply | Quote