|
|
|
|
|
|
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)
If Target.Address <> "$B$1" Then Exit Sub
If Target = "" Then Exit Sub
Range("B2") = ""
Range("B2").Select
SendKeys "%{DOWN}"
End Sub
|
How to use:
|
- To view the code, rightclick the sheet tab & select "View Code".
- To close the Visual Basic Editor, use File -- Close or [ALT Q]
|
Test the code:
|
- 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.
|
|