Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Toolbars Again!!!
Sorry to be a pain about this. The custom toolbar I created is displaying
when the workbook opens and hidden when the workbook closes, which is fine. I then wanted to copy the workbook to another location with another name, and attached the toolbar to the new workbook. However, the damn thing keeps looking back to the original workbook for the macros. Is the only way to create a new toolbar name for each workbook? -- Sharon |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Toolbars Again!!!
Hi Sharon.
When you created the custom toolbar, the toolbar was saved in "Excel.xlb" NOT the workbook that was open when you created it. So, when you created the buttons on the toolbar, you pointed them at the macros in the workbook that was open at the time. Now, when you open the toolbar (regardless of which workbooks are open), those macros look for the macros you've assigned to them...which are in the original workbook. Hope that explains what happened. To get around this, you might want to put code in the workbook that will create a toolbar on the fly each time you open the workbook [and destroy it each time you close the workbook]. That way the code for the toolbar as well as the macros in the workbook are ALL copied to a new workbook. John Walkenbach has a couple of excellent books out that explains this technique. Check out 'Excel 2003 Power Programming with VBA' at http://j-walk.com/ss/books/index.htm HTH, -- Gary Brown If this post was helpful, please click the ''Yes'' button next to ''Was this Post Helpfull to you?''. "Sharon" wrote: Sorry to be a pain about this. The custom toolbar I created is displaying when the workbook opens and hidden when the workbook closes, which is fine. I then wanted to copy the workbook to another location with another name, and attached the toolbar to the new workbook. However, the damn thing keeps looking back to the original workbook for the macros. Is the only way to create a new toolbar name for each workbook? -- Sharon |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Toolbars Again!!!
Sharon,
Here's a template for doing what Gary suggests. (j-walk's site will be more complete). You would paste this code into the ThisWorkbook module: Private Sub Workbook_Activate() Call make_menu End Sub Private Sub Workbook_Deactivate() Call delete_menu End Sub Sub make_menu() Dim cbar As CommandBar Dim cbarcontrol As CommandBarControl 'delete the previous version if it exists Call delete_menu Set cbar = Application.CommandBars.Add(Name:="tester", temporary:=True) cbar.Visible = True Set cbarcontrol = cbar.Controls.Add(Type:=msoButtonIcon) With cbarcontrol .FaceId = 2 .OnAction = "tester" End With End Sub Sub delete_menu() On Error Resume Next Application.CommandBars("tester").Delete On Error GoTo 0 End Sub Then create a macro "tester" in a regular module: Sub tester msgbox "tester" End Sub hth, Doug "Sharon" wrote in message ... Sorry to be a pain about this. The custom toolbar I created is displaying when the workbook opens and hidden when the workbook closes, which is fine. I then wanted to copy the workbook to another location with another name, and attached the toolbar to the new workbook. However, the damn thing keeps looking back to the original workbook for the macros. Is the only way to create a new toolbar name for each workbook? -- Sharon |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Toolbars Again!!!
Gary and Doug - thanks so much - helped a lot and will make the toolbar on
the fly. -- Sharon "Doug Glancy" wrote: Sharon, Here's a template for doing what Gary suggests. (j-walk's site will be more complete). You would paste this code into the ThisWorkbook module: Private Sub Workbook_Activate() Call make_menu End Sub Private Sub Workbook_Deactivate() Call delete_menu End Sub Sub make_menu() Dim cbar As CommandBar Dim cbarcontrol As CommandBarControl 'delete the previous version if it exists Call delete_menu Set cbar = Application.CommandBars.Add(Name:="tester", temporary:=True) cbar.Visible = True Set cbarcontrol = cbar.Controls.Add(Type:=msoButtonIcon) With cbarcontrol .FaceId = 2 .OnAction = "tester" End With End Sub Sub delete_menu() On Error Resume Next Application.CommandBars("tester").Delete On Error GoTo 0 End Sub Then create a macro "tester" in a regular module: Sub tester msgbox "tester" End Sub hth, Doug "Sharon" wrote in message ... Sorry to be a pain about this. The custom toolbar I created is displaying when the workbook opens and hidden when the workbook closes, which is fine. I then wanted to copy the workbook to another location with another name, and attached the toolbar to the new workbook. However, the damn thing keeps looking back to the original workbook for the macros. Is the only way to create a new toolbar name for each workbook? -- Sharon |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom Toolbars | Excel Worksheet Functions | |||
Custom Toolbars? | Excel Programming | |||
Custom Toolbars..? | Excel Discussion (Misc queries) | |||
Custom toolbars | Excel Discussion (Misc queries) | |||
Custom ToolBars | Excel Programming |