Userform 2 comboboxes

[brentcook]

I have a form that has 2 data validation list cells. The first is linked to a table column. The second contains the folowing formula:

=OFFSET(Contacts!$A$2,MATCH($U$4,School,0)-1,1,COUNTIF(School,$U$4),1)

It populates based on the column to the right of the matches of the 1st column. Then I have multiple other cells that autopopulate based on that cell.

I asked a question last weeked, but got no responses, so decided to try a different angle. I decided to make a user form with comboboxes. The first one was easy to link to the desired column. The second is not. I am a VBA infant (if that isn't obvious yet). I was hoping someone would be willing to help me accomplish the above formula for the second combobox.

Any help is greatly appreciated.

Thanks

 

[brentcook]

I'm sorry. I have a worksheet, not a form. I mistyped. I "created" a user form that has two comboboxes in it. The second list needs to list to be refined based on what was entered into the first list. Then I plan on having that put into the worksheet to carry out the rest of my tasks. A starting point on the code for the 2nd would be immensly helpful!

 

[brentcook]

I'm going to keep trying. I have tried coding it out, but I can't (not surprisingly) figure it out.

Here's what I have:

Private Sub ComboBox1_Change()

Dim x As Long
Dim Counselor As Range
Dim myTable As ListObject
Dim myArray As Variant
Dim TempArray As Variant
Const sRngName As String = "Contacts"


    Dim sGrp As String
    
    Set myTable = contacts.Sheet
    Temp.Array = myTable.DataBodyRange
    sGrp = ComboBox1.List(ComboBox1.ListIndex, 1)
    ComboBox2.Clear
    For x = 1 To Counselor.Rows.Count
        If School1.Cells(x, 2) = sGrp Then
            ComboBox2.AddItem Counselor.Cells(x, 1)
        End If
    Next x
End Sub

Is anyone able to help out? Thanks in advance for trying.

 

[brentcook]

Just an update in case there are any users who are struggling with something similar. I worked around the issue. I kept the data validation. I inserted a combobox over the 1st cell. It allowed me to take advantage of the autofill function of the combobox. I then used the code

Private Sub ComboBox1_Change()



Dim ws As Worksheet


Set ws = Worksheets("Team Sheet")


ActiveSheet.Unprotect


ws.Range("U4") = ComboBox1.Value


ActiveSheet.Protect


End Sub

Just update the ws.Range to the correct cell you are looking for.

Then the rest of the data validation continued to work just as before.

If you are not much into coding and need a little better explaination, feel free to message me directly.