PDA

View Full Version : Command Button to save, print and clear



SallysPizza
06-17-2008, 08:00 AM
I have a form users fill out to get what they need from our production dpeartment. The problem is, most don't save their orders after printing them, then they re-use an old order and type in new data while not changin everything on the form.

I already have a command button to save to the main server using this code:
Private Sub CommandButton1_Click()
ActiveSheet.SaveAs Filename:= _
"S:\Traffic\Prod Orders\" & Sheets("Sheet1").Range("B8") & " - " & Format(Now, "mm-dd-yy hh.mm AM/PM") & ".xls"
End Sub
What can I add to print, then clear the contents (including buttons & checkboxes), and finally bring a clear worksheet?

lucas
06-17-2008, 08:30 AM
Post the workbook......

SallysPizza
06-17-2008, 08:35 AM
Here it is. Not pretty.

lucas
06-17-2008, 08:45 AM
What if we just used the blank copy as a template....have it hidden. Create a new each time....they fill it out and hit the button and the copy is made per your button code and the active copy of the template is deleted?

lucas
06-17-2008, 09:32 AM
I personally think a better idea is to keep a copy of each order in the same workbook instead of creating a bunch of excel files, one for each order....

run this a couple of times to get an idea of what I am getting at......let me know if we are way off base for your needs.

SallysPizza
09-02-2008, 11:37 AM
I personally think a better idea is to keep a copy of each order in the same workbook instead of creating a bunch of excel files, one for each order....

run this a couple of times to get an idea of what I am getting at......let me know if we are way off base for your needs.

Apologies for not answering. I got called off this and onto some other pressing projects.

If we were in a single location, I think the template idea would work fine. But we're spread out with five different sales teams in 4 different locations. Also, the end user has the mental agility of a small soap dish. So I'm trying to make it as dummy proof as I can. I have been using this at our location and with the exception of being able to clear the form, it works well. Any other thoughts would be appreciated.

Bob Phillips
09-02-2008, 03:19 PM
A few thoughts.

How about clearing it all down on opening, that way it is always fresh? Harsh but might work.

I am with Steve though. Hold a template in the workbook. Start with a copy of that template, the user can fill it in. When they print it, save the workbook, hide that production form, and make a new one from the template (it will de facto be initialised).

SallysPizza
09-03-2008, 05:35 AM
A few thoughts.

How about clearing it all down on opening, that way it is always fresh? Harsh but might work.

I am with Steve though. Hold a template in the workbook. Start with a copy of that template, the user can fill it in. When they print it, save the workbook, hide that production form, and make a new one from the template (it will de facto be initialised).

I guess I'm not getting it. Thank you for your time, I do appreciate it.:)

lucas
09-03-2008, 06:38 AM
I don't know what happened to my attachment but it doesn't matter I guess since you don't wish to use a template.

The alternatives are, as Bob has suggested, you can clear all of the data on the sheet on open or on each use but you would have to address all of the controls on the sheet and the ranges and it would work but it would take some time to set up.

Another alternative would be to use a userform to imput the data. That way each time you run the form you could have completely new data but that would require some work on the design of the sheet to print also.