vbaexcelloopsfor-loop

How can I set OptionButton font using VBA For Loop?

I am trying to manually set every OptionButton's font on a sheet to be a uniform size and type using a For Loop. I can do them manually by writing out each specific button's information but I have hundreds of buttons. I can even get VBA to write the correct syntax to a test Worksheet by using this code here:

`Private Sub Thisworkbook_Open()
    For i = 1 to Worksheets("Core").OLEObjects.Count
        If TypeName(Worksheets("Core").OLEObjects(i).Object) = "OptionButton" Then
        Worksheets("testsheet").Range("A" & i).Value =     Worksheets("Core").OLEObjects(i).Name
        End If
    Next i
End Sub`

But what I can't do is put the rest of this below code along with the above code to have ONE clean and concise statement that will manually set all OptionButton values to these settings:

    `With Worksheets("Core").OptionButton1
   .Font.Size = 11
   .Font.Name = "Calibri"
   .Font.Bold = False
End With`

Can someone explain to me how I can make this work?

Solution

  • Actually you have your answer in your question, all you have to do is to put your properties to correct location, as follows:

        For i = 1 To Worksheets("Core").OLEObjects.Count
        If TypeName(Worksheets("Core").OLEObjects(i).Object) = "OptionButton" Then

            Worksheets("Core").OLEObjects(i).Object.FontSize = 5
            ' Remaining code goes here.
        End If
    Next i