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 |
Commandbar frustration.
Julian,
I use something along the lines of Option 2. I store the specialist pictures as images on a sheet in the xla, and paste these to buttons. What does your code look like? -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Julian Cox" wrote in message ... 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 |
Commandbar frustration.
"Bob Phillips" wrote:
Hi bob I use something along the lines of Option 2. I store the specialist pictures as images on a sheet in the xla, and paste these to buttons. Glad I'm not barking up the wrong tree. I prefer this option because all the attributes for all the buttons are visible on one sheet of the ..vba which makes it nice and maintainable. What does your code look like? This is the critical part ' Work down column A, theres a number in the cell if there ' is a button to create For Each Cell In Worksheets("Button Images").Range("A2:A30") If Not (StrComp(Cell, "") = 0) Then ' Column C contains the picture name ImageName = Cell.Offset(0, 2) ' Column D contains the Caption Text CaptionText = Cell.Offset(0, 3) ' Column E contains the macro name ButtonMacro = Cell.Offset(0, 4) ' Set transparency colour on the picture ActiveSheet.Shapes(ImageName).Select Selection.ShapeRange.PictureFormat.TransparentBack ground _ = msoTrue Selection.ShapeRange.PictureFormat.TransparencyCol or _ = RGB(214, 211, 206) Selection.ShapeRange.Fill.Visible = msoFalse ' not sure what this does Set ButtonHandle = CommandBars(DestBar).Controls.Add Worksheets("Button Images").DrawingObjects(ImageName).Copy With ButtonHandle .Caption = CaptionText .Visible = True .State = msoButtonUp .OnAction = ButtonMacro .PasteFace .Enabled = False End With End If Next Cell It works perfectly except that the buttons are all 16x16 grey squares when disabled. Cheers Julian |
Commandbar frustration.
Maybe Excel VBA help can shed some light on #1
When a workbook is embedded in another application and activated by the user by double-clicking the workbook, using this property with a Workbook object returns the set of Microsoft Excel command bars available within the other application. At all other times, using this property with a Workbook object returns Nothing. Your probably beyond this article, but it might be useful: http://msdn.microsoft.com/library/techart/ofcmdbar.htm http://support.microsoft.com/default...71&Product=ofd How To Create a Transparent Picture For Office CommandBar Buttons http://support.microsoft.com/default...50&Product=ofd How To Add a Transparent Icon to a Toolbar Button -- Regards, Tom Ogilvy "Julian Cox" wrote in message ... 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 |
Commandbar frustration.
"Tom Ogilvy" wrote:
Hi Tom Maybe Excel VBA help can shed some light on #1 When a workbook is embedded in another application and activated by the user by double-clicking the workbook, using this property with a Workbook object returns the set of Microsoft Excel command bars available within the other application. At all other times, using this property with a Workbook object returns Nothing. That goes stright over my head, even on the third reading. Wibble. What section of the help is it in? Maybe the full context will help. All it appears to be saying is that using application.commandbars returns the commandbars in the container application even if that is not excel. For example if an .xls was embedded in a word document using application.commandbars would return the word command bars. Which is obvious. Looking at the VBA help ThisWorkBook is exactly what I should be using in my situation. Your probably beyond this article, but it might be useful: http://msdn.microsoft.com/library/techart/ofcmdbar.htm Some of it is certainly useful, I shall come back to the section on user-proofing command bars. http://support.microsoft.com/default...71&Product=ofd How To Create a Transparent Picture For Office CommandBar Buttons I thought this one had cracked it, Mask properties sound like exactly what I need. Then I read it again and spotted 'Office XP offers a new Picture and Mask property....'. Nuts, I'm on xl 2000. Ho Hum. http://support.microsoft.com/default...50&Product=ofd How To Add a Transparent Icon to a Toolbar Button This is just pasting the face from an Icon resource file instead of from a picture object. I was really hoping not to need to do something like this as I want to be able to distribute my code in a single .xla file. Can an icon resource be embedded in a sheet like a picture can? I guess I'll have to try it to find out if the paste is done correctly. There is some hope as the blank button used as the background is truly blank, not background coloured. It all depends on how intelligent DrawIconEx() is I think. Can an icon have null data, the 17th colour, for pixels like a button face does? Thanks for the links. Cheers Julian |
Commandbar frustration.
As I read it, unless you are going to be working in a browser or similar,
you should always use Application.CommandBars. transparant icons. The first article on Transparent did say that about XP, but if you read further, as I understood it, and I only scanned it, it gave you a method that did not depend on these XP only attributes. I assume its method would be somewhat akin to the second article. It isn't something I have had a need to do, so I haven't played with it, but it appeared to explain the situation and my hope was that you could figure out a solution based on the information provided. -- Regards, Tom Ogilvy "Julian Cox" wrote in message ... "Tom Ogilvy" wrote: Hi Tom Maybe Excel VBA help can shed some light on #1 When a workbook is embedded in another application and activated by the user by double-clicking the workbook, using this property with a Workbook object returns the set of Microsoft Excel command bars available within the other application. At all other times, using this property with a Workbook object returns Nothing. That goes stright over my head, even on the third reading. Wibble. What section of the help is it in? Maybe the full context will help. All it appears to be saying is that using application.commandbars returns the commandbars in the container application even if that is not excel. For example if an .xls was embedded in a word document using application.commandbars would return the word command bars. Which is obvious. Looking at the VBA help ThisWorkBook is exactly what I should be using in my situation. Your probably beyond this article, but it might be useful: http://msdn.microsoft.com/library/techart/ofcmdbar.htm Some of it is certainly useful, I shall come back to the section on user-proofing command bars. http://support.microsoft.com/default...71&Product=ofd How To Create a Transparent Picture For Office CommandBar Buttons I thought this one had cracked it, Mask properties sound like exactly what I need. Then I read it again and spotted 'Office XP offers a new Picture and Mask property....'. Nuts, I'm on xl 2000. Ho Hum. http://support.microsoft.com/default...50&Product=ofd How To Add a Transparent Icon to a Toolbar Button This is just pasting the face from an Icon resource file instead of from a picture object. I was really hoping not to need to do something like this as I want to be able to distribute my code in a single .xla file. Can an icon resource be embedded in a sheet like a picture can? I guess I'll have to try it to find out if the paste is done correctly. There is some hope as the blank button used as the background is truly blank, not background coloured. It all depends on how intelligent DrawIconEx() is I think. Can an icon have null data, the 17th colour, for pixels like a button face does? Thanks for the links. Cheers Julian |
Commandbar frustration.
"Tom Ogilvy" wrote:
As I read it, unless you are going to be working in a browser or similar, you should always use Application.CommandBars. transparant icons. The first article on Transparent did say that about XP, but if you read further, as I understood it, and I only scanned it, it gave you a method that did not depend on these XP only attributes. I assume its method would be somewhat akin to the second article. It isn't something I have had a need to do, so I haven't played with it, but it appeared to explain the situation and my hope was that you could figure out a solution based on the information provided. Oops, my mistake. Note to self, don't jump to conclusions. Thanks Tom. Regards Julian |
All times are GMT +1. The time now is 10:20 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com