Looping through multiple Optionbuttons

Question

I have 170 optionbuttons on a worksheet.  When the user opens the workbook, I want a macro to execute and loop through all option buttons and set the value to FALSE, thereby deselecting any that my have been selected so the user will have a black slate, so to speak.

This is the code that I currently have but it has an error:

Private Sub Workbook_Open()

   Dim k As Integer

   Sheets("Template").Activate

   For k = 1 To 170

      ActiveSheet.OLEObjects("Optionbutton" & k).Value = False

   Next k

End Sub

Thanks for your help,

Les

Answer 1

Option Explicit

Private Sub Workbook_Open()

   Dim k As Long

   'WorkSheets("Template").Activate

   For k = 1 To 170

      Worksheets("template").OLEObjects("Optionbutton" & k).Object.Value = False

   Next k

End Sub

There's no reason to activate the Template worksheet (for this anyway).

WLMPilot wrote:

I have 170 optionbuttons on a worksheet.  When the user opens the workbook, I want a macro to execute and loop through all option buttons and set the value to FALSE, thereby deselecting any that my have been selected so the user will have a black slate, so to speak.

This is the code that I currently have but it has an error:

Private Sub Workbook_Open()

   Dim k As Integer

   Sheets("Template").Activate

   For k = 1 To 170

      ActiveSheet.OLEObjects("Optionbutton" & k).Value = False

   Next k

End Sub

Thanks for your help,

Les

--

Dave Peterson

Answer 2

Hi,

How about this

Dim K As Long

Sheets("Template").Activate

For K = 1 To ActiveSheet.OLEObjects.Count

   If TypeName(ActiveSheet.OLEObjects(K).Object) = "OptionButton" Then

    ActiveSheet.OLEObjects(K).Object = False

  End If

Next K

---

If this post answers your question, please mark it as the Answer.

Mike H