OptionButton numbering loop

1
votes

Hope you have an elegant solution for what is probably a simple problem!

I am using ActiveX option buttons, but within a worksheet and not a userform or a group box because of the way the sheet was designed. The code is contained as a sub within an option button code form.

This code is pretty self-explanatory of what I'm trying to do:

Public Sub SectionD_Click()

If OptionButton1.Value = True Then
    ThisWorkbook.Sheets("Boolean").Range("B2").Value = 1
ElseIf OptionButton2.Value = True Then
    ThisWorkbook.Sheets("Boolean").Range("B2").Value = 0
End If

If OptionButton3.Value = True Then
    ThisWorkbook.Sheets("Boolean").Range("B3").Value = 1
ElseIf OptionButton4.Value = True Then
    ThisWorkbook.Sheets("Boolean").Range("B3").Value = 0
End If

If OptionButton5.Value = True Then
    ThisWorkbook.Sheets("Boolean").Range("B4").Value = 1
ElseIf OptionButton6.Value = True Then
    ThisWorkbook.Sheets("Boolean").Range("B4").Value = 0
End If

End Sub

I would like to make it such that the number following "OptionButton" changes values using a simple 'i = i + 2' type statement but it seems some VBA variable/expression/object limitations will not let me (sorry I'm a noob here, not sure what the proper terminology should be).

Would really appreciate if anyone could point me in the right direction here! I have to look through 25 or so option button pairs, and I would very much like the code to just be 5 simple lines rather than a hundred over lines doing the same thing!

2
vbaexcelradio-buttonactivex

2 Answers

1
votes

I can name that tune with one line of code!!

Public Sub SectionD_Click():    Dim i As Integer:    Dim rw As Long:    rw = 2:    With Worksheets("Sheet1"):    For i = 1 To 10 Step 2:        If .OLEObjects("OptionButton" & i).Object.Value Then:            Worksheets("Boolean").Cells(rw, "B").Value = 0:        ElseIf .OLEObjects("OptionButton" & i).Object.Value Then:            Worksheets("Boolean").Cells(rw, "B").Value = 0:        End If:        rw = rw + 1:    Next:    End With:End Sub:

But I think that 16 lines is prettier.

Public Sub SectionD_Click()
    Dim i As Integer
    Dim rw As Long
    rw = 2

    With Worksheets("Sheet1")
        For i = 1 To 10 Step 2
            If .OLEObjects("OptionButton" & i).Object.Value Then
                Worksheets("Boolean").Cells(rw, "B").Value = 0
            ElseIf .OLEObjects("OptionButton" & i).Object.Value Then
                Worksheets("Boolean").Cells(rw, "B").Value = 0
            End If
            rw = rw + 1
        Next
    End With
End Sub
1
votes

5 lines? Really? :) That's the best I can do:

Option Explicit

Public Sub SectionD_Click()

    With ThisWorkbook.Sheets("Boolean")
        Call CheckValue(.OptionButton1, .OptionButton2, .Range("B2"))
        Call CheckValue(.OptionButton3, .OptionButton4, .Range("B3"))
    End With
End Sub

Sub CheckValue(btn1 As Object, btn2 As Object, my_cell As Range)

    If btn1.Value Then
        my_cell.Value = 1
    ElseIf btn2.Value Then
        my_cell = 0
    End If

End Sub