Search code examples
excelvbauserform

Userform OptionButtons exclusive in two directions

I have a userform with buttons organized like a table in colums and rows. I want only one button activated for each column and row. For now it works with all buttons being option buttons and all optionbuttons of a row in the same frame. Then for the columns, I started to write for each button a sub of the type :

Private Sub Col1Row3_Click()

For i = 1 To 2
 Me.Controls("col1Row" & i) = False
Next i

For i = 4 To 14
 Me.Controls("col1Row" & i) = False
Next i

End Sub

It works, but I'm fairly confident there is a more elegant way. Any thoughts ? Thanks !

Solution

  • Simple sub procedure

    Tried to demonstrate a simple way to simplify code remaining close to your approach by

    1. using a sub procedure
    2. setting all option buttons to False in a loop and eventually the active one to True again. 
    Sub Only(ByVal rowNum As Long, ByVal colName As String, Optional ByVal maxRow As Long = 14)
' Purpose: set all OptionButtons to False except the one for row rowNum
    Dim currRow     As Long
    For currRow = 1 To maxRow
        Me.Controls(colName & "Row" & currRow) = False
    Next
    Me.Controls(colName & "Row" & rowNum) = True
End Sub

    Example call avoiding complicated loops

    Private Sub col1Row3_Click()
    Only 3, "col1"
End Sub

    Further hint

    Instead of repeating the procedure call for each of your option buttons, it would be possible to profit from class programming (~> WithEvents). For deeper insight you might be interested e.g. in VBA Control arrays if you want to group controls that share the same name type and the same event procedures pre-defined in a class.