excelvbauserform

Return on Textbox1 value based upon two criteria in combobox and label


I need an expert help after trying a lot without success, please.

I have a price list in Sheet1 with 3 columns:

Medical Procedure
Type
Value of Procedure

In a userform, I need to return in Textbox1 the value of the procedure based on the criteria selected in combobox1 (with values that can be found in Medical Procedure column in Sheet1) and the caption in label1 (which already is populated with a value that can be encounter in the Type column in Sheet1).

I tried this found here in stackoverflow from the user B Hart (thanks, B Hart!), but I wasn't able to change it to return in a textbox as a numerical value (this vba insert the found value in a listbox instead). Another issue is that the criteria below is in two combobox. I need the two criteria to be in a combobox and another in a label.

Private Sub GetCondStrandValue()
Dim iRow As Long
Dim strValue As String

strValue = vbNullString
If Me.ComboBox1.Value = vbNullString Or Me.ComboBox2.Value = vbNullString Then Exit Sub

With Planilha1
    For iRow = 2 To .Range("A65536").End(xlUp).Row
        If StrComp(.Cells(iRow, 1).Value, Me.ComboBox1.Value, 1) = 0 And _
         StrComp(.Cells(iRow, 2).Value, Me.ComboBox2.Value, 1) = 0 Then
            strValue = .Cells(iRow, 3).Value
            Exit For
        End If
    Next
End With

If strValue = vbNullString Then Exit Sub
With Me.ListBox1
    'If you only want a single value in the listbox un-comment the .clear line
    'Otherwise, values will continue to be added
    '.Clear
    .AddItem strValue
    .Value = strValue
    .SetFocus
End With
End Sub

Solution

  • Maybe something like this:

    Private Sub combobox1_Change()
    
        Dim lastRow As Integer
    
        lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    
        With Me
    
            For r = 2 To lastRow
    
                If Sheets("Sheet1").Cells(r, 1) = .ComboBox1.Value And Sheets("Sheet1").Cells(r, 2) = .Label1.Caption Then
                    .TextBox1.Text = Sheets("Sheet1").Cells(r, 3)
                    Exit For
                End If
    
            Next
    
        End With
    
    End Sub