hide activex checkboxes conditionally

[lydiapenrose]

Hello!

I have a bunch of text conditionally formatted to appear when a certain cell (J9) = "Yes". However, I also have three ActiveX checkboxes floating there that I would like to have the same conditional formatting for. The checkboxes are named "GxP", "SOX" and "Privacy".

From my research, it looks like it would require a simple macro that runs every time someone edits the page? Could someone please help me? My skills are quite basic when it comes to macros and I can't figure it out.

I tried the below code, because the checkboxes are the only activex controls that I am using but something seems to be wrong as it's not working...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Range("J9").Value = "Yes" Then
ActiveWorkbook.DisplayDrawingObjects = xlHide
End If
 
End Sub

 

[BiocideJ]

That code looks good although you probably want to add another condition to make them visible again if J9 is NOT Yes.

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("J9").Value = "Yes" Then
    ActiveWorkbook.DisplayDrawingObjects = xlHide
Else
    ActiveWorkbook.DisplayDrawingObjects = xlDisplayShapes
End If
 
End Sub

Also, make sure you have the code in the Visual Basic window for the sheet that is actually changing (e.g. Sheet1, Sheet2, etc) and not in a module (e.g Moduel1, Module2, etc) as it will not fire the Worksheet_Change event.

 

[lydiapenrose]

Yay! It worked!

Thank you so much. I did not have the code associated with the sheet which was why it wasn't responding.

One quick thing now is that I have J9 data validated to a dropdown list of Yes or No. I'm guessing that this functionality would be a drawing object? Because now, the dropdown list functionality is not available when J9 is "No". If I wanted to specifically hide the checkboxes, how would I do that?

Thanks

 

[lydiapenrose]

And I figured it out! I ended up using the below code. Thanks so much

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Range("J9").Value = "Yes" Then
Me.GxP.Visible = True
Me.SOX.Visible = True
Me.Privacy.Visible = True
Else
Me.GxP.Visible = False
Me.SOX.Visible = False
Me.Privacy.Visible = False
End If
End Sub