We help IT Professionals succeed at work.

Excel: Auto Update Cell Listbox Values

dabug80
dabug80 asked
on
1,792 Views
Last Modified: 2014-05-07
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?
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2011
Awarded 2010

Commented:
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

Author

Commented:
Hi teylyn,

Here's a better sample example.
ValidationExample.xlsx
CERTIFIED EXPERT
Most Valuable Expert 2011
Awarded 2010

Commented:
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

Author

Commented:
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
CERTIFIED EXPERT
Most Valuable Expert 2011
Awarded 2010

Commented:
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

Author

Commented:
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.
CERTIFIED EXPERT
Most Valuable Expert 2011
Awarded 2010
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Great. Thanks for your help.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.