Textbox value to be pulled from a list depending on 2 other textboxes

Question

I have a user form (Userform1) that allows users to select items(max 24 items) from check boxes. (Checkbox1 to checkbox24)

Each check box is linked to a different text box so that users can select more than 1 item.

If checkbox 1 is selected, then textbox 1 = "A"

If checkbox 2 is selected, then textbox 2 = "B" and it goes on like this.

Now these items are available in Small and Medium.

I have a list of all the items and their different prices per size.

Example of My list:

Items   Price (Small)   Price (Medium)
A       10              15
B       7               12
and it goes on.

I want the userform to pull up the prices of the items based on their sizes.

So that when a user selects item "A" in small - a text box will show the price 10.

I tried:

Private Sub Textbox1_change()
Dim sizes As String
sizes = Me.TextBox1.Value

On Error Resume Next

If textboxsize.Value = "SMALL" Then Me.TextBox25.Value = 
application.worksheetfunction.vlookup(sizes, sheets("PRICE 
LIST").range("A2:C100000"),2,0)
End If

If textboxsize.Value = "MEDIUM" Then Me.TextBox25.Value = 
application.worksheetfunction.vlookup(sizes, sheets("PRICE 
LIST").range("A2:C100000"),3,0)  
End If

End Sub

It is working but the constraint is that i have to copy the code to every textbox of the userform and change the ids.

Is there any easier way to do this?

Thanks.

May refer [link1](https://stackoverflow.com/questions/53789201/about-multipage-unless-all-pages-have-input-commandbuttom2-is-disabled/53789939#53789939) and [link2](https://stackoverflow.com/questions/53123046/implementing-a-change-event-to-check-for-changes-to-textbox-values-and-enabling) — Ahmed AU, Jan 30 '19 at 09:22
Actually, you would only need to bind your code to the listener only once if you use the dropdown combo box instead (I think that's what it's called.. It's been awhile since I did VBA) — kerwei, Jan 30 '19 at 09:30

score 0 · Answer 1 · answered Jan 30 '19 at 09:32

Not clear how, exactly, you are set up but please consider a variation of I demonstrate below.

Private Sub Textbox1_Change()

    SetPrice 25
End Sub

Private Sub SetPrice(TbxNumber As Integer)

    Dim Sizes As String
    Dim Price As Variant
    Dim Clm As Long

    Sizes = Me.Textbox1.Value
    Clm = IIf(TextboxSize.Value = "SMALL", 2, 3)

    On Error Resume Next
    Price = Application.WorksheetFunction.VLookup(Sizes, _
            Worksheets("PRICELIST").Range("A2:C100000"), Clm, 0)
    Me.Controls("TextBox" & TbxNumber).Value = Price
End Sub

You would need a Change event procedure for each TextBox. That procedure would call the Sub SetPrice, passing the number of the TextBoxto write to (not sure that it is always different, and therefore not sure that it is required at all). The Sub will retrieve the price and write it to the TextBox. If it's always the same, scrap the parameter and just specify the TextBox directly in the sub.

Note that the Iif function will return 3 if the size isn't "SMALL". By comparison, your code would return a blank if it's neither "SMALL" nor "MEDIUM". If you need that result consider Select Case rather than If/Else.

Textbox value to be pulled from a list depending on 2 other textboxes

1 Answers1