I'm having a problem with VBA

Posted by Mark on January 15, 2002 10:47 AM

I have a combo box which selects a part number from sheet 1, column A [row source(=sheet1!a2:a200)].

On my form, beside the part number combo box, I have a text box that I would like to display the part name, located on sheet1, column B.

How can I call for the part name based on the part number selected with the combo box??

Posted by Andy Gee on January 15, 2002 11:03 AM

Use offset in the textbox =OFFSET(Sheet1!A1,D3-1,0) Where D3 is the link cell for the combo box. Both combo and link call are on sheet2

PS Text box?

Hope this helps

Posted by Russell Hauf on January 15, 2002 11:18 AM

Another thing you can do is to change your Row Source in your combo box to sheet1!a2:b200. Then you could have both the part and the name right there. If you do this, change your ColumnCount property to 2. If you really want the name in a different control (a text box), you could do what I said above, but in the ColumnWidths property box, type ";0" (no quotes). Then double-click on your combo box and under ComboBox1_Change() type:

TextBox1.Value = TextBox1.Value = ComboBox1.Column(1)

(Column(1) is actually the second column since columns are zero-based).

There are even more ways to do this, but I hope this gives you some ideas.

-Russell

Posted by Mark on January 15, 2002 1:09 PM

Thanks Russell & Andy....

I tried both, both worked. I understood how Russell's method works, so that's the method I used.

Another question, what will I need to do if I want:

a.) a text box on sheet2 to have the same value as a text box on sheet1?

b.) a text box on sheet2 to have the same value as a list box on sheet1?

Thanks,
Mark