Retaining history data in cells when using a form text box
I'm trying to create a macro using a form that will update a
spreadsheet, . The macro will have to add a sheet, copy the info from
the previous days, save it using current date, update the information
that the user inputs into the form, then create a chart, and save the
chart with the current date. Ideally I'd like the saved chart be in
pdf format but that's not a necessity at this point. I have gotten
this 'design' to work using a msgbox but that's way to cumbersome and
defeats the purpose of efficiency
Basically the form will have 6 text boxes per multi tabbed page ( multi
pages = total # of products) which after the ok button is clicked it
will populated the appropriate cells with a number. The user will need
only click on the product tabs that have changed that day, to update
the spreadsheet, why I'm copying the previous days sheet. So my
question - the row calculates a total number and percentage based on
the column entries, how do I maintain the information from the previous
day? I found that when I don't enter a value in the forms text box,
i.e. leave it blank the macro deletes the entry, which throws off the
numbers. The numbers are being calculated with basic named formulas
in the spreadsheet.
Spread sheet Layout
Column Headings:
Completed In progress Defects On hold Pending Assigned total
Rows:
Product 1
Product 2
Product 3
Product 4
etc...
Form layout for each tabbed page
Completed _____
In Progress _____
Defects _____
On Hold _____
Pending _____
Assigned _____
OK Cancel
I'm a real newbie with VBA and much of what I've learned and put into
practice has come from this board. So thanks much for all the help
|