Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
invoice toolbar for invoice calcuation and assign number | Excel Discussion (Misc queries) | |||
missing invoice toolbar when save customised invoice | New Users to Excel | |||
Invoice templet Excel97 to 2003 invoice toolbar missing | Excel Discussion (Misc queries) | |||
How do I change the invoice number assigned in Invoice template... | Excel Discussion (Misc queries) | |||
Userform Combobox to choose date | Excel Programming |