Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HELP - Radio buttons and other controls suddenly moving in Excel 2010 only | Excel Programming | |||
Option Button in form controls | Excel Programming | |||
Option Buttons in a Form | Excel Discussion (Misc queries) | |||
more than 2 option buttons on a form | Excel Programming | |||
option buttons in a form | Excel Programming |