can you write me a VBA code to insert into my Worksheet_Change event that will do the…
- 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!2:161 to only show list values where Suppliers!2:161 is equal to D3.
- 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 SubThis 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.
- User
error: Type Mismatch - on the line - .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Join(Application.Transpose(filterRange.Value), ",")
- Assistant
I apologize for the error. The issue is caused by trying to join multi-dimensional array directly in the
Formula1parameter of theValidation.Addmethod. 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 SubPlease replace the existing code in the Worksheet_Change event with this code, and it should resolve the "Type Mismatch" error.
- 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
- 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 SubThis 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.
- User
Thanks, It worked
- 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!