Greetings !

I have some routines in the "WorkbookOpen" routine
of various workbooks that create ToolBar buttons.

Generally, I have managed to get the relevant buttons
deleted when the workbook is closed, but what I haven't
yet
managed is to prevent new duplicate buttons being created.

Would SKS be so kind as to give me the relevant code ?

e.g.

Public Sub MakeButtons()

' Make "Print" button

If Exists Button(Print) Then ' <--- It's this line I
need help with !

Else
'Existing Make "Print" button routine goes here

End If


' Repeat for the other buttons


End Sub


RClay AT haswell DOT com

Robin

I have a similar problem and appear (only just tried this!) to have a
solution by adding the following line to my Workbook_Open routine:

Application.CommandBars("Worksheet Menu
Bar").Controls("Navigation").Delete

When there is no "Navigation" control it seems to simply do nothing. I
realise this doesn't seem to be a programmatically sound approach - perhaps
others can comment.

Let me know if this works for you too.

Peter Morris
"Robin Clay" wrote in message
...
Greetings !

I have some routines in the "WorkbookOpen" routine
of various workbooks that create ToolBar buttons.

Generally, I have managed to get the relevant buttons
deleted when the workbook is closed, but what I haven't
yet
managed is to prevent new duplicate buttons being created.

Would SKS be so kind as to give me the relevant code ?

e.g.

Public Sub MakeButtons()

' Make "Print" button

If Exists Button(Print) Then ' <--- It's this line I
need help with !

Else
'Existing Make "Print" button routine goes here

End If


' Repeat for the other buttons


End Sub


RClay AT haswell DOT com

Peter,

Deleting the toolbar this way is a sound approach. I wouldn't rely on
nothing happening though, it might be okay today but not tomorrow. I would
put error handling around it (see my response to OP) so that if it did fail,
the code will not bomb.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Peter M" wrote in message
...
Robin

I have a similar problem and appear (only just tried this!) to have a
solution by adding the following line to my Workbook_Open routine:

Application.CommandBars("Worksheet Menu
Bar").Controls("Navigation").Delete

When there is no "Navigation" control it seems to simply do nothing. I
realise this doesn't seem to be a programmatically sound approach -
perhaps
others can comment.

Let me know if this works for you too.

Peter Morris
"Robin Clay" wrote in message
...
Greetings !

I have some routines in the "WorkbookOpen" routine
of various workbooks that create ToolBar buttons.

Generally, I have managed to get the relevant buttons
deleted when the workbook is closed, but what I haven't
yet
managed is to prevent new duplicate buttons being created.

Would SKS be so kind as to give me the relevant code ?

e.g.

Public Sub MakeButtons()

' Make "Print" button

If Exists Button(Print) Then ' <--- It's this line I
need help with !

Else
'Existing Make "Print" button routine goes here

End If


' Repeat for the other buttons


End Sub


RClay AT haswell DOT com

Robin,

You could put error handling around it

On Error Resuume Next
'your code to create button, this will fail but the resume next
'will stop it bombing out
'On Error Goto 0


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Robin Clay" wrote in message
...
Greetings !

I have some routines in the "WorkbookOpen" routine
of various workbooks that create ToolBar buttons.

Generally, I have managed to get the relevant buttons
deleted when the workbook is closed, but what I haven't
yet
managed is to prevent new duplicate buttons being created.

Would SKS be so kind as to give me the relevant code ?

e.g.

Public Sub MakeButtons()

' Make "Print" button

If Exists Button(Print) Then ' <--- It's this line I
need help with !

Else
'Existing Make "Print" button routine goes here

End If


' Repeat for the other buttons


End Sub


RClay AT haswell DOT com

Bob,

I think I should run my solutions passed you first!

A little more testing and my solution bombed out too! Inclusion of error
handling is a good idea!

Peter

"Bob Phillips" wrote in message
...
Robin,

You could put error handling around it

On Error Resuume Next
'your code to create button, this will fail but the resume next
'will stop it bombing out
'On Error Goto 0


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Robin Clay" wrote in message
...
Greetings !

I have some routines in the "WorkbookOpen" routine
of various workbooks that create ToolBar buttons.

Generally, I have managed to get the relevant buttons
deleted when the workbook is closed, but what I haven't
yet
managed is to prevent new duplicate buttons being created.

Would SKS be so kind as to give me the relevant code ?

e.g.

Public Sub MakeButtons()

' Make "Print" button

If Exists Button(Print) Then ' <--- It's this line I
need help with !

Else
'Existing Make "Print" button routine goes here

End If


' Repeat for the other buttons


End Sub


RClay AT haswell DOT com

Hello, Bob !

I had written:

Public Sub MakeButtons()
' Make "Print" button
If Exists Button(Print) Then
' <--- It's this line I need help with !
Else
'Existing Make "Print" button routine goes here
End If
' Repeat for the other buttons
End Sub


You kindly wrote,
You could put error handling around it

On Error Resuume Next
'your code to create button,
this will fail but the resume next
'will stop it bombing out
'On Error Goto 0

Erm... I don't get an error !

What I (sometimes) get is about six duplicate buttons !

However... maybe your reply to Peter M's kind response
(for which, many thanks) answers the case, i.e. delete the
button before creating it, and error-trap for the (usual)
case that the button does not already exist.

Thank you both !

But one more thing, if I may -
what does "On Error GoTo 0" do,
as opposed to "On Error Resume Next" ?


RClay AT haswell DOT com

About On Error GoTo 0.
From the Help:
On Error GoTo 0 disables error handling in the current procedure. It doesn't
specify line 0 as the start of the error-handling code, even if the
procedure contains a line numbered 0.


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Robin Clay" wrote in message
...
Hello, Bob !

I had written:

Public Sub MakeButtons()
' Make "Print" button
If Exists Button(Print) Then
' <--- It's this line I need help with !
Else
'Existing Make "Print" button routine goes here
End If
' Repeat for the other buttons
End Sub


You kindly wrote,
You could put error handling around it

On Error Resuume Next
'your code to create button,
this will fail but the resume next
'will stop it bombing out
'On Error Goto 0

Erm... I don't get an error !

What I (sometimes) get is about six duplicate buttons !

However... maybe your reply to Peter M's kind response
(for which, many thanks) answers the case, i.e. delete the
button before creating it, and error-trap for the (usual)
case that the button does not already exist.

Thank you both !

But one more thing, if I may -
what does "On Error GoTo 0" do,
as opposed to "On Error Resume Next" ?


RClay AT haswell DOT com