Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Does a toolbar already exist?
I have some code in a workbook which creates a new custom toolbar when the
workbook opens and deletes it when the workbook is closed. (Excel 97) The same process is present in earlier versions of the workbook from previous years. Sometimes I need to open one of those earlier versions while the current version is already open. This produces an error message dialog with runtime error '5'. Hitting "End" stops the process and gets rid of it, but I would like to insert some additional code at the start to check if the custom toolbar already exists, and stop the code running if it does. Basically "if (toolbar) exists then exit sub" I have tried a variety of possibilities but no luck so far. It is probably something simple but to date it eludes me. I'm getting a bit rusty at coding! -- Drew Paterson Paraparaumu New Zealand |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Does a toolbar already exist?
In the routine that creates your bar start by deleting any similarly named
bar that might exist, but under an error handler in case it doesn't exist. Use the same routine to delete the bar when your workbook closes (assuming it's not needed any more) Sub openEventTest() ' call from Workbook_Open myMenu True End Sub Sub closeEventTest() ' call from Workbook_Close myMenu False End Sub Sub myMenu(bCreate As Boolean) Dim cbr As CommandBar Dim cbt As CommandBarButton On Error Resume Next Set cbr = Application.CommandBars("TestBar") If Not cbr Is Nothing Then cbr.Delete End If On Error GoTo 0 If bCreate Then Set cbr = Application.CommandBars.Add("TestBar", temporary:=True) Set cbt = cbr.Controls.Add(msoControlButton) With cbt .Caption = "press to run myMacro" .OnAction = "myMacro" .Style = msoButtonCaption .Visible = True End With End If cbr.Visible = True End Sub Sub myMacro() MsgBox "Hello from myMacro" End Sub Regards, Peter T "Drew Paterson" wrote in message ... I have some code in a workbook which creates a new custom toolbar when the workbook opens and deletes it when the workbook is closed. (Excel 97) The same process is present in earlier versions of the workbook from previous years. Sometimes I need to open one of those earlier versions while the current version is already open. This produces an error message dialog with runtime error '5'. Hitting "End" stops the process and gets rid of it, but I would like to insert some additional code at the start to check if the custom toolbar already exists, and stop the code running if it does. Basically "if (toolbar) exists then exit sub" I have tried a variety of possibilities but no luck so far. It is probably something simple but to date it eludes me. I'm getting a bit rusty at coding! -- Drew Paterson Paraparaumu New Zealand |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Does a toolbar already exist?
You could perform the delete function both at the beginning and at the
end. Then, it would kill it, and then re-create it. If it isn't already up, I do not know if that would produce an error. On Sun, 3 Jan 2010 21:15:27 +1300, "Drew Paterson" wrote: I have some code in a workbook which creates a new custom toolbar when the workbook opens and deletes it when the workbook is closed. (Excel 97) The same process is present in earlier versions of the workbook from previous years. Sometimes I need to open one of those earlier versions while the current version is already open. This produces an error message dialog with runtime error '5'. Hitting "End" stops the process and gets rid of it, but I would like to insert some additional code at the start to check if the custom toolbar already exists, and stop the code running if it does. Basically "if (toolbar) exists then exit sub" I have tried a variety of possibilities but no luck so far. It is probably something simple but to date it eludes me. I'm getting a bit rusty at coding! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Does a toolbar already exist?
Thanks, Peter T
That seems to have done the trick Appreciate your input Drew "Peter T" <peter_t@discussions wrote in message ... In the routine that creates your bar start by deleting any similarly named bar that might exist, but under an error handler in case it doesn't exist. Use the same routine to delete the bar when your workbook closes (assuming it's not needed any more) Sub openEventTest() ' call from Workbook_Open myMenu True End Sub Sub closeEventTest() ' call from Workbook_Close myMenu False End Sub Sub myMenu(bCreate As Boolean) Dim cbr As CommandBar Dim cbt As CommandBarButton On Error Resume Next Set cbr = Application.CommandBars("TestBar") If Not cbr Is Nothing Then cbr.Delete End If On Error GoTo 0 If bCreate Then Set cbr = Application.CommandBars.Add("TestBar", temporary:=True) Set cbt = cbr.Controls.Add(msoControlButton) With cbt .Caption = "press to run myMacro" .OnAction = "myMacro" .Style = msoButtonCaption .Visible = True End With End If cbr.Visible = True End Sub Sub myMacro() MsgBox "Hello from myMacro" End Sub Regards, Peter T "Drew Paterson" wrote in message ... I have some code in a workbook which creates a new custom toolbar when the workbook opens and deletes it when the workbook is closed. (Excel 97) The same process is present in earlier versions of the workbook from previous years. Sometimes I need to open one of those earlier versions while the current version is already open. This produces an error message dialog with runtime error '5'. Hitting "End" stops the process and gets rid of it, but I would like to insert some additional code at the start to check if the custom toolbar already exists, and stop the code running if it does. Basically "if (toolbar) exists then exit sub" I have tried a variety of possibilities but no luck so far. It is probably something simple but to date it eludes me. I'm getting a bit rusty at coding! -- Drew Paterson Paraparaumu New Zealand |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
hide custom toolbar from Toolbar list | Excel Programming | |||
Form Toolbar verses Control Toolbar | Excel Discussion (Misc queries) | |||
Add-In Toolbar Control - Removing The Toolbar Question | Excel Programming | |||
Copy Worksheet to a new Workbook creating if it doesn't exist and add more Worksheets if it does exist | Excel Programming | |||
Command Line. How to tell to XL : If the xls file exist : Open it, if it does not exist : Create it. | Excel Programming |