VBA If button exists

data808

Active Member
Joined
Dec 3, 2010
Messages
353
Office Version
  1. 2019
Platform
  1. Windows
How would you write an "IF" statement based on if a macro button exists on the spreadsheet? I'm planning to delete the button once the user clicks on it but I need to take care of the scenario if the user tries to type data into cells before clicking on that macro button.

So basically just need help on if the button exists. Would it be something like this:

If ActiveSheet.Buttons("Auto Save") <> 0 Then
Range("A5:A10").Value = ""
MsgBox "Click Auto Save Button." & vbNewLine & _
vbNewLine & "Then You May Start Entering Data."
Application.EnableEvents = True
Exit Sub
End If
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
You mean if any button exists on the sheet, or one that goes by a particular name?
If the latter, I think you'd have to loop over the shapes (not buttons or controls) on the sheet and test the name property. If the former, then it would just be the count.
 
Upvote 0
Maybe
VBA Code:
Dim shp As Shape

For Each shp In ActiveSheet.Shapes
   If shp.Name = "Auto Save" Then
      Range("A5:A10") = ""
      MsgBox "Click Auto Save Button." & vbNewLine & _
      vbNewLine & "Then You May Start Entering Data."
      Application.EnableEvents = True
      Exit Sub
   End If
Next
Better to use actual sheet name if possible, rather than ActiveSheet.
 
Upvote 0
You mean if any button exists on the sheet, or one that goes by a particular name?
If the latter, I think you'd have to loop over the shapes (not buttons or controls) on the sheet and test the name property. If the former, then it would just be the count.
Not sure how to do either. I think the best would be if a particular button exists. The name of the button is called "Auto Save". How would you write the code to check for that button?
 
Upvote 0
Maybe
VBA Code:
Dim shp As Shape

For Each shp In ActiveSheet.Shapes
   If shp.Name = "Auto Save" Then
      Range("A5:A10") = ""
      MsgBox "Click Auto Save Button." & vbNewLine & _
      vbNewLine & "Then You May Start Entering Data."
      Application.EnableEvents = True
      Exit Sub
   End If
Next
Better to use actual sheet name if possible, rather than ActiveSheet.
Ok thanks. I'll test it out.
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

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