Help with user form

R

RobJ

I need help with a user form that basically mimicks my worksheet. I need to
get the data from the 29 text boxes on the user form to the matching columns
and row on my worksheet.
My worksheet looks like this:
V# Vendor Name Inv-No Inv Amount Cat Amount Cost Retail Cost Retail
Cost Retail
the above columns are repeated 4 times
My user form basically follows the same form. I need to have the user enter
the data for the invoice which is then copied to the worksheet, and start the
process over again until all invoice are entered.

Any help is appreciated, I have been struggling with this for 5 days.

Rob
 
J

JulieD

Hi Rob

this is probably the wrong question at this stage in the process, but why
don't you just use the built in Data / Form?

if this isn't an option ... what stage are you up to with your userform and
what specifically do you need help with?

Cheers
JulieD
 
R

RobJ

Hi Julie
I am fairly new to all this, if by built in data/form,you mean the actual
spreadsheet
it is fairly hard to navigate. If by this you mean some other built in
function, please let me know how to do it.
Thanks again
RobJ
 
R

RobJ

Dave
Thanks I did download form, however because it inserts rows, it will not
take the data to where I need it to end up for printing purposes. Maybe I am
going about this whole process wrong, I think I need someone to take a look
at my whole application. Any other feedback is appreciated
RobJ
 
J

JulieD

Hi RobJ

Not knowing exactly what you're trying to do & why you need certain things
in certain places for printing ... makes it a bit hard to give a complete
answer. The Form found under the Data menu add the NEW records to the end
of the current records and doesn't insert any rows (John Walkenbach's may -
but i've not used this one).

As for printing ... using either autofilters (Data / Filter / AutoFilter);
subtotals (Data / Subtotals - but do sort first) and pivot tables (Data /
Pivot Table & Pivot Chart reports) you can extract / summaries and list
records in a hugh number of useful (& printable) ways. Basically, when
constructing an excel list you put your headings across the top row & then
all the data underneath these headings. Re-reading your original post i
notice that you seem to repeat your column headings four times and am
wondering why.

If you'ld like to post back with more details on what you're trying to
achieve and why you've laid your data out like this we'll be happy to
assist.

However, to give you a quick answer to your original question ... assuming
that you have 28 boxes & 28 worksheet columns and your headings are in row 1
starting at column A and when the cancel button is pressed the form is to be
closed then the code for the OK button would be something like:

sub cmdOk_Click()

If Range("A2").value = "" then 'first cell in the data area of your
worksheet - looking for first blank line
Range("A2").select
else
Range("A1").select
Selection.End(xlDown).Select
Activecell.offset(1,0).select
end if

activecell.value = textbox1.value 'place the information in for your
first textbox ... substitute textbox1 with the actual name of the textbox,
same for the others below
activecell.offset(0,1).value = textbox2.value
activecell.offset(0,2).value = textbox3.value
activecell.offset(0,3).value = textbox4.value
'repeat for all your textboxes

For Each ctl In UserForm1.Controls 'clearing the textboxes in the form
for the next invoice.
If TypeName(ctl) = "TextBox" then
ctl.Value = ""
End If
Next

End sub

Cheers
JulieD
 
Top