Clear a cell when select ANY option from drop down?

[FatalLordes]

Morning all (well, it is morning here).

So I'm using O365 and I'm wondering if there is any way to have excel clear a cell (lets say D16) when you make ANY selection from a drop down box located in another cell? And continuing on from that, as well as clearing a cell so that it contains nothing, it is also possible to set another cell (lets say C42), which contains a tick box, to clear the tick in the box if it is there when you make ANY selection from the same drop down box. Great if either one can be done. Fantastic if both can be done.

 

[kevin9999]

Please try the following on a copy of your workbook. Put the code in the worksheet module of the sheet in question. Make the changes as suggested in the code.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge = 1 And Target.Address = "$A$1" Then '<< change A1 to your dropdown cell address
        [D16].ClearContents
        Me.CheckBoxes("Check Box 1").Value = False  '<< change checkbox name to your actual checkbox name
    End If
End Sub

 

[FatalLordes]

Please try the following on a copy of your workbook. Put the code in the worksheet module of the sheet in question. Make the changes as suggested in the code.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge = 1 And Target.Address = "$A$1" Then '<< change A1 to your dropdown cell address
        [D16].ClearContents
        Me.CheckBoxes("Check Box 1").Value = False  '<< change checkbox name to your actual checkbox name
    End If
End Sub

Thanks Kevin. I got the D16 cell part to work but, um, how do I name a checkbox? I've always just picked it from the menu and I didn't think there were any options to name it??

 

[kevin9999]

If you right-click on the check box, its name will appear in the name box (top left of screen above cell A1).

 

[FatalLordes]

Oh yeah, well, I did that (it is called ReminderSent) but when I run I get the following error. What have I missed?

 

[kevin9999]

It works for me using this code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge = 1 And Target.Address = "$A$1" Then '<< change A1 to your dropdown cell address
        [D16].ClearContents
        Me.CheckBoxes("ReminderSent").Value = False  '<< change checkbox name to your actual checkbox name
    End If
End Sub

Link to demo file here