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" wrote:

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" wrote in message
...
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.