|
saving toolbar buttons on custom toolbar
I've created a custom toolbar that is Attached to a particular workbook
file. Every time I open that file, the custom toolbar appears as it is supposed to. However, none of the toolbar buttons that I put on the toolbar appear with it, even though I saved the workbook after I put the buttons one during the last session. I do have some code that deletes the custom toolbar from the workbook when the workbook closes so the toolbar is only open when that file is open, but I wouldn't expect this to have any effect on whether the toolbar buttons remain on the toolbar. On the outside chance this is causing my problem, here's the code I'm using for that purpose: *************** Private Sub Workbook_BeforeClose(Cancel As Boolean) On Error Resume Next Application.CommandBars("TimesheetToCalStars").Del ete End Sub Sub Auto_Close() On Error Resume Next Application.CommandBars("TimesheetToCalStars").Del ete End Sub *************** What can I do to get these toolbar buttons to remain on the toolbar the next time I open the workbook? Thanks in advance. |
saving toolbar buttons on custom toolbar
If you want to do it this way, I think youll have to
reconstruct the toolbar and make it invisible on close instead of deleting it (".Visible = False"). However, you'll probably also have to change the toolbar name as well. I confess to not fully understanding the issue, but, as I have it, once a toolbar with a given name is on record in the .xlb file it will always reproduce the first toolbar on record with this name EVEN AFTER YOU HAVE DELETED IT !!! Assuming you find this to be the case and if you don't want to change the name then you can delete the .xlb file so long as there are no other needed custom toolbars listed on the computer. Excel will recreate it if it cannot find it. Preferred alternative: I believe most professionals (I'm not one) prefer to create their toolars on the fly to avoid complications. A "quick and dirty" reproduction of what I do follows. Note that the toolbar's Temporary parameter is set to True so it will automatically delete upon closing Excel (no code required). 'Paste to the ThisWorkbook Module: Option Base 1 Private Sub Workbook_Open() Call MakeToolBar End Sub Private Sub MakeToolBar() Dim NewTB As CommandBar, NewBut As CommandBarButton Dim Ar1 As Variant, Ar2 As Variant Dim Ar3 As Variant, Ar4 As Variant Dim i As Integer Ar1 = Array ("Calculate", "ListNames", "DeleteBlanks", "ListOT") Ar2 = Array(283, 222, 1786, 521) Ar3 = Array("Calculate hours", "List names", "Delete blank entries", "List OT hours") Ar4 = Array("Calculates employee weekly hours", "Lists employee names", "Deletes blank entries", "Lists total OT hours for group") Application.ScreenUpdating = False Set NewTB = Application.CommandBars.Add(Name:="TestTB", Temporary:=True) NewTB.Visible = True For i = 1 To 4 Set NewBut = NewTB.Controls.Add(Type:=msoControlButton) With NewBut .OnAction = Ar1(i) .FaceId = Ar2(i) .Caption = Ar3(i) .TooltipText = Ar4(i) .Style = msoButtonIconAndCaption End With Next Application.ScreenUpdating = True End Sub Hope I was of some help. A reminder that I'm only a student. Regards, Greg Wilson -----Original Message----- I've created a custom toolbar that is Attached to a particular workbook file. Every time I open that file, the custom toolbar appears as it is supposed to. However, none of the toolbar buttons that I put on the toolbar appear with it, even though I saved the workbook after I put the buttons one during the last session. I do have some code that deletes the custom toolbar from the workbook when the workbook closes so the toolbar is only open when that file is open, but I wouldn't expect this to have any effect on whether the toolbar buttons remain on the toolbar. On the outside chance this is causing my problem, here's the code I'm using for that purpose: *************** Private Sub Workbook_BeforeClose(Cancel As Boolean) On Error Resume Next Application.CommandBars("TimesheetToCalStars").Del ete End Sub Sub Auto_Close() On Error Resume Next Application.CommandBars("TimesheetToCalStars").Del ete End Sub *************** What can I do to get these toolbar buttons to remain on the toolbar the next time I open the workbook? Thanks in advance. . |
saving toolbar buttons on custom toolbar
Greg - thank you for the explanation and the code. In addition to
addressing the question I asked, it was a good illustration of using arrays and VBA code to create a toolbar. This is going to be helpful to me in several areas. Having said that, I'm surprised that if you have an Attached toolbar, you still need to recreate it in order to use it in the workbook. If that't the case, what's the point of having an attached toolbar in the first place? Is there anyone out there who can tell us if there's a way to get an attached toolbar to appear with all it's toolbar buttons on in when you open the workbook in which the toolbar is attached? Thanks much. |
saving toolbar buttons on custom toolbar
My bet is you missed a step when you did it manually.
Jan Karel Pieterse has posted some nice instructions for attached toolbars: http://google.com/groups?threadm=083...0a% 40phx.gbl And I don't think you need both Auto_close and workbook_beforeclose in your project (since they're doing the same thing). Paul James wrote: Greg - thank you for the explanation and the code. In addition to addressing the question I asked, it was a good illustration of using arrays and VBA code to create a toolbar. This is going to be helpful to me in several areas. Having said that, I'm surprised that if you have an Attached toolbar, you still need to recreate it in order to use it in the workbook. If that't the case, what's the point of having an attached toolbar in the first place? Is there anyone out there who can tell us if there's a way to get an attached toolbar to appear with all it's toolbar buttons on in when you open the workbook in which the toolbar is attached? Thanks much. -- Dave Peterson |
saving toolbar buttons on custom toolbar
Hi Paul,
I actually meant for you to abandon altogether the method of creating a toolbar and attaching it to the workbook. Instead, just paste my code to the ThisWorkbook module. To run my demo code, you'll have to create four dummy macros in a standard module with the referenced macro names. These will be called when you press the buttons. Delete your toolbar and remove the reference from the Attached list. My understanding of attaching toolbars to a workbook is that the original version of the toolbar when it was attached is the one that is recalled upon opening the workbook. It does not automatically update to a revised version. You must manually unattach the toolbar, revise it, then reattach it. My theory as to your situation is that a version of your toolbar without buttons was originally attached if that is possible. To resolve this, try unattahing the toolbar, deleting it, then recreating and reattaching. Note that an experiment I conducted proved that even if you delete an attached toolbar (as does your code), the original version (when it was attached) will reappear when the workbook is opened. I also suggest that you NOT delete the toolbar upon close but to make it invisible instead and also make it visible again upon reopen using the Workbook_BeforeClose and Workbook_Open events respectively. If my suggestions are not successful, note that if you delete the .xlb file then the old version will be destroyed. Of course, it will destroy ALL toolbar customizations as well. Excel automatically recreates the .xlb file if it has been deleted. The main reason I prefer the create-on-the-fly method is because if someone saves your workbook under a different name (using SaveAs) then all the macro references will link to the new workbook and will no longer work for the original version. If you have 20 or so buttons then this is a real bitch !!! And from my experience, this will happen a whole lot !!! Try this experimentally but close the new workbook after creating it. If you have created your own button images using the button image editor then there is a way to deal with this. Good luck, Greg -----Original Message----- Greg - thank you for the explanation and the code. In addition to addressing the question I asked, it was a good illustration of using arrays and VBA code to create a toolbar. This is going to be helpful to me in several areas. Having said that, I'm surprised that if you have an Attached toolbar, you still need to recreate it in order to use it in the workbook. If that't the case, what's the point of having an attached toolbar in the first place? Is there anyone out there who can tell us if there's a way to get an attached toolbar to appear with all it's toolbar buttons on in when you open the workbook in which the toolbar is attached? Thanks much. . |
saving toolbar buttons on custom toolbar
Thanks for the information, Dave. I'll check out Jan's instructions.
You're probably right that I missed a step. Greg Wilson had some specific suggestions about what I might be missing in his last message, so I'm going to check those out as well. |
saving toolbar buttons on custom toolbar
Hi Paul,
What happens when you assign a macro to a toolbar button is that not only is the name of the macro referenced for the particular button but the workbook of origin as well. To prove this, add a button to the Worksheet Menu Bar at the end (far right) and assign it a macro. Then run the following code: Sub TestMacroAssignment() Dim X As Integer X = Application.CommandBars(1).Controls.Count MsgBox Application.CommandBars(1).Controls(X).OnAction End Sub You will get a message referencing the workbook followed by the name of the macro that was assigned. If your workbook is named "Timesheets.xls" then you'll get a message something like: 'C:\Windows\Desktop\Timesheet.xls'!MyMacro Remember, toolbars are global to Excel as opposed to a particular workbook. When you save a workbook under a different name you DO NOT also create a duplicate series of toolbars including custom toolbars. What happens when someone saves the workbook under a different name (SaveAs) is that Excel reassigns the workbook references for custom toolbar button macros to the new workbook. In other words, if you were to now run the above code with the new workbook open you would now return a message something like this: "C:\Windows\MyDocuments\SalesDivTimesheets.xls'!My Macro After some evil person has done this and closed the new workbook, if you now come along and open the original workbook and click on the same button that was added to the Worksheet Menu Bar, it will return an error message something like: Cannot find macro "MyMacro" or such. This is because it is looking for the code in the new workbook which is closed. In my experience, this is likely to occur frequently by people who treat your workbook as a template to crunch the numbers and then save their work under a different name. However, if you follow my suggestion, whenever the original or any duplicate workbooks are opened, the toolbars and buttons are created anew and the macros are also assigned anew to each button. Upon closing the workbooks, they are also deleted automatically. Problem solved. I suggest you check it out experimentally. As I said previously, if you have edited the button images and want to use the customized images, there is also a way to accomodate this. Regards, Greg -----Original Message----- Greg - in your last message, you said something I didn't understand. When you said: "if someone saves your workbook under a different name (using SaveAs) then all the macro references will link to the new workbook and will no longer work for the original version. " I don't understand what "macro references" are, and how saving the workbook under a different name would change anything. All my VBA procedures are stored in modules that are included in the workbook. If you save the workbook under a different name, both the original workbook and the new one each contain the modules with those VBA procedures in them. And nowhere in my code do I make any mention of the name of the workbook file. I do have a line that uses the "ThisWorkbook" object, but that isn't affected by the name of the workbook. So my expectation is that my VBA procedures will work just fine regardless of the name of the workbook. Am I wrong about that? By the way, thanks for the explanation of attaching the toolbar to the workbook. I'm going to test your theory, but it makes sense, and I think you're right that it saves only the original version. I'm going to build a new toolbar and see if I can overcome the problem that way. . |
saving toolbar buttons on custom toolbar
I like Greg's suggestion better than attaching the toolbar, too. But
sometimes it's good to know why something didn't work. "Paul James" wrote in message news:<5YIXa.69676$YN5.54371@sccrnsc01... Thanks for the information, Dave. I'll check out Jan's instructions. You're probably right that I missed a step. Greg Wilson had some specific suggestions about what I might be missing in his last message, so I'm going to check those out as well. |
saving toolbar buttons on custom toolbar
Greg - again, thank you for all the information. I ran the
TestMacroAssignment() sub and you're right, it did display the workbook name in the macro assignment designation. But then that code displays the macro assignment for a menu bar item. Do you know what the code would be to display the same thing for the toolbar buttons? The reason I ask is that I opened another workbook that I created some time ago that has a custom toolbar attached to the workbook. This workbook doesn't recreate the toolbar when it opens, the toolbar is simply attached to the workbook. I renamed that workbook to see if the toolbar would still open when the workbook opens (it does), and the toolbar buttons also function properly, even though they're in a workbook with a different name. So it would seem that the macro assignments on those toolbar buttons are not dependent on the name of the workbook. When I close the workbook, I delete the toolbars using "Application.CommandBars("toolbar name").Delete" in the Auto_Close sub. And after that workbook is closed, the toolbar no longer appears in the list of toolbars in the application. These observations seem to suggest that the toolbar goes with the workbook. I think one of the key steps might be that you need to place all of the buttons and macro assignments in the toolbar before you Attach it to the workbook. But it seems to work well, even after you rename the workbook, without having to rebuild the toolbar every time you open the workbook. Try it yourself as an experiment, following these steps: - create a Sub procedure - create a new toolbar, place a new button on the toolbar and assign that sub procedure to the button - before you close the Customize dialog, go back to the Toolbars tab and Attach the new toolbar to the workbook. - add this sub to a module in the workbook: Sub Auto_Close() On Error Resume Next Application.CommandBars("toolbar name").Delete End Sub - try out the toolbar button to confirm that it's calling the sub - save the workbook, close then reopen, then save it with another name - open the renamed workbook and you'll see that the custom toolbar is still there You'll also notice that after you close the workbook, the custom toolbar no longer appears in the list of toolbars in the application. It's only there when the workbook that contains the "attached" toolbar is open. In view of the foregoing, it seems to me that we don't need to recreate custom toolbars in VBA code in order to use them with a workbook. Unless there's something I'm missing. Paul |
saving toolbar buttons on custom toolbar
Dave - Jan said that "if you want a certain menu-item or toolbar button to
be unavailable whilst certain workbooks are visible (or only available whilst one workbook is there), you need to use VBA code to show and hide them." I think he's mistaken about that. For my explanation on this, please see my latest reply to Greg Wilson elsewhere in this thread. Again, you were exactly right when you said I missed a step when I did it manually. A "attached" the toolbar to the workbook before I put the command buttons on the toolbar. The key is to build the toolbar by putting everything on it before attaching it to the workbook. Thanks. |
correction
I need to correct something I said in my previous post here. The delete
method of the toolbars object needs to be located in the BeforeClose event of the workbook, not the Auto_Close. This is the way to programmatically get rid of the toolbar when the workbook to which it's attached isn't open. Here's the code: Private Sub Workbook_BeforeClose(Cancel As Boolean) On Error Resume Next Application.CommandBars("TimesheetToCalStars").Del ete End Sub Sorry for any confusion. Paul |
saving toolbar buttons on custom toolbar
Hi Paul,
First, I appologize for not checking out my theory before posting as I was wrong. It was late and I was tired. The same is true tonight unfortunately. I keyed into your post because this issue had been the bain of my existence for some time until I arrived at my solution (which does work). From my observations, when you save a workbook under a different name then the macros get reasigned to the new workbook. If you now close the new workbook and go back and open the original, when you push a button on the toolbar, it will automactically open the new workbook (contrary to what I said) in order to access the macro code. This assumes that it can find the new workbook. To prove that the new workbook gets opened automatically when you push a button, check the list of open workbooks using the Window menu item in the Worksheet Menu Bar before and after pushing a button. What was happening that drove me nuts was that frequently, someone would use my workbook as a template to crunch the numbers and then save the results (the workbook) either to some place on our intranet, or alternatively, to a floppy, and then walk off with the floppy. The macros would reassign to the new workbook; i.e., to someplace on the intranet or to a floppy that rode off into the sunset. Therefore, when you subsequently attempted to run the macros from the origninal workbook, Excel would look for the code in the new workbook. But, in my case, it couldn't find the new workbook. I'm not sure why, but Excel couldn't access the new workbook through the intranet and obviously couldn't do so for an absent floppy. Therefore, I would have to manually reassign the macros to the toolbar buttons. What I've learned from you is that after attaching a toolbar to a workbook, it is crucial to have code that DELETES it on close as opposed to making it invisible. (This is what you were doing and the exact opposite to what I advised so I again appologize). The importance of this is that: i) If someone makes a copy, then it too has a record of "Toolbar X" attached and also has code that will delete "Toolbar X" on close. So "Toolbar X" always gets deleted. ii) When a workbook with an attached toolbar is opened and it can't find the attached toolbar because it has been deleted then it will RECREATE it. The recreated version is the one that existed when it was first attached to that workbook. And the toolbar button macro assignments will of course point to macros contained in the workbook. iii) On the other hand, if "Toolbar X" does not get deleted then it will not get recreated and so a toolbar with macro assignments pointing to another workbook will persist. This is not a brilliant insight but a major transformation of my thinking. I never read anywhere that you should delete the toolbar (seems like a dumb idea after all that work). I do recall examples of making it invisible. I did caution you that I'm only a student. I've done a few things for my employer unpaid and study VBA for personal development. I hope I'm not still confused and just think I understand. What puzzles me is that the create-on-the-fly method that I use is commonly done by many professionals 'in order to avoid complications'. Ironically, THANKS FOR THE HELP!!! I think I'll stop responding to these posts until I get more experience. So did you ever get your toolbar to work ??? Best regards, Greg -----Original Message----- Greg - again, thank you for all the information. I ran the TestMacroAssignment() sub and you're right, it did display the workbook name in the macro assignment designation. But then that code displays the macro assignment for a menu bar item. Do you know what the code would be to display the same thing for the toolbar buttons? The reason I ask is that I opened another workbook that I created some time ago that has a custom toolbar attached to the workbook. This workbook doesn't recreate the toolbar when it opens, the toolbar is simply attached to the workbook. I renamed that workbook to see if the toolbar would still open when the workbook opens (it does), and the toolbar buttons also function properly, even though they're in a workbook with a different name. So it would seem that the macro assignments on those toolbar buttons are not dependent on the name of the workbook. When I close the workbook, I delete the toolbars using "Application.CommandBars("toolbar name").Delete" in the Auto_Close sub. And after that workbook is closed, the toolbar no longer appears in the list of toolbars in the application. These observations seem to suggest that the toolbar goes with the workbook. I think one of the key steps might be that you need to place all of the buttons and macro assignments in the toolbar before you Attach it to the workbook. But it seems to work well, even after you rename the workbook, without having to rebuild the toolbar every time you open the workbook. Try it yourself as an experiment, following these steps: - create a Sub procedure - create a new toolbar, place a new button on the toolbar and assign that sub procedure to the button - before you close the Customize dialog, go back to the Toolbars tab and Attach the new toolbar to the workbook. - add this sub to a module in the workbook: Sub Auto_Close() On Error Resume Next Application.CommandBars("toolbar name").Delete End Sub - try out the toolbar button to confirm that it's calling the sub - save the workbook, close then reopen, then save it with another name - open the renamed workbook and you'll see that the custom toolbar is still there You'll also notice that after you close the workbook, the custom toolbar no longer appears in the list of toolbars in the application. It's only there when the workbook that contains the "attached" toolbar is open. In view of the foregoing, it seems to me that we don't need to recreate custom toolbars in VBA code in order to use them with a workbook. Unless there's something I'm missing. Paul . |
| All times are GMT +1. The time now is 03:43 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com