none
Dependent validations RRS feed

  • 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

    Wednesday, October 30, 2019 7:01 AM

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 Sub
    
    Code 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 Sub
    For your convenience, I've shared "Rate.xlsm" via OneDrive.
    Please download and check it.

    Regards,

    Ashidacchi -- http://hokusosha.com

    Wednesday, October 30, 2019 9:00 AM
  • 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? 

    Wednesday, October 30, 2019 11:51 AM
  • Hi M.Awal,

    Could you add more validation/combo box?

    Would it be possible to add as a separate column for 2020-21 price, such as increase the price by 3% for 2020-21? 
    The Excel book you shared has only one work sheet. So, I cannot make an image of another sheet.
    I guess it would be possible.  That's what I can say, unless you provide/share the book which has a price sheet.

    Regards,

    Ashidacchi -- http://hokusosha.com


    Wednesday, October 30, 2019 10:32 PM
  • Hi: sorry for the late reply. Firstly, I would like to create the dependent validation as part of macro . Something like what exist in the user form. I know the workbook that I provided has one worksheet. I want to keep that worksheet separate from the worksheet where user can select different products/services using dependent validation. Thanks
    Friday, November 1, 2019 11:04 PM
  • 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

    Saturday, November 2, 2019 1:06 AM
  • 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.

    Saturday, November 2, 2019 5:06 AM
  • Hi,

    I'd like to confirm:
    Do you need four forms?
    Is each of them dependent from other forms?

    I want to make your requirement clear before adding UserForms and writing code. 

    Also is it possible to place all the user forms in a different worksheet?


    As far as I know, it would be not possible.
    you need to insert UserForm(s) into each Excel file and to copy&paste VBA code in each UserForm, one by one manually.  

    Regards,

    P.S.
    Think which is easier or time saving,
    a) copy & paste UserForm and VBA code into Excel file
    b) copy a base Excel file as a new Excel file, and copy & paste original data

    Ashidacchi -- http://hokusosha.com


    • Edited by Ashidacchi Saturday, November 2, 2019 7:39 AM
    Saturday, November 2, 2019 7:31 AM
  • Can I please have something like below, there will be multiple drop down in my case?

    Saturday, November 2, 2019 7:43 AM
  • Hi,

    I'm afraid you need to explain more clear and detailed requirements.

    Regards, 

    Ashidacchi -- http://hokusosha.com

    Saturday, November 2, 2019 7:48 AM
  • 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

    Saturday, November 2, 2019 9:53 AM
  • Hi,

    Any updates about this problem now?

    Regards,

    Emi

    Wednesday, November 6, 2019 8:56 AM
    Moderator
  • Hi Emi: Thanks for following up. After my last post I haven't received any reply. I am still waiting.
    Wednesday, November 6, 2019 10:56 AM