Show hide ActiveX optionbuttons based on cells with formulas

vipett

New Member
Joined
May 6, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
I have an Excelsheet with mulitple ActiveX Optionbuttons in certain categories (Product, color, power).
If I choose for example White in Color, I want a certain power option to be hidden.

I have linked my color optionbuttons to different cells, which becomes true/false depending on the choice and next to these, the color is written in a cell.
I then have a summary area of the page which shows what options have been selected.

In cell L17, the color name is written and if that cell contains "White", I want optionbutton8 to be hidden.

I've tried this code:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'---Shows button if Cell value = "Yes" if not hide button
    Dim sAddress As String
    sAddress = "$L$17"
       With ActiveSheet
        If Intersect(Target, Range(sAddress)) _
            Is Nothing Then Exit Sub
        On Error GoTo CleanUp
        Application.EnableEvents = False
        Me.OptionButton8.Visible = _
            (Range(sAddress).Value = "White")
    End With
CleanUp:
    Application.EnableEvents = True
End Sub

If I manually enter White in L17 it works fine, but not when L17 is based on a Vlookup.

I realize this might not be the way to do it...


In the future, I might have up to 30 different products with different colors and powers, Is there a smarter way to do this, can I have a sheet with possible combinations and link the visibility to this sheet?
I know this is very easy using drop down menus but I want to use option buttons as I want pictures to click on and not only text..
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

vw412

Active Member
Joined
Dec 16, 2011
Messages
343
Office Version
  1. 2019
  2. 2016
  3. 2010
  4. 2007
Platform
  1. Windows
You have discovered that the worksheet_change event does not fire when a cell is changed by formula, only when changed manually or by macro. You will have to find a different way to trigger the hiding of your optionbuttons. I suggest you examine the user interface interaction for another possiblity. Maybe whatever optionbutton you select that would put White in the cell next to it could be used? I will need more detail and discussion with you to figure out how to proceed.
 

Forum statistics

Threads
1,141,072
Messages
5,704,133
Members
421,329
Latest member
mippy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top