Populating a combobox

[Guest]

I have a workbook that contains a sheet (Order) that the user will use to
enter a supply requisition by entering the item number and quantity via a
userform. Another sheet (Items) actually list the item numbers (Col A), and
Description (Col B). NOTE: The first row of this list is row 3. In the
event that new items are added, I did a COUNT to obtain the total number of
items and placed it in M1. Using that number (M1), how can I populate a
Combobox with the item number?

Also, I need to be able to "pull" the matching description for that item
number and place it in the appropriate cell in the Order sheet. If you can
provide me the code on how to pull the correct description, I would greatly
appreciate it. I can get it in the correct cell.

Thanks a million!!!

Les

 

[merjet]

The ComboBox can pick up the descriptions along the item numbers. That
will make it easier to get the description for a given item number.
The user doesn't have to see it. Set the ComboBox's ColumnCount to 2
and the 2nd column's width to 0, e.g. ColumnWidths = 30 pts; 0 pts. I
assume you have a CommandButton the user clicks to indicate having
selected an Item Number and quantity. Assuming this, something like
the following code should work.

Private Sub CommandButton1_Click()
Dim iRow As Integer
iRow = 1 + Sheets("Order").Range("A65536").End(xlUp).Row
Sheets("Order").Cells(iRow, 1) = ComboBox1.List(ComboBox1.ListIndex,
0)
Sheets("Order").Cells(iRow, 2) = CInt(TextBox1.Value)
Sheets("Order").Cells(iRow, 3) = ComboBox1.List(ComboBox1.ListIndex,
1)
End Sub

Private Sub UserForm_Activate()
Dim iCt As Integer
Dim r As Range
iCt = Sheets("Items").Range("M1")
Set r = Sheets("Items").Range("A3:B" & iCt + 2)
ComboBox1.RowSource = r.Worksheet.Name & "!" & r.Address
End Sub

Hth,
Merjet