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

"Ed" wrote:

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

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