Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Commandbar frustration.
Hi all,
I am trying to set up a command bar and code in an .xla for a custom Office 2000 application. All is going well so far except for the run time creating of my command bar. Why create it at run time? Because my command bar is only applicable to specific spreadsheets. I do not like to clutter my users workspace, visible or otherwise, with my application toolbar all the time. If I rely on excel adding the toolbar to the workspace when my .xla is opened it will be there whenever excel is run even if the user is working on an unrelated workbook. So I need a way to create my commandbar at run time. I have devised two schemes, both of which have fallen at the last hurdle. Scheme 1: Store the command bar as a command bar in the .xla In Workbook_Open() of my .xla I have this to declutter the workspace when my .xla is opened. Application.CommandBars(SourceBar).Delete Then when the target application spreadsheet is created I want to copy the commandbar from ThisWorkBook (my .xla) to Application. (the users workspace) but this doesn't work: Set SourceBarHandle = ThisWorkbook.CommandBars(SourceBar) Which is nuts because this works when the commandbar exists in the workspace. Set SourceBarHandle = Application.CommandBars(SourceBar) The ThisWorkBook. form fails with 'Object variable or With block variable not set'. The help and the autofill in the editor both think ..CommandBars is valid with ThisWorkBook. Scheme 2: Store the button data and images on a sheet. In this scheme I have my command button images stored as pictures on a sheet in my .xla. I can create the commandbar at run time and by having the transparency set correctly on the pictures the buttons look fine when they are copied to the buttons. However, the background pixels exist with the background colour rather than being blank as would be the case when the button face editor is used. This means that when the button is disabled all buttons are 16x16 grey squares rather than being the correct shape. The smiley face on a disabled button is a grey circle. Copy it to a picture and back to a button and it looks fine when enabled but when disabled it is a 16x16 grey square. Can anyone fix either scheme? Thanks in advance. Julian |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
X-Axis Frustration | Charts and Charting in Excel | |||
Solver Frustration | Excel Discussion (Misc queries) | |||
Vlookup Frustration | Excel Worksheet Functions | |||
Vlookup Frustration | Excel Worksheet Functions | |||
Vlookup Frustration | Excel Worksheet Functions |