Update a Drop-Down Menu when a cell value changes (Excel)

0
votes

I have a Problem with a Drop-Down menu. The Drop-Down menu (B5) has a range of four values (J1:J4) that are dependent on a value in a cell above the Drop-Down menu (B3). When B3 is below a certain value, then the values in the range turn to FALSE (That part is on purpose and done with an easy if-function) and this is displayed in the Drop-Down menu, meaning you can chose the value FALSE. But when you first choose a value in the Drop-Down menu and later change the value in B3, say, reduce the value so the range-value should turn to FALSE, the value displayed in the Drop-Down menu does not update, while the value in the range changes just fine.

Is there a way to "force"-update the Drop-Down menu, each time the value in B3 changes?

1
excelvba
Try repopulating the validation list on a worksheet_change event, but make sure you specify this event for B3 using Intersect – Tim Stack
@TimStack don't necessarily need to use the Intersect method. An easier option, based on the OPs understanding of VBA, could simply be If Target.Address = $B$3 etc. – Dean
That's correct @Dean – Tim Stack
I tried it on My Worksheet. It Automatically updates the Drop Down List when you change the value in the Reference cell in your case B3. But Yeah It does not change the already selected value from the Drop down. Which can be handled by Worksheet_Change event. – Mikku
@Mikku What command do I have to use on B5? I tried with .run but that doesn't seem to work: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$3" Then Range("B5").Run End If End Sub – Laurin Allemand

1 Answers

0
votes

Just Add an Worksheet Event to capture the Change in Cell B3 and clear it's value. 

Private Sub Worksheet_Change(ByVal Target As Range)

 If Target.Address = "$B$3" Then 
 Range("B5").ClearContents 
 End If 

End Sub