VBA Express : Excel - Two Validation List with automatic updating of first data of second validation list

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:

Put the above code in the specific sheet code .
The intersect command will limit the firing of the macro to changes made in cell e4.
xx is the container for the value of range e4
zz is like a indirect function that converts the xx to it's reference range
Range("g4").Value = Range(zz).Cells(1).Value just input the first data of ref range
The last statement "validation" update the range of the validation list.
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:

The source data (complete list) of the major group is in column A while the sub group is in column c.
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 250 times.