XL2016 Form Controls OptionButtons - moving and/or graying option buttons
 

I have a number of OptionButtons (Form Controls, not ActiveX) on a worksheet. According to user selections, some of these need to be enabled or disabled, or visible or not.

I would like to either:
- 1. gray out the disabled optionbuttons, OR
- 2. make not visible those not required, and then neatly align in 4 columns those that are visible.

I can enable and disable the option buttons, and I can make visible or not. What I can't work out, is how to either gray out the disabled controls, or move the invisible ones. Option 1 looks simpler and will do the job, if it's possible. Option 2 looks better, but I can't work out how to do it, and it seems more work. Any suggestions much appreciated...

XL2016 Form Controls OptionButtons - moving and/or graying option buttons
 

Option controls should be 'grouped' so only 1 can be selected; -clicking on any
button in the group deselects the others. Grouping requires using ActiveX
controls!

Also, best to put controls at the top of the sheet in non-scrolling rows so
they don't shift around (lose position) when hiding/unhiding cols/rows.

Optionally, you could use a modeless userform setup so it's 'context sensitive'
to what the user is doing so it only exposes controls that suit the activity at
hand.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

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

XL2016 Form Controls OptionButtons - moving and/or graying option buttons
 

Congrats! Similar 'work arounds' have been posted over the years.

Personally, I don't care much for using worksheet controls. I prefer to usea
custom toolbar OR 'disguised' cells as controls in rows/cols that allow
hiding/unhiding as needed and/or so they don't shift when expanding/collapsing
outlines. While the code is a good alternative, it does require more work to
make *reliable* use of.

Not sure, though, why toggling the Enabled prop isn't sufficient. Actually
sounds like a userform interface would be a better approach so user options can
be more conveniently made 'context sensitive'. That or a custom Toolbar where
different menuitems display in context by simply toggling their Visible
property!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion