PDA

View Full Version : UserForm save spreadsheet on change



Emoncada
12-19-2007, 08:26 PM
I have a userform that sends data to a spreadsheet. Is it possible for when cell A2 has a value for the spreadsheet to save itself as the Date(mmm dd). In a certain location example "C:\Packing Slips\" & (mmm yyyy)\ & (mmm dd).

mikerickson
12-19-2007, 09:04 PM
You could put this routine in a normal module and call it from a userfom routine. The On Error code is there to handle the user Canceling when asked if they want to overwrite a file. If you don't want the user to be asked the question, the DislpayAlerts code should be un-commented.
Sub saveDate()
On Error Resume Next
Rem Application.DisplayAlerts = False
ThisWorkbook.SaveAs Filename:= _
"Macintosh HD:Users:merickson:Desktop:" & Format(Date, "mmm_yyyy") & ".xls"
Rem Application.DisplayAlerts = True
On Error GoTo 0
End Sub

Or did you want code that would summon the SaveAs dialog box?

Emoncada
12-19-2007, 09:16 PM
I would like for it to run just once not just when CELL A2 gets a value.
and also can it create the other folders


In a certain location example "C:\Packing Slips\" & (mmm yyyy)\ & (mmm dd).

Create two folders then place the spreadsheet inside the second folder?

mikerickson
12-19-2007, 10:13 PM
I don't understand when you want the routine called. Do you want it called from a userform? Or when A2 is changed? You can put

Call saveDate in either a userform routine or a worksheet change event.

I misread your desired file name, perhaps using this instead will do what you want
Format(Date, "mmm_yyyy & mmm dd")?

I'm a Mac user, I don't know about Windows file handling.

PaSha
12-20-2007, 06:46 AM
I would like for it to run just once not just when CELL A2 gets a value.
and also can it create the other folders



Create two folders then place the spreadsheet inside the second folder?

you can look for ex. if folder are already there if not make two and save in specific named folder ...


If testDir = True Then
' copy statement ... CopyFile ...

Exit Sub

Else
MkDir targetpath1
MkDir targetpath2

'again copy statement

End If

PaSha
12-20-2007, 06:47 AM
I would like for it to run just once not just when CELL A2 gets a value.
and also can it create the other folders



Create two folders then place the spreadsheet inside the second folder?

you can look for ex. if folder are already there if not make two and save in specific named folder ...


If testDir = True Then
' copy statement ... CopyFile ...

Exit Sub

Else
MkDir targetpath1
MkDir targetpath2

'again copy statement

End If





this is a sugestion ...

but try something like this ...