View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.programming
Paul Martin[_2_] Paul Martin[_2_] is offline
external usenet poster
 
Posts: 133
Default 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