How to find out if a button exists on a sheet
 

Hi all,
I use the following code to create a button and procedure at runtime:
With ActiveSheet
Set btn = .OLEObjects.Add(classtype:="Forms.CommandButton.1" ,
_
Left:=.Range("B3").Left, Top:=.Range("B5").Top, _
Width:=100, Height:=20)
End With
btn.Object.Caption = "Back to Forms"
btn.Name = "Button"
With Workbooks("ResultLib.xls").VBProject.VBComponents( ActiveSheet.CodeName).CodeModule
.InsertLines .CreateEventProc("Click", btn.Name) + 1, _
" Application.run ""Frontend1.xls!initialize"" "
End With

This works fine, however this code is called from the click of a
button and on the second call it creates the same procedure again
creating an ambigious name.
I have tried to use an error handler as follows

on error goto notfound
ActiveSheet.Shapes("Button").select
exit sub
notfound:
With ActiveSheet
Set btn = .OLEObjects.Add(classtype:="Forms.CommandButton.1" ,
_
Left:=.Range("B3").Left, Top:=.Range("B5").Top, _
Width:=100, Height:=20)
End With
btn.Object.Caption = "Back to Forms"
btn.Name = "Button"
With Workbooks("ResultLib.xls").VBProject.VBComponents( ActiveSheet.CodeName).CodeModule
.InsertLines .CreateEventProc("Click", btn.Name) + 1, _
" Application.run ""Frontend1.xls!initialize"" "
End With

However the error doesn't seem to be getting thrown and the code just
steps through ignoring the select statement.
Can anybody please shed some light on this.


Regards
Nigel

How to find out if a button exists on a sheet
 

Nigel,

Your code worked fine for me. Perhaps you could change that code so
that when the button is created your also write a flag value to a
customdocument property or to the registry, and use a reading of that
value to exit or continue your routine.

HTH,
Bernie
MS Excel MVP

"Nigel Brown" wrote in message
om...
Hi all,
I use the following code to create a button and procedure at
runtime:
With ActiveSheet
Set btn =
..OLEObjects.Add(classtype:="Forms.CommandButton.1 ",
_
Left:=.Range("B3").Left, Top:=.Range("B5").Top, _
Width:=100, Height:=20)
End With
btn.Object.Caption = "Back to Forms"
btn.Name = "Button"
With
Workbooks("ResultLib.xls").VBProject.VBComponents( ActiveSheet.CodeName
).CodeModule
.InsertLines .CreateEventProc("Click", btn.Name) + 1, _
" Application.run ""Frontend1.xls!initialize"" "
End With

This works fine, however this code is called from the click of a
button and on the second call it creates the same procedure again
creating an ambigious name.
I have tried to use an error handler as follows

on error goto notfound
ActiveSheet.Shapes("Button").select
exit sub
notfound:
With ActiveSheet
Set btn =
..OLEObjects.Add(classtype:="Forms.CommandButton.1 ",
_
Left:=.Range("B3").Left, Top:=.Range("B5").Top, _
Width:=100, Height:=20)
End With
btn.Object.Caption = "Back to Forms"
btn.Name = "Button"
With
Workbooks("ResultLib.xls").VBProject.VBComponents( ActiveSheet.CodeName
).CodeModule
.InsertLines .CreateEventProc("Click", btn.Name) + 1, _
" Application.run ""Frontend1.xls!initialize"" "
End With

However the error doesn't seem to be getting thrown and the code
just
steps through ignoring the select statement.
Can anybody please shed some light on this.


Regards
Nigel