excel vba check if command button and checkbox exists

Question

I have a script that uses checkboxes and command buttons, and there is a macro that resets them to default on every sheet.

'opt button reset
    For i = 1 To Worksheets.Count
Sheets(i).Shapes("Option Button 1").ControlFormat.Value = xlOn
    Next i

'cb reset
For i = 1 To Worksheets.Count
    Sheets(i).Shapes("CheckBox1").ControlFormat.Value = xlOff
Next i

The problem is that, if there is a sheet that has no checkbox or opt button, the script does not work

What I want to do is to check if the cb and ob exists and execute the script only than.

Thanks in advance

Automate This · Accepted Answer · 2014-06-29 01:10:21Z

4

You have a couple of options.

Quickest way: On Error Resume Next

On Error Resume Next
For i = 1 To Worksheets.Count
  Sheets(i).Shapes("Option Button 1").ControlFormat.Value = xlOn   'opt button reset
  Sheets(i).Shapes("CheckBox1").ControlFormat.Value = xlOff   'cb reset
Next i

Loop through objects and match name

For i = 1 To Worksheets.Count
  For Each myControl In Sheets(i).Shapes
    If myControl.Name = "CheckBox1" Then
      myControl.Value = xlOff
    ElseIf myControl.Name = "Option Button 1" Then
      myControl.Value = xlOn
    End If
  Next myControl
Next i

answered Jun 29, 2014 at 1:10

Automate This

31.5k11 gold badges63 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Lrnd Over a year ago

the second one does not work, I get the error: object doesn't support this property of method. I find the second one fancyer :-)

Automate This Over a year ago

You actually can't have spaces in the name of a control. Could you verify the name of the opt button?

Lrnd Over a year ago

It was with spaces, and it was working with the first solution. Now I did it without spaces, and the same error appears. Is it possible that it is because I use the Workbook_BeforeSave function?

Lrnd Over a year ago

ok, I found the problem: myControl.ControlFormat.Value is the working version :) Thanks you for the help

Collectives™ on Stack Overflow

excel vba check if command button and checkbox exists

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related