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

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

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!

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