Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros in a template run from Custom Toolbar attaching to wrong file.
I've created a Excel template with a number of macros to
run functions in this system. I've created a custom toolbar to run a number of these macros. The problem I've encountered is that the toolbar will become attached to a workbook saved from this template and when the template is opened next, the toolbar buttons cause the old worksheet to open and the macros running from the toolbar are in the old book and not the new one. I've tried "attaching" the toolbar; opening and closing the toolbar when the workbooks open and close; and deleting the toolbar every time a workbook closes. None of these things have solved the problem. Does anyone have a fuller understanding of how to make a custom toolbar run properly with templates and workbooks created from this template? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros in a template run from Custom Toolbar attaching to wrong file.
Linda,
I find that having the workbook activate or workbook open macro (in the ThisWorkbook module) avoids a lot of hassel. Try the below code. When the toolbar only needs be used for one workbook out of many - the activate and deactivate events work great... ..OnAction is the name of macros in the workbook. ''''''''''''''''''''''''''''''' Private Sub Workbook_BeforeClose(Cancel As Boolean) ' destroy the toolbar before closing On Error Resume Next Application.CommandBars("myBar").Delete On Error GoTo 0 End Sub Private Sub Workbook_Open() Dim oCBMenuBar As CommandBar Dim oCBCLeave As CommandBarControl Dim iMenu As Integer Dim i As Integer ' remove toolbar if it exists On Error Resume Next Application.CommandBars("myBar").Delete On Error GoTo 0 ' build toolbar Set oCBMenuBar = Application.CommandBars.Add(Name:="myBar") With oCBMenuBar With .Controls.Add(Type:=msoControlButton) .BeginGroup = True .Caption = "My Toolbar" .Style = msoButtonCaption End With With .Controls.Add(Type:=msoControlButton) .FaceId = 155 .TooltipText = "Previous month" .OnAction = "prevMonth" End With With .Controls.Add(Type:=msoControlButton) .FaceId = 156 .TooltipText = "Next month" .OnAction = "nextMonth" End With With .Controls.Add(Type:=msoControlButton) .FaceId = 157 .TooltipText = "Last month" .OnAction = "lastMonth" End With With .Controls.Add(Type:=msoControlButton) .BeginGroup = True .Caption = "Summary" .Style = msoButtonCaption .TooltipText = "Show summary sheet" .OnAction = "gotoSummary" End With .Position = msoBarLeft .Protection = msoBarNoMove .Visible = True End With End Sub ''''''''''''''''''''''''''''''''''''' -- hth steveB (Remove 'NOSPAM' from email address if contacting me direct) "Linda" wrote in message ... I've created a Excel template with a number of macros to run functions in this system. I've created a custom toolbar to run a number of these macros. The problem I've encountered is that the toolbar will become attached to a workbook saved from this template and when the template is opened next, the toolbar buttons cause the old worksheet to open and the macros running from the toolbar are in the old book and not the new one. I've tried "attaching" the toolbar; opening and closing the toolbar when the workbooks open and close; and deleting the toolbar every time a workbook closes. None of these things have solved the problem. Does anyone have a fuller understanding of how to make a custom toolbar run properly with templates and workbooks created from this template? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros in a template run from Custom Toolbar attaching to wrong file.
Assign and unassign the macros when the workbook is opened or closed.
Sub Auto_open() Toolbars("LogForm").Visible = True Toolbars("LogForm").ToolbarButtons(1).OnAction = "PhoneLogger" Toolbars("LogForm").ToolbarButtons(2).OnAction = "LateLogger" End Sub Sub Auto_close() On Error Resume Next Toolbars("LogForm").ToolbarButtons(1).OnAction = "" Toolbars("LogForm").ToolbarButtons(2).OnAction = "" Toolbars("LogForm").Visible = False Toolbars("LogForm").Delete On Error GoTo 0 End Sub HTH, Greg "Linda" wrote in message ... I've created a Excel template with a number of macros to run functions in this system. I've created a custom toolbar to run a number of these macros. The problem I've encountered is that the toolbar will become attached to a workbook saved from this template and when the template is opened next, the toolbar buttons cause the old worksheet to open and the macros running from the toolbar are in the old book and not the new one. I've tried "attaching" the toolbar; opening and closing the toolbar when the workbooks open and close; and deleting the toolbar every time a workbook closes. None of these things have solved the problem. Does anyone have a fuller understanding of how to make a custom toolbar run properly with templates and workbooks created from this template? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros in a template run from Custom Toolbar attaching to wrong file.
Steve B,
Thank you for this info. I must digest this code before I try to use it. But just one question. If a user has saved any number of files from my template and then has more than one of them open at a time, will the tool bar that appears work in the Current Workbook only, or how will the toolbar buttons know which book's macros it's running. Do you know if there is any way to create something like a toolbar (perhaps a "form"?) within a workbook that would basically sit on top of each sheet as you move from sheet to sheet? Then it would be saved in that workbook alone, not in the .xlb file for all excel workbooks. I guess your code would actually cause the toolbar to be deleted before you can exit Excel and then it doesn't get saved in the .xlb file, right? Thanks again for your help. Linda -----Original Message----- Linda, I find that having the workbook activate or workbook open macro (in the ThisWorkbook module) avoids a lot of hassel. Try the below code. When the toolbar only needs be used for one workbook out of many - the activate and deactivate events work great... ..OnAction is the name of macros in the workbook. ''''''''''''''''''''''''''''''' Private Sub Workbook_BeforeClose(Cancel As Boolean) ' destroy the toolbar before closing On Error Resume Next Application.CommandBars("myBar").Delete On Error GoTo 0 End Sub Private Sub Workbook_Open() Dim oCBMenuBar As CommandBar Dim oCBCLeave As CommandBarControl Dim iMenu As Integer Dim i As Integer ' remove toolbar if it exists On Error Resume Next Application.CommandBars("myBar").Delete On Error GoTo 0 ' build toolbar Set oCBMenuBar = Application.CommandBars.Add (Name:="myBar") With oCBMenuBar With .Controls.Add(Type:=msoControlButton) .BeginGroup = True .Caption = "My Toolbar" .Style = msoButtonCaption End With With .Controls.Add(Type:=msoControlButton) .FaceId = 155 .TooltipText = "Previous month" .OnAction = "prevMonth" End With With .Controls.Add(Type:=msoControlButton) .FaceId = 156 .TooltipText = "Next month" .OnAction = "nextMonth" End With With .Controls.Add(Type:=msoControlButton) .FaceId = 157 .TooltipText = "Last month" .OnAction = "lastMonth" End With With .Controls.Add(Type:=msoControlButton) .BeginGroup = True .Caption = "Summary" .Style = msoButtonCaption .TooltipText = "Show summary sheet" .OnAction = "gotoSummary" End With .Position = msoBarLeft .Protection = msoBarNoMove .Visible = True End With End Sub ''''''''''''''''''''''''''''''''''''' -- hth steveB (Remove 'NOSPAM' from email address if contacting me direct) "Linda" wrote in message ... I've created a Excel template with a number of macros to run functions in this system. I've created a custom toolbar to run a number of these macros. The problem I've encountered is that the toolbar will become attached to a workbook saved from this template and when the template is opened next, the toolbar buttons cause the old worksheet to open and the macros running from the toolbar are in the old book and not the new one. I've tried "attaching" the toolbar; opening and closing the toolbar when the workbooks open and close; and deleting the toolbar every time a workbook closes. None of these things have solved the problem. Does anyone have a fuller understanding of how to make a custom toolbar run properly with templates and workbooks created from this template? . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros in a template run from Custom Toolbar attaching to wrong file.
Thank you for your help. I've had a similar answer from
SteveB, but had some additional concerns about this method. If you see what I wrote in reponse to SteveB, I wonder what you would think. Thanks again. Linda -----Original Message----- Assign and unassign the macros when the workbook is opened or closed. Sub Auto_open() Toolbars("LogForm").Visible = True Toolbars("LogForm").ToolbarButtons(1).OnAction = "PhoneLogger" Toolbars("LogForm").ToolbarButtons(2).OnAction = "LateLogger" End Sub Sub Auto_close() On Error Resume Next Toolbars("LogForm").ToolbarButtons(1).OnAction = "" Toolbars("LogForm").ToolbarButtons(2).OnAction = "" Toolbars("LogForm").Visible = False Toolbars("LogForm").Delete On Error GoTo 0 End Sub HTH, Greg "Linda" wrote in message ... I've created a Excel template with a number of macros to run functions in this system. I've created a custom toolbar to run a number of these macros. The problem I've encountered is that the toolbar will become attached to a workbook saved from this template and when the template is opened next, the toolbar buttons cause the old worksheet to open and the macros running from the toolbar are in the old book and not the new one. I've tried "attaching" the toolbar; opening and closing the toolbar when the workbooks open and close; and deleting the toolbar every time a workbook closes. None of these things have solved the problem. Does anyone have a fuller understanding of how to make a custom toolbar run properly with templates and workbooks created from this template? . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom toolbar for template - macros not working | Excel Discussion (Misc queries) | |||
Source Excel Template Pops Up when using a custom toolbar | Excel Discussion (Misc queries) | |||
Custom toolbar and macros | Excel Discussion (Misc queries) | |||
Custom Toolbar Macros | Excel Programming | |||
Attaching Macros to Custom Buttons | Excel Programming |