Show hide ActiveX optionbuttons based on cells with formulas

[vipett]

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:

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..

 

[vw412]

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.