Automated Saving of File

[SU]

I am trying to prompt users with a message (when they click/use SAVE) to fill
in a cell with their LoginName.

This LoginName will be used as the filename to save the file automatically
in a SPECIFIC FOLDER.

This is what I'd like to achieve:

1. I have a Spreadsheet with 12 worksheets, January to December

2. The user enters his/her LoginName (JOHNDOE) in cell A1 in worksheet January

3. When user click/use SAVE; they will be asked to fill the cell A1 in
worksheet January if they have left this cell empty by mistake

4. When user click/use SAVE; they will get a prompt message asking them to
confirm whether their LoginName (johndoe) in cell A1 is correct

5. If they choose YES; the file will be saved in folder C:\timesheet\ as
johndoe.xls

6. The user will receive another prompt to let them know that the file was
saved as C:\timesheet\johndoe.xls

7. If they choose NO; sub ends and the user repeats the process with correct
LoginName

8. When user click/use SAVE AS (instead of using SAVE); they will be given
LoginName (johndoe) in the FILE NAME section of the SAVE AS prompt screen.

I have been working on this over a month and have tried everything I know.
I have also asked for help but owing to my limited knowledge - I'm back!

Please help and advice. Please be as detailed as you deem necessary - I'm a
novice.

Many thanks in advance.

 

[Ed]

With your master timesheet file open, press ALT+F11. This opens the Visual
Basic Editor (VBE). You should see an Explorer-like tree on the left side.
Find the name of the master timesheet file. If there are no icons for each
sheet and ThisWorkbook below it, then double-click the workbook icon.

Double-click the ThisWorkbook icon. The left side upper rectangle will
probably show General; if so, click the drop-down arrow to select Workbook.
It will probably change the right side upper rectangle to Open, and put
Private Sub Workbook_Open()

End Sub
in the VBE window. Use the drop-down arrow on the right rectangle to select
BeforeSave. A new macro header should appear as
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

End Sub

Copy the following code and paste it in the space between "Private Sub" and
"End Sub".

'Create string variable for file name
Dim strFName As String
'Set file path in string
strFName = "C:\timesheet\"
' Check if A1 is empty
If Worksheets("January").Range("A1") <> "" Then
'If not, then verify name
If MsgBox("Is your name in Cell A1 of January?", vbYesNo) = vbYes Then
'If name is correct, add to file name string
strFName = strFName & Range("A1") & ".xls"
' Save file
Me.SaveAs strFName
Else
'If name is not correct, prompt to change it
MsgBox "Please type your name in Cell A1 of January."
'Cancel save
Cancel = True
End If
Else
'Prompt for name
MsgBox "Please type your name into Cell A1 of January."
' Cancel save
Cancel = True
End If

HTH
Ed

 

[SU]

Ed
Many thanks for your detailed answer.

I get these problems:

1. When Saving; I get the message "Is your name in Cell A1 of January?" -
twice. After pressng "YES" the first time

2. If I press "NO" second time; the file still gets saved

3. When I receive the prompt, to say that "File already exists, do you wanna
replace it" and I choose "NO" - I get "Runtime error 1004" method saveas
failed and if I choose debug option this part of the code "Me.SaveAs
strFName" id highlighted in yellow.

Would appreciate your help again. many thanks.

 

[Ed]

Sorry about that!! 8>{
I forgot that the SaveAs method in VBA would re-trigger the BeforeSave event
in the workbook. In the macro, find
' Save file
Me.SaveAs strFName
and replace it with
' Save file
Application.DisplayAlerts = False
Application.EnableEvents = False
Me.SaveAs strFName
Application.EnableEvents = True
Application.DisplayAlerts = True

Another problem is going to be trying to save this template with a blank
space in A1! So put this after the End Sub of the first macro.
Sub SaveTemplate()
Dim strName As String
strName = InputBox("Name that file!")
strName = Me.Path & "\" & strName
Application.EnableEvents = False
Me.SaveAs strName
Application.EnableEvents = True
End Sub

Application.EnableEvents allows you to decide whether Excel responds to the
Workbook events listed in the VBE window upper right rectangle. If you must
turn it off (= False), it must be turned back on (= True), or very strange
things may happen! (You can ask me how I know this, but I'd be too
embarassed to tell you!)

Hope this gets you going.
Ed

 

[Ed]

Okay - after conferring with the experts (Tom Ogilvy, MVP), here's a new
complete macro the also supresses the SaveAs dialog box.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)

Dim wb As Workbook
Set wb = ActiveWorkbook

'Cancel Save event
Cancel = True

'Create string variable for file name
Dim strFName As String
'Set file path in string
'strFName = "C:\timesheet\"
strFName = wb.Path & "\"
' Check if A1 is empty
If wb.Worksheets("January").Range("A1") <> "" Then
'If not, then verify name
If MsgBox("Is your name in Cell A1 of January?", vbYesNo) = vbYes Then
'If name is correct, add to file name string
strFName = strFName & Range("A1") & ".xls"
' Save file
Application.DisplayAlerts = False
Application.EnableEvents = False
wb.SaveAs strFName
Application.EnableEvents = True
Application.DisplayAlerts = True
Else
'If name is not correct, prompt to change it
MsgBox "Please type your name in Cell A1 of January."
End If
Else
'Prompt for name
MsgBox "Please type your name into Cell A1 of January."
End If

End Sub

 

[SU]

Ed
That's excellent, works perfect. Many many thanks for the solution. Very
much obliged.

 

[Curt]

trying to save a file think what I read is more than I need?
useing
Answer = MsgBox(" Have you made a backup of this data?", vbYesNo)
If Answer = vbNo Then
FileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Text Files (*.txt),*.txt")
If FileSaveName <> False Then
MsgBox "Save as" & FileSaveName
End If
Range("e3")(DataEntry).Select
End If
Answer = MsgBox(" Confirm backup of this data. Click Yes", vbYesNo)
If Answer = vbYes Then

Missing something as it does not save when named in save as box?
want user to give Active worlsheet a name then save befor other action
clearing data. Any help appreciated Thanks