Combobox link to textbox

[SPLUCENA]

Hi all,

I have browsed this subject on the thread but I did not find specific answer to my problem. I am into inventory of items and I have created a userform and placed a combobox (as dropdown list for me to choose the products) and a text box for my qty input. Will you kindly help me put the right code if I choose product A in the combobox list the textbox entry will fall into the same row as the product on the appointed column in the excel sheet.



ex.

PRODUCT NAME QTY
A 25

Thanks for the help!!!


splucena

 

[Andrew Poulsom]

See if this works for you:

Private Sub CommandButton1_Click()
    Dim r As Long
    With Worksheets("Sheet1")
        r = Application.Match(ComboBox1.Value, Range("A:A"), False)
        .Range("B" & r).Value = Val(TextBox1.Text)
    End With
End Sub

Change the worksheet and range column references to suit.

 

[SPLUCENA]

Thanks! It worked!

 

[SPLUCENA]

Hi Andrew,

With the same criteria but on a different application.What could be the right code if I search my list of products(thru combobox) the corresponding datas on the same row of the product chosen will reflect on the labels as caption? I Have around 10 labels in my userform where I want the data to show when I chose a certain product on the combobox. All these datas are in sheet1 of excel with column A as the product name and column B-H as the description of the product.I would like to assign a label to every product description and have that description appear on the labels as caption.


Kindly assist.

thanks,

splucena

 

[Andrew Poulsom]

Here is an example with 2 labels:

Private Sub ComboBox1_Change()
    Dim r As Long
    With Worksheets("Sheet1")
        r = Application.Match(ComboBox1.Value, .Range("A:A"), False)
        Label1.Caption = .Cells(r, 2).Value
        Label2.Caption = .Cells(r, 3).Value
    End With
End Sub

 

[SPLUCENA]

Hi,

this codes get a yellow


r = Application.Match(ComboBox1.Value, .Range("A:A"), False)
</pre>

 

[SPLUCENA]

Private Sub ComboBox1_DropButtonClick()

Dim r As Long
With Worksheets("1L")
r = Application.Match(ComboBox1.Value, .Range("A:A"), False)
Label1.Caption = .Cells(r, 2).Value
Label2.Caption = .Cells(r, 3).Value
End With


the code in red highlight shows error

thanks,
splucena

 

[Andrew Poulsom]

What is the error? Are you sure what's in ComboBox1 appears in column A on sheet "1L"?

Notice that I used the Change event, not the DropButtonClick event.

 

[SPLUCENA]

Thanks, I'll try it later on......


splucena

 

[SPLUCENA]

Hi Andrew,

It is showing run time error 16 'type mismatch". I checked the combobox1 and it is assigned to column 1 .

Thanks,
splucena