Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Enable Option Button if File Exists | Excel Discussion (Misc queries) | |||
Checking if Sheet Exists? | Excel Discussion (Misc queries) | |||
check if the sheet/tag exists | Excel Worksheet Functions | |||
Sheet name already exists | Excel Discussion (Misc queries) | |||
How can I know if a sheet exists ? | Excel Programming |