Trouble Looping Through Option Buttons in Worksheets

[Cheech22]

Hi All, new to vba coding and have been wrestling with an issue.

I have a large workbook with multiple worksheets. Each spreadsheet has information in it contained in option buttons (I think they are the Form Control type and not the ActiveX type). I am attempting to write code that extracts information from the optionbuttons.

In most of the worksheets, the option buttons are named consistently, and my code extracts the info I need when I reference the buttons by name. However, there are some sheets where the buttons randomly have different names, so I will need a macro to rename them (I think) so I can reference them properly.

Unfortunately, I haven't even gotten that far. Excel does not recognize any of the option buttons as being part of the Optionbuttons collection. Here is an example:

Sub OptionGet()


'set up worksheet and variables


Dim resultsheet As Worksheet, results As String, x As Worksheet, lastrow As Long, ResultsRange As Range, i As Integer, y As Long, myopt As optionbutton




results = "Results"


ActiveWorkbook.Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = results


Set resultsheet = Sheets(results)




lastrow = 1


'loop through worksheets


For Each x In ActiveWorkbook.Worksheets
    
    If resultsheet.Cells(1, 2).Value = 0 Then
    Set ResultsRange = resultsheet.Cells(1, 2)
    
    Else: Set ResultsRange = resultsheet.Cells(lastrow, 2)
    
    End If
    
'find option buttons
    With x
'test that there is an option button in "x"
    MsgBox (TypeName(.OptionButtons("Option Button 17")))
    
   For Each myopt In .OptionButtons
   


      MsgBox (myopt.Name & " " & TypeName(myopt) & " " & myopt.Type)
   Next myopt
   
     End With
   Next x
 End Sub

There is more to this code, but I shortened it to just the part I'm struggling with.

The line with "option button 17" is in there to prove that there is indeed an optionbutton on x. The msgbox displays "OptionButton" at that line, but nothing happens during the loop.

If I make myopt a shape and loop though .shapes instead of .optionbuttons, it finds all the group boxes, text boxes and other things on the sheet, but no option buttons.

Any help you can provide is appreciated. Thanks.

 

[Domenic]

It looks like Type is not a property of the OptionButton object. So you should be getting an error at that line. Maybe your actual code contains error handling. Try removing myopt.Type from that line.

 

[Cheech22]

It looks like Type is not a property of the OptionButton object. So you should be getting an error at that line. Maybe your actual code contains error handling. Try removing myopt.Type from that line.

Thanks for looking into this, Domenic.

It does appear to be the case that Type is not a property of the OptionButton object, however, that part from the message box code unfortunately did not fix the issue.

 

[Domenic]

Have you enabled error handling in your code? If so, remove it, and run the code again, and see if you get an error message of some sort.

 

[Cheech22]

Have you enabled error handling in your code? If so, remove it, and run the code again, and see if you get an error message of some sort.

I do not have any error handling in this code - I've gotten plenty of errors for other things in the process of getting the code to where it is now.

Thanks again for your time,

 

[RoryA]

Do you have a variable or procedure called optionbutton somewhere? The capitalisation of your variable type suggests so.

 

[Cheech22]

Do you have a variable or procedure called optionbutton somewhere? The capitalisation of your variable type suggests so.

Rory,

I think you are onto something here. I assumed that was the way the compiler formatted it automatically since I couldn't get it to read OptionButton, but it does read OptionButton when I close the workbook and start with a blank module.

Thanks for the response - I will investigate.

 

[Cheech22]

Rory,

I think you are onto something here. I assumed that was the way the compiler formatted it automatically since I couldn't get it to read OptionButton, but it does read OptionButton when I close the workbook and start with a blank module.

Thanks for the response - I will investigate.

I copied my entire code into a new module in a different workbook and I am now able to Dim myopt as an OptionButton. However, the loop is now finding the first OptionButton (one that is different than the #17 I used to test the code) and then moving on to the rest of the code. When I introduce the line MsgBox (.OptionButtons.Count), it returns a 1. There should be around 20.

Thanks again,

 

[RoryA]

Are you sure the others aren't ActiveX, or that you aren't looking at the wrong sheet? (note: just because it's called Option Button 17 doesn't mean that there are necessarily 16 others on there).

 

[Cheech22]

Are you sure the others aren't ActiveX, or that you aren't looking at the wrong sheet? (note: just because it's called Option Button 17 doesn't mean that there are necessarily 16 others on there).

Thanks again for replying, Rory.

I was actually able to discover the issue - most of the optionbuttons were grouped, and for whatever reason optionbuttons that appear in a group do not appear in the OptionButtons collection.

Thanks again, everyone.