value of textboxes depends on combined values of 3 combobox in excel 2007 userform

[rydysh]

Hi all,

I would like to seek some help regarding my problem..I have a userform that contains 3 comboboxes and 4 textboxes.
What i want for my userform is when i select values from the 3 comboboxes, the 4 textboxes would be automatically filled up with their corresponding values.

the sheet where the vales would come from contains 7 columns.. the first 3 columns contains the values for the 3 comboboxes respectively.
the remaining 4 values contains the values corresponding to the combination of the first 3 column..

i want to know how to do this..kindly help..

[mehmetcik]

There is no userform on this workbook

[mehmetcik]

Try This Example.

I have built some error checking into it for you.

The loops for filling the comboboxes and text boxes are a bit of an over kill for this userform.
They are there to show you how you can build bigger and more complicated userforms.

Dim rngFind As Range
    Dim strValueToPick As String
    Dim rngPicked As Range
    Dim rngLook As Range
    Dim strFirstAddress As String
Dim LR As Integer

Private Sub ComboBox1_Change()
update
End Sub
Private Sub ComboBox2_Change()
update
End Sub

Private Sub ComboBox3_Change()
update
End Sub

Private Sub Label3_Click()

End Sub

Private Sub UserForm_Activate()
Sheets("Sheet1").Select
LR = Range("A1").End(xlDown).Row

For Count = 1 To 3
Me.Controls("ComboBox" & Count).RowSource = Worksheets("Sheet1").Range(Cells(1, Count), Cells(LR, Count)).Address
Me.Controls("ComboBox" & Count).ListIndex = 0
Next
End Sub


Private Sub update()

On Error GoTo 200

    Range("A1:A" & LR).Select

    Selection.Find(What:=ComboBox1.Text, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate

    Range("B" & ActiveCell.Row() & ":B" & LR).Select
    Selection.Find(What:=ComboBox2.Text, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate

    Range("C" & ActiveCell.Row() & ":C" & LR).Select
    Selection.Find(What:=ComboBox3.Text, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate

   SR = ActiveCell.Row
Range("a1").Select

If ComboBox1.Value <> Range("A" & SR).Value Or CStr(ComboBox2.Value) <> CStr(Range("b" & SR).Value) Or CStr(ComboBox3.Value <> CStr(Range("C" & SR).Value)) Then GoTo 10

For Count = 1 To 3
Me.Controls("TextBox" & Count).Text = Cells(SR, Count + 3).Text
Next
Exit Sub

10

For Count = 1 To 3
Me.Controls("TextBox" & Count).Text = ""
Next

200 'EXIT

End Sub

[jindon]

Different method.

Option Explicit

Private dic As Object

Private Sub UserForm_Initialize()
    Dim a, i As Long
    Set dic = CreateObject("Scripting.Dictionary")
    dic.CompareMode = 1
    a = Sheets("sheet1").Cells(1).CurrentRegion.Value
    For i = 2 To UBound(a, 1)
        If Not dic.exists(a(i, 1)) Then
            Set dic(a(i, 1)) = CreateObject("Scripting.Dictionary")
            dic(a(i, 1)).CompareMode = 1
        End If
        a(i, 2) = CStr(a(i, 2)): a(i, 3) = CStr(a(i, 3))
        If Not dic(a(i, 1)).exists(a(i, 2)) Then
            Set dic(a(i, 1))(a(i, 2)) = CreateObject("Scripting.Dictionary")
            dic(a(i, 1))(a(i, 2)).CompareMode = 1
        End If
        dic(a(i, 1))(a(i, 2))(a(i, 3)) = _
        VBA.Array(a(i, 4), a(i, 5), a(i, 6), a(i, 7))
    Next
    Me.ComboBox1.List = dic.keys
End Sub

Private Sub ComboBox1_Click()
    ClearTB 1, 4
    ClearCB 2, 3
    With Me
        If .ComboBox1.ListIndex > -1 Then
            .ComboBox2.List = dic(.ComboBox1.Value).keys
        End If
    End With
End Sub

Private Sub ComboBox2_Click()
    ClearTB 1, 4
    ClearCB 3, 3
    With Me
        If .ComboBox2.ListIndex > -1 Then
            .ComboBox3.List = dic(.ComboBox1.Value)(.ComboBox2.Value).keys
        End If
    End With
End Sub

Private Sub ComboBox3_Click()
    Dim i As Long, w
    With Me
        If .ComboBox3.ListIndex > -1 Then
            For i = 1 To 4
                w = dic(.ComboBox1.Value)(.ComboBox2.Value)(.ComboBox3.Value)
                Me.Controls("TextBox" & i).Value = _
                dic(.ComboBox1.Value)(.ComboBox2.Value)(.ComboBox3.Value)(i - 1)
            Next
        End If
    End With
End Sub

Private Sub ClearTB(s As Long, e As Long)
    Dim i As Long
    For i = s To e
        Me.Controls("TextBox" & i).Value = ""
    Next
End Sub

Private Sub ClearCB(s As Long, e As Long)
    Dim i As Long
    For i = s To e
        Me.Controls("ComboBox" & i).Clear
    Next
End Sub

[rydysh]

hi mehmetcik, jindon,

its working! thanks for the quick reply..ill study your advices and hopefully someday i can also contribute to others here.. thank you so much! your the best! this site is the best!

Ryan