Home/ Questions/Q 8919
Next
In Process
Computing Staff
  • 1
Asked: 2021-11-30T09:49:50+00:00 In:

Excel 2010 | Check Box (Form Control) Appear/Disappear

  • 1

Hi,

Is it possible to have a Check Box (Form Control) appear/disappear in a cell by selecting a value from a dropdown menu? For example: I have my dropdown menu in cell b2. Based on certain dropdown values, I would like different Check Boxes to appear in B4-B6.

Meaning, if B2=”Apple”, I need B4 to have a check box to enable “Granny Smith”, B5 to have a check box to enable “Pink Lady”, and B6 to have a check box to enable “Fuji”. But if B2=”Grape”, I need B4 to have a check box to enable “Green” and B5 to have a check box to enable “Purple”.

I am open to using other types of form controls or coding to make this happen–it’s just that my knowledge in that area is slim.

Appreciate the assistance!

Share

    1 Answer

    1. I don’t do much with Form Controls, but perhaps you can use the Worksheet_Change event to accomplish your goal.

      The Worksheet_Change event fires whenever a change is made to the worksheet in which it resides.

      A Worksheet_Change event looks something like this:

      Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Address = "$B$2" Then
   MsgBox "Cell B2 Was Changed!"
 End If
End Sub

      Obviously you would change the MsgBox instruction to show your Form Control.

      To check for different values in B2, you would use more If’s:

      Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Address = "$B$2" Then
 
   If Target = "Apple" Then
    MsgBox "B2 Now Shows Apple"
   End If
   
    If Target = "Grape" Then
    MsgBox "B2 Now Shows Grape"
   End If
   
 End If
End Sub

      I hope that helps.

      Click Here Before Posting Data or VBA Code —> How To Post Data or Code.

      • 0