Excel

Two Validation List with automatic updating of first data of second validation list

Ease of Use

Easy

Version tested with

2002 

Submitted by:

chitosunday

Description:

Two validation list (Major Group/sub group) that automatically update the sub group putting the first data of the sub group in the second validation cell 

Discussion:

Most of us use the validation list with the indirect function on the second validation list. The disadvantage of using indirect function is that it is not automatically seem to be adjusted unless you click the second validation list. This method automatically update the second validation list inputting the first data of the chosen range. 

Code:

instructions for use

			

Private Sub Worksheet_Change(ByVal Target As Range) 'Only cell e4 will continue the macro command when you click it If Not Application.Intersect(Range("e4"), Target) Is Nothing Then 'name xx and zz as string data type Dim xx As String, zz As String 'place the value of e4 to xx string datatype xx = Range("e4").Value 'place the range address of the range name in cell e4 to zz zz = Range(xx).Address 'update cell g4 into first value of range named chosen in cell e4 Range("g4").Value = Range(zz).Cells(1).Value 'change the validation range to the one chosen in cell e4 Range("g4").Validation _ .Modify Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="=" & zz 'end of if statement End If End Sub

How to use:

  1. Put the above code in the specific sheet code .
  2. The intersect command will limit the firing of the macro to changes made in cell e4.
  3. xx is the container for the value of range e4
  4. zz is like a indirect function that converts the xx to it's reference range
  5. Range("g4").Value = Range(zz).Cells(1).Value just input the first data of ref range
  6. The last statement "validation" update the range of the validation list.
  7. In the sheet, you must name the range the sub service group into the name of the major reference group by using insert, name ,define or highlighting the range of the sub-group then clicking the name box (left upper of the menu) then typing the major ref name
 

Test the code:

  1. The source data (complete list) of the major group is in column A while the sub group is in column c.
  2. Choose the major reference in cell "E4" . In Cell "G4", the first data of the sub service list will appear.
 

Sample File:

validationlist.zip 12.77KB 

Approved by mdmackillop


This entry has been viewed 325 times.

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