Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0125· OpenAIAi2 WildChat-4.8M (AI2)

can you write me a VBA code to insert into my Worksheet_Change event that will do the…

  1. User

    can you write me a VBA code to insert into my Worksheet_Change event that will do the following: When I change the value in D3 of sheet 'Order Form', change the Data Validation List in D4 of sheet 'Order Form' which is taken from Suppliers!BB2:BB161 to only show list values where Suppliers!AA2:AA161 is equal to D3.

  2. Assistant

    Sure! Here is the VBA code you can insert into the Worksheet_Change event of your 'Order Form' worksheet:

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim supplierList As Range
        Dim supplierCell As Range
        Dim filterCriteria As String
        Dim filterRange As Range
        
        'Check if the changed cell is D3
        If Not Intersect(Target, Me.Range("D3")) Is Nothing Then
            'Set the filter criteria to the value in D3
            filterCriteria = Me.Range("D3").Value
            Set supplierList = Sheets("Suppliers").Range("A2:A161")
            
            'Filter the range based on the criteria in D3
            supplierList.AutoFilter Field:=1, Criteria1:=filterCriteria
            
            'Set the filter range to show only the visible cells in column B
            Set filterRange = Sheets("Suppliers").Range("B2:B161").SpecialCells(xlCellTypeVisible)
            
            'Update the data validation list in D4 with the values from the filter range
            Me.Range("D4").Validation.Delete
            With Me.Range("D4").Validation
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Join(Application.Transpose(filterRange.Value), ",")
            End With
            
            'Turn off the filter
            supplierList.AutoFilter
        End If
    End Sub
    

    This code will dynamically update the data validation list in cell D4 whenever you change the value in cell D3 on the 'Order Form' worksheet. The list will be filtered to only show values where the corresponding value in column A of the 'Suppliers' worksheet matches the value in cell D3.

  3. User

    error: Type Mismatch - on the line - .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Join(Application.Transpose(filterRange.Value), ",")

  4. Assistant

    I apologize for the error. The issue is caused by trying to join multi-dimensional array directly in the Formula1 parameter of the Validation.Add method. To fix this, we can first write the values to a separate array and then use that array in the validation formula.

    Here is the corrected code:

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim supplierList As Range
        Dim supplierCell As Range
        Dim filterCriteria As String
        Dim filterRange As Range
        Dim arrValues() As Variant
        Dim i As Integer
        
        'Check if the changed cell is D3
        If Not Intersect(Target, Me.Range("D3")) Is Nothing Then
            'Set the filter criteria to the value in D3
            filterCriteria = Me.Range("D3").Value
            Set supplierList = Sheets("Suppliers").Range("A2:A161")
            
            'Filter the range based on the criteria in D3
            supplierList.AutoFilter Field:=1, Criteria1:=filterCriteria
            
            'Set the filter range to show only the visible cells in column B
            Set filterRange = Sheets("Suppliers").Range("B2:B161").SpecialCells(xlCellTypeVisible)
            
            'Write the visible values to an array
            ReDim arrValues(1 To filterRange.Rows.Count)
            For i = 1 To filterRange.Rows.Count
                arrValues(i) = filterRange.Cells(i, 1).Value
            Next i
            
            'Update the data validation list in D4 with the values from the array
            Me.Range("D4").Validation.Delete
            With Me.Range("D4").Validation
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Join(arrValues, ",")
            End With
            
            'Turn off the filter
            supplierList.AutoFilter
        End If
    End Sub
    

    Please replace the existing code in the Worksheet_Change event with this code, and it should resolve the "Type Mismatch" error.

  5. User

    The filtering in the code is not working as it should. When I change the value in D3, I am only getting a single value in the data list in D4

  6. Assistant

    I see what you mean. It seems that the code is not correctly capturing and writing the visible values to the array. Let's revise the code to read the visible values correctly and apply them to the data validation list in D4. Below is the updated code:

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim supplierList As Range
        Dim filterCriteria As String
        Dim dataRange As Range
        Dim selRange As Range
        Dim cell As Range
        Dim validationFormula As String
    
        If Not Intersect(Target, Me.Range("D3")) Is Nothing Then
            Application.EnableEvents = False
            filterCriteria = Me.Range("D3").Value
            Set supplierList = Sheets("Suppliers").Range("A2:A161")
            Set dataRange = supplierList.Offset(0, 1).Resize(supplierList.Rows.Count, 1)
            
            supplierList.AutoFilter Field:=1, Criteria1:=filterCriteria
            Set selRange = Sheets("Suppliers").Range("B2:B161").SpecialCells(xlCellTypeVisible)
            
            validationFormula = ""
            For Each cell In selRange
                If validationFormula <> "" Then
                    validationFormula = validationFormula & ","
                End If
                validationFormula = validationFormula & cell.Value
            Next cell
            
            Me.Range("D4").Validation.Delete
            With Me.Range("D4").Validation
                .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=validationFormula
            End With
            
            supplierList.AutoFilter
            Application.EnableEvents = True
        End If
    End Sub
    

    This updated code correctly captures the visible values from the filtered range in column B of the 'Suppliers' sheet and adds them to the data validation list in cell D4 on the 'Order Form' sheet when the value in D3 is changed. Please replace the previous code with this updated version in your Worksheet_Change event. It should now display the correct list values in D4 based on the filter criteria in D3.

  7. User

    Thanks, It worked

  8. Assistant

    You're welcome! I'm glad to hear that it worked. If you have any more questions or need further assistance with VBA or anything else, feel free to ask. Happy coding!