How to find out if a button exists on a sheet

  • Thread starter Thread starter Nigel Brown
  • Start date Start date
N

Nigel Brown

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
 
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
 
Back
Top