Dependent validations
Question
-
Hi: Can I please get assistance on creating a dependent validation through visual basic as per below? For example, if I select "01. Static Guarding" then I would only see the relevant distinct values under Description column that linked to "01 static Guarding" and the same goes for Category column as well. Please see the link below. Any help would be greatly appreciated. Thanks
https://1drv.ms/x/s!Amc8fiGpDxekhw5a3WsWKl4R42QS?e=Uld51f
All replies
-
Hi M.Awal,
I've made a sample with Macro (VBA).
One row is added at the top of the sheet.
Cell [A1] is for data validation.
Code in ThisWorkbook:
Option Explicit Public LastRow As Integer Private Sub Workbook_Open() Dim wkValue As String: wkValue = "" Dim list As String: list = "" ' --- With Sheets("Rate") ' --- modify row count according to your data .Rows("3:1000").Hidden = False ' -- visible all rows ' --- get last row in column [A] LastRow = .Cells(Rows.Count, 1).End(xlUp).Row ' --- concatenate list with unique value Dim r As Integer For r = 3 To LastRow If (.Cells(r, 1).Value <> wkValue) Then list = list & "," & .Cells(r, 1).Value wkValue = .Cells(r, 1).Value End If Next End With ' --- [A1] validation list With Sheets("Rate").Range("A1").Validation .Delete .Add Type:=xlValidateList, _ Operator:=xlEqual, _ Formula1:=list End With End SubCode in sheet ("Rate") --- Sheet1
Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If (Intersect(Target, Range("A1")) Is Nothing) Then Exit Sub End If ' --- range A1 changed Application.ScreenUpdating = False Dim LastRow As Integer Dim r As Integer For r = 3 To ThisWorkbook.LastRow If (Cells(r, 1).Value = Range("A1").Value) Then Rows(r).Hidden = False Else Rows(r).Hidden = True End If Next Application.ScreenUpdating = True End SubFor your convenience, I've shared "Rate.xlsm" via OneDrive.
Please download and check it.
Regards,Ashidacchi -- http://hokusosha.com
-
Hi Ash: Thank you so much and it looks very nice. If it is not too much trouble I would like to create multiple validations/combo box dependent on the previous selection. For example, after selecting 01. Static.., the user needs to select another validation under "Description" column and it goes on until the final selection "Join column" where the price gets populated. When the second and third validation gets added then the values underneath these categories would show as distinct values .
I would like all the price data in a separate page where user can't see it and the validation in a separate page.
Would it be possible to add as a separate column for 2020-21 price, such as increase the price by 3% for 2020-21?
-
Hi M.Awal,
I've made a sample (version 2) and shared it via OneDrive.
Download "Rate_v2.xlsm" and see it.
I hope it will be helpful.
Regards,Ashidacchi -- http://hokusosha.com
-
Hi Ash: Thanks for your solution, looks good. Would it be possible to include three more user forms (dependent validations) for columns "Description", "Category", "Join Column"? The idea would be when an option is selected under the last column "Join Column" the relevant price would be selected. Also is it possible to place all the user forms in a different worksheet? Greatly appreciated your time on this.
-
Can I please have something like below, there will be multiple drop down in my case?
-
Hi : As per the screen shot above country (in my case it would be "Service Category"). After selecting an option in the "Service Category" then the next drop down to be populated City (in my case it would be "Description". After the selecting the option the next drop down would be "Category". After selecting the option in the "Category" drop down I am expecting the relevant price to be populated. No further drop down is required for "Join Column".
Also I would like to avoid the output in Message box. Rather I would like to show it for example, if I select the first line below then it would show 01. Static Guarding TT2 Regional $108.98. Hope that clarifies
