VBA Drop down selection clears contents in another cell

[drefiek2]

Hi,
I have a cell (J6) with a drop down list (just two options "Day" and "Night"). If "Night" is chosen in the drop down, then I want cells S4:S5 to clear contents. I did put this code into Sheet1 VBA but nothing happens when I choose Night from the drop down. It must operate in the background without a macro button or anything like that. Any help would be appreciated.

Sub ClearContentsDropDown()
If ThisWorkbook.Worksheets("SHEET1").Range("J6") = "Night" Then
ActiveSheet.Range("S4:S5").ClearContents
End If
End Sub

 

[Micron]

I'm guessing that code doesn't even run because it's not contained within any event handler for the drop down. It looks like an ordinary procedure (sub). You should use event subs if you want things to happen when an event occurs. What that event is depends on the control type. If it's an ActiveX control, the event could be the Change event (there are 2 other possibilities). If it's a Form control then the event would be Change event, but I believe that would be your only choice. So move your code into an event handler.
In design mode, right click on the control and choose "Assign macro" (form control) or "View code" (ActiveX control).

 

[drefiek2]

I'm guessing that code doesn't even run because it's not contained within any event handler for the drop down. It looks like an ordinary procedure (sub). You should use event subs if you want things to happen when an event occurs. What that event is depends on the control type. If it's an ActiveX control, the event could be the Change event (there are 2 other possibilities). If it's a Form control then the event would be Change event, but I believe that would be your only choice. So move your code into an event handler.
In design mode, right click on the control and choose "Assign macro" (form control) or "View code" (ActiveX control).

Hi, I don't want to assign this code to any macro, I want it to be an automatic change of a cell based on the value of another cell.
Very basic VBA knowledge here, I did try the macro recorder but that doesn't do anything.
I read somewhere about doing a selection change event but I've no idea what that is.

 

[Engberg]

Is this the behavior you are looking for? "When there is a change on the worksheet, if the change occurs in J6 AND the value of J6 is Night, then clear S4:S5". It "runs in the background" and triggers when there is a change on the worksheet.

You have to place it in the worksheet-specific object.

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Not Intersect(Target, Range("J6:J6")) Is Nothing And Range("J6:J6").Value = "Night" Then
        Range("S4:S5").ClearContents
    End If
    Application.EnableEvents = True
End Sub

 

[drefiek2]

Is this the behavior you are looking for? "When there is a change on the worksheet, if the change occurs in J6 AND the value of J6 is Night, then clear S4:S5". It "runs in the background" and triggers when there is a change on the worksheet.

You have to place it in the worksheet-specific object.

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Not Intersect(Target, Range("J6:J6")) Is Nothing And Range("J6:J6").Value = "Night" Then
        Range("S4:S5").ClearContents
    End If
    Application.EnableEvents = True
End Sub

Thanks, it worked perfectly!

 

[Micron]

Hi, I don't want to assign this code to any macro,

Yet in the end, that is the solution you accepted, and it is an event procedure like I said it had to be (Worksheet_Change).