If form button exists then

cmefly

Well-known Member
Joined
May 13, 2003
Messages
683
hi,

is there a vba code to determine if a form button exists on a certain worksheet?

Regards,

Marc
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
You can check through shapes. Note, this would go in the particular sheet's module, as I used Me.

Rich (BB code):
Sub exampleShapes()
Dim shp As Shape
    
    For Each shp In Me.Shapes
        MsgBox TypeName(shp.OLEFormat.Object) & vbTab & shp.OLEFormat.Object.Name
    Next
End Sub
 

Jaye7

Well-known Member
Joined
Jul 7, 2010
Messages
1,060
GTO's script is best, however you could use the following if you want to do a process if it exists.

Code:
Sub Test()
On Error GoTo err2
ActiveSheet.Shapes("Button 1").Select
MsgBox "Button Exists"
Exit Sub
 
err2:
 MsgBox "Button Doesn't Exist"
End Sub
 

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154

ADVERTISEMENT

GTO's script is best, however you could use the following if you want to do a process if it exists.

Hi both,

Actually I like Jay's better as to not looping through all the shapes :)

I would mention that selecting the shape is not necessary. We could just attempt to set a reference to the shape. That way, if we find it, we can do "stuff" with it.

Rich (BB code):
Option Explicit
    
Sub example()
Dim myButton As Button
Dim wks As Worksheet
    
    For Each wks In ThisWorkbook.Worksheets
        Set myButton = Nothing
        On Error Resume Next
        Set myButton = wks.Buttons("Button 1")
        On Error GoTo 0
        
        If Not myButton Is Nothing Then
            myButton.Caption = "Here I am!"
            myButton.OnAction = "TestSub"
            Exit For
        End If
    Next
End Sub
    
Sub TestSub()
    MsgBox "Hello"
End Sub

Hope that helps,

Mark
 

Jaye7

Well-known Member
Joined
Jul 7, 2010
Messages
1,060
GTO, both my script and your latest script will not work in 2010, tested it on my work operating system which says that it is 2002 and they work fine.
 

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154

ADVERTISEMENT

GTO, both my script and your latest script will not work in 2010, tested it on my work operating system which says that it is 2002 and they work fine.

Hi Jay,

Just so I understand, you are saying that the scripts don't work in Excel 2010. When you say "my work operating system", are you saying that you have Excel 2002 or a different OS?

I don't have Excel 2010 available until tomorrow to test (I'm at home, where I still have rusty/dusty Excel 2000).

Presuming you stuck the code in a Standard Module and saved the file as .xls (or .xlsm), does it error any place or not recognize Button as a valid type?

Mark
 

Jaye7

Well-known Member
Joined
Jul 7, 2010
Messages
1,060
I have 2 operating systems on one PC as my work runs it's stand alone software system with windows which is 2002 and my normal operating system is 2010

In 2010 for your script it bugs out on the following if the button doesn't exist.

Code:
Set myButton = wks.Buttons("Button 1")

Error - method of object buttons of worksheet failed.
 

GTO

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
Thanks for checking Jay. I am presuming that you included the Resume Next and ...GoTo 0 lines, as you had an error handler in yours as well, and say that this fouls as well. I think it may be your settings in Excel 2010.

In VBIDE, go to the menubar, Tools|Options, select the General tab. In the Error Trapping section, see if 'Break on All Errors' is ticked. If yes, change to 'Break on Unhandled Errors'.

Mark
 
Last edited:

Jaye7

Well-known Member
Joined
Jul 7, 2010
Messages
1,060
Hi GTO,

I set the error handler to break on unhandled errors previously, for some reason it had changed back to break on all errors.

It now works fine.

Thanks for that.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,608
Messages
5,597,143
Members
414,129
Latest member
mr vba

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top