Avatar of dabug80
dabug80
 asked on

Excel: Auto Update Cell Listbox Values

Hello,

I have a cell list box, linked to a range. Within this range are if statements that update the values of the list.

However once a user selects a cell list box value, this value is set - it doesn't change until the user proactively changes it - even if the value no longer exists within the range.

Is it possible to have each cell list box value update reactively?
Microsoft Excel

Avatar of undefined
Last Comment
dabug80

8/22/2022 - Mon
Ingeborg Hawighorst (Microsoft MVP / EE MVE)

Hello,

are you talking about a form list box or is this really a data validation drop-down?

The values that are computed in the cell range will be offered in the list box or DV drop down with the cell contents that is current at the time and the value will be text, not a formula.

One possibility to achieve what you describe would be a VBA macro that fires when the sheet is recalculated. The macro would evaluate all cells that use the list box / drop-down and compare the text in the cell with the current cell range.

Then you would need to specify what you want to do when a value in a cell is no longer in the source range for the drop down. Delete the invalid value?

You need to provide a bit more background and a few scenarios of the desired behaviour.

cheers, teylyn
dabug80

ASKER
Hi teylyn,

Here's a better sample example.
ValidationExample.xlsx
Ingeborg Hawighorst (Microsoft MVP / EE MVE)

As I understand from the explanations contained in your spreadsheet, you would like the data validation drop-downs in column G to display "Yes" and "No" only if the value in the same row in column D is Yes. If that value is No, then you want the drop-down to show only "No".

Is this correct?

This is quite a bit different from what you asked about in your question. Can you clarify?

The scenario described in your workbook can be achieved in the following way:

- Enter Yes and No into two cells on the sheet, for example into cells I1 and I2
- select a cell in row 7. This is important. You MUST select a cell in row 7 for the rest to work
- create a range name (or named formula) called AllowDiscount with the formula

=IF(Sheet1!$D7="yes",Sheet1!$I$1:$I$2,Sheet1!$I$2)

See how the cell D7 is addressed with an absolute column and relative row reference? This means that when the range name is applied, it will look at column D in the current row.

- Add data validation with the List option to the cells in column G and use the reference =AllowDiscount

The data validation drop-down will now only show the "yes" and the "no" option if the value in column D in the same row is Yes. If not, it will only show the "no" option.

See attached file.

If a value has been selected in column G and then the value in column D changes, you will still need either a manual interaction or a macro to change column G to "no" when column D changes to "no". Let me know if you want to pursue that.

cheers, teylyn
ValidationExample.xlsx
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
dabug80

ASKER
Thanks teylyn

If a value has been selected in column G and then the value in column D changes, you will still need either a manual interaction or a macro to change column G to "no" when column D changes to "no". Let me know if you want to pursue that.

Yes, it would be great if you could help that that macro.

Cheers
Ingeborg Hawighorst (Microsoft MVP / EE MVE)

OK. Right-click the sheet tab, select "View Code" and paste the following code into the code window:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Range

If Not Intersect(Target, Range("D:D")) Is Nothing Then
    For Each cel In Target
      If UCase(cel.Value) = "NO" Then cel.Offset(0, 3) = "No"
    Next cel
End If
End Sub

Open in new window


If the value "No" is entered or pasted into one or more cells in column D, the cell in column G in the same row will be set to "No".

You will need to save the file as a macro-enabled file with the .xlsm extension.

See attached file.

cheers, teylyn
ValidationExample.xlsm
dabug80

ASKER
Thanks teylyn,

I'm now looking to apply this to my full spreadsheet. In my full spreadsheet the range "D:D" in your above code is actually "L:L" (or more specifically "L43:L45". I have tried editing your code above with both the new L ranges, however I can't get it to auto update.

Is there another aspect of your code/implementation that I am missing?

Cheers.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Ingeborg Hawighorst (Microsoft MVP / EE MVE)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
dabug80

ASKER
Great. Thanks for your help.