Obtaining an offset value from a userform combobox

**kanuvas** · 02-20-2008, 05:50 AM

Hi all,

I currently have a userform setup so that when the user puts a value into a textbox and press a button it prints the values on the screen.

I have now added a combobox into the form and want the user to select a value from worksheet 2 (named test1 and test2).
What I want it too do is print out the values to the right of the selected combobox item(test1 or test2).

I cannot find the syntax to allow me to do this.

The excel sheet I am working on is attached, any help will be appreciated.

Cheers

**royUK** · 02-20-2008, 07:11 AM

I can't understand what you are doing. You form looks like it is designed to enter data to the parts sheet. What is the purpose of the combobox?

**kanuvas** · 02-20-2008, 09:13 AM

The combo box is meant to look at the column A in sheet2. When the user selects either test1 or test2 and click on the 'Add this part' button I need it to print out column B,C,D and E in sheet 2 with the row depending on the value in the combo box. These values need to be printed into sheet1 column E,F,G and H along with the other information that is printed.

Hope this helps.

cheers

**royUK** · 02-20-2008, 09:26 AM

You will need to check this, I don't know which TextBoxes refer to which Column on Sheet 2.

Private Sub ComboBox1_Change()
    Dim R      As Long
    With Me
        R = .ComboBox1.ListIndex + 1
        .TxtPart = Sheet2.Cells(R, 2).Value
        .TxtLoc = Sheet2.Cells(R, 3).Value
        .TxtDate = Sheet2.Cells(R, 4).Value
        .TxtQty = Sheet2.Cells(R, 5).Value
    End With
End Sub