0

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

Divin3
  • 538
  • 5
  • 12
  • 27

1 Answers1

4

You have a couple of options.

  1. 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

  2. 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
Automate This
  • 30,726
  • 11
  • 60
  • 82
  • the second one does not work, I get the error: object doesn't support this property of method. I find the second one fancyer :-) – Divin3 Jun 30 '14 at 16:34
  • You actually can't have spaces in the name of a control. Could you verify the name of the opt button? – Automate This Jun 30 '14 at 17:27
  • 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? – Divin3 Jun 30 '14 at 19:49
  • 1
    ok, I found the problem: `myControl.ControlFormat.Value` is the working version :) Thanks you for the help – Divin3 Jul 07 '14 at 07:47