XL2016 Form Controls OptionButtons - moving and/or graying option buttons
Thanks Garry. FWIW, the solution I came up with works with the Form Controls OptionButtons that were in place. I created a table of values for Left and Top properties and treated the controls as shapes. Based on the user selection, I made Visible true or false, and if true, set the positions based on the values in my table. It's not the most elegant code and has room for improvement, but here it is (Column 3 of my table is Enabled: TRUE or FALSE, Column 4 is Top, Column 5 is Left)
Public Sub DisplayProductsPerChannel()
Dim strChannel As String
Dim rngTable As Range
Dim opt As OptionButton
Dim strOptBtn As String
Dim blnEnabled As Boolean
Dim i As Integer
Dim rngChannel As Range
Dim wsSubGroup As Worksheet
' Get selected Channel
strChannel = Range("SelectedChannel").Value
Set rngTable = Range("Tbl_ProductsPerChannel")
' Filter Tbl_ProductsPerChannel by Channel
With rngTable
.AutoFilter
.AutoFilter 1, strChannel
Set rngChannel = .Offset(1).Resize(.Rows.Count - 1). _
SpecialCells(xlCellTypeVisible)
End With
Set wsSubGroup = Worksheets(SUBGRPBASESHEETNAME)
' Loop thru OptionButtons
For i = 1 To wsSubGroup.OptionButtons.Count
Set opt = wsSubGroup.OptionButtons(i)
strOptBtn = opt.Name
blnEnabled = rngChannel.Cells(i, 3).Value
With wsSubGroup.Shapes(strOptBtn)
.Visible = blnEnabled
If blnEnabled = True Then
.Top = rngChannel.Cells(i, 4).Value
.Left = rngChannel.Cells(i, 5).Value
End If
End With
Next i
End Sub
|