Excel

Dynamic Data Validation

Ease of Use

Easy

Version tested with

2000 

Submitted by:

andy_uk

Description:

A very simple demo of Worksheet Change ; when a data validation option is selected from B1, the code blanks B2, selects B2 & shows the data validation options for B2. 

Discussion:

I "devised" this recently, temping in an Adult Education office, on an enquiry/receipt system. Course fees came in 2 flavours, "Full" or "Concessionary". I wanted to be able to choose a course from a data validation list (B1 in the file), & and have this action pop-up the fees for that course (in B2). B2 has a nifty (IMO) data validation formula that works dynamically ; the (horizontal) validation list moves according to the course selected. Of course you could achieve the same thing by referencing a "Full/Conc"cell in an IF ... VLOOKUP formula but the office wasn't THAT busy :) 

Code:

instructions for use

			

Private Sub Worksheet_Change(ByVal Target As Range) 'Don't run the code unless the change occurs in cell B1 If Target.Address <> "$B$1" Then Exit Sub 'Don't run the code if the change (in B1) is clearing contents If Target = "" Then Exit Sub 'Blank B2, select B2, show B2 data validation options Range("B2") = "" Range("B2").Select SendKeys "%{DOWN}" 'NB - XL purists dislike the "SendKeys" method ; the choice is yours :) End Sub

How to use:

  1. To view the code, rightclick the sheet tab & select "View Code".
  2. To close the Visual Basic Editor, use File -- Close or [ALT Q]
 

Test the code:

  1. Select cell B1, then choose an option from the dropdown arrow.
 

Sample File:

Worksheet Change.zip 6.33KB 

Approved by mdmackillop


This entry has been viewed 690 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express