Choose Invoice# fills userform
 

Hi,
Well looking for trouble I am starting my second userform. On my first user
form I have buttons First, Prev, Next, and Last these show a row number in
the database and fill in the form with info. I would like on this one for the
user to have the option of choosing the invoice number instead of finding the
row number. Can anyone get me started.
Example:
First Prev. 12002(invoice#) Next Last

THen the form is filled in witht the data fromt he databse that correlates
to the invoice number
Qty 100 Price $1.0 Frt. $.35 Date: 11/4/04

You get the idea. Thank you Jennifer
Though daily learning, I LOVE EXCEL!
Jennifer

Choose Invoice# fills userform
 

Hi,
Something along these lines which finds the invoice number (and
assumes it is unique) in row "r" and assigns fields to Quantity, Price etc:


Sub TestMe()
Call FindInvoice("12346") ' Invoice number as string in case they can
be alphanumeric
End Sub

Sub FindInvoice(InvoiceNo)

Dim r As Long

With Worksheets(1).Range("a1:f500") ' Change to suit your needs
Set c = .Find(InvoiceNo, LookIn:=xlValues)
If Not c Is Nothing Then 'invoice found .....
' Get data from this row i.e. containing invoice
r = c.Row
' e.g. ...
Qty = Cells(r, "B")
Price = Cells(r, "C")
' etc .....
Else
MsgBox "Invoice " & InvoiceNo & " was not found"
End If
End With

End Sub


HTH


"Jennifer" wrote:

Hi,
Well looking for trouble I am starting my second userform. On my first user
form I have buttons First, Prev, Next, and Last these show a row number in
the database and fill in the form with info. I would like on this one for the
user to have the option of choosing the invoice number instead of finding the
row number. Can anyone get me started.
Example:
First Prev. 12002(invoice#) Next Last

THen the form is filled in witht the data fromt he databse that correlates
to the invoice number
Qty 100 Price $1.0 Frt. $.35 Date: 11/4/04

You get the idea. Thank you Jennifer
Though daily learning, I LOVE EXCEL!
Jennifer

Choose Invoice# fills userform
 

why not have a combobox with the IDs in it. Your user selects an ID. Th
ecombos change event can then be used to search th etable for the correct row
number....

The good news is that the combobox listindex tellwhich item is selected,
starting at zero for the first item. As your data starts at row 2, then
simply adding 2 to the combos list index gives you the correct row...no need
for complex searches.

eg say your order id is column A

in the initialise event of the form..



Private Sub cmbOredrID_Change()
RowNumber = cmbOredrID.ListIndex + 2
End Sub

Private Sub UserForm_Initialize()
Dim cell As Range
With Worksheets("Datasheet")
For Each cell In .Range(.Range("A2"), .Range("A2").End(xlDown)).Cells
cmbOredrID.AddItem cell.Value
Next
End With
End Sub




"Jennifer" wrote:

Hi,
Well looking for trouble I am starting my second userform. On my first user
form I have buttons First, Prev, Next, and Last these show a row number in
the database and fill in the form with info. I would like on this one for the
user to have the option of choosing the invoice number instead of finding the
row number. Can anyone get me started.
Example:
First Prev. 12002(invoice#) Next Last

THen the form is filled in witht the data fromt he databse that correlates
to the invoice number
Qty 100 Price $1.0 Frt. $.35 Date: 11/4/04

You get the idea. Thank you Jennifer
Though daily learning, I LOVE EXCEL!
Jennifer

Choose Invoice# fills userform
 

Ok,
I think I totally get it. Of coarse me getting it to work and thinking I
understand are two differant things. I'll work on it. Ditto (this will be my
thanks for the 100th time)
--
Though daily learning, I LOVE EXCEL!
Jennifer


"Patrick Molloy" wrote:

why not have a combobox with the IDs in it. Your user selects an ID. Th
ecombos change event can then be used to search th etable for the correct row
number....

The good news is that the combobox listindex tellwhich item is selected,
starting at zero for the first item. As your data starts at row 2, then
simply adding 2 to the combos list index gives you the correct row...no need
for complex searches.

eg say your order id is column A

in the initialise event of the form..



Private Sub cmbOredrID_Change()
RowNumber = cmbOredrID.ListIndex + 2
End Sub

Private Sub UserForm_Initialize()
Dim cell As Range
With Worksheets("Datasheet")
For Each cell In .Range(.Range("A2"), .Range("A2").End(xlDown)).Cells
cmbOredrID.AddItem cell.Value
Next
End With
End Sub




"Jennifer" wrote:

Hi,
Well looking for trouble I am starting my second userform. On my first user
form I have buttons First, Prev, Next, and Last these show a row number in
the database and fill in the form with info. I would like on this one for the
user to have the option of choosing the invoice number instead of finding the
row number. Can anyone get me started.
Example:
First Prev. 12002(invoice#) Next Last

THen the form is filled in witht the data fromt he databse that correlates
to the invoice number
Qty 100 Price $1.0 Frt. $.35 Date: 11/4/04

You get the idea. Thank you Jennifer
Though daily learning, I LOVE EXCEL!
Jennifer

Choose Invoice# fills userform
 

Love it! Wow that was really easy. Ditto!
--
Though daily learning, I LOVE EXCEL!
Jennifer


"Patrick Molloy" wrote:

why not have a combobox with the IDs in it. Your user selects an ID. Th
ecombos change event can then be used to search th etable for the correct row
number....

The good news is that the combobox listindex tellwhich item is selected,
starting at zero for the first item. As your data starts at row 2, then
simply adding 2 to the combos list index gives you the correct row...no need
for complex searches.

eg say your order id is column A

in the initialise event of the form..



Private Sub cmbOredrID_Change()
RowNumber = cmbOredrID.ListIndex + 2
End Sub

Private Sub UserForm_Initialize()
Dim cell As Range
With Worksheets("Datasheet")
For Each cell In .Range(.Range("A2"), .Range("A2").End(xlDown)).Cells
cmbOredrID.AddItem cell.Value
Next
End With
End Sub




"Jennifer" wrote:

Hi,
Well looking for trouble I am starting my second userform. On my first user
form I have buttons First, Prev, Next, and Last these show a row number in
the database and fill in the form with info. I would like on this one for the
user to have the option of choosing the invoice number instead of finding the
row number. Can anyone get me started.
Example:
First Prev. 12002(invoice#) Next Last

THen the form is filled in witht the data fromt he databse that correlates
to the invoice number
Qty 100 Price $1.0 Frt. $.35 Date: 11/4/04

You get the idea. Thank you Jennifer
Though daily learning, I LOVE EXCEL!
Jennifer