Prompt message and Automated saving!

[SU]

I am trying to prompt users with a message (when they save the file) to fill
in a cell with their LoginName.

This LoginName will be used as the filename to save the file automatically.
But I am getting program error message followed by crashing of Excel.

Please help and advice. Also, can I automate the same process to save the
file in a specific folder?

Many thanks in advance.

Here is the code that I am using below to achieve this:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "Have you filled in your LoginName on the first Sheet - (Jan)?
Please do so to save this file as Timesheet2005_YourLoginName "
Style = vbYesNo
Title = "Save Prompt"
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then
ThisWorkbook.SaveAs filename:=Sheets("Jan").Range("Y23")
Exit Sub
Else
Cancel = True
End If
End Sub

 

[Tom Ogilvy]

why not just get the information yourself:


See mr Erlandsen's page
http://www.erlandsendata.no/english/index.php?t=envbaos
or Chris Rae's page http://www.chrisrae.com/vba/routines.html

or use the environment variable:

[demo'd from the immediate window]
sstr = environ("Username")
? sStr
OgilvyTW

 

[SU]

Tom
Tried both sites. Could not find the required information. Please Help.

why not just get the information yourself:


See mr Erlandsen's page
http://www.erlandsendata.no/english/index.php?t=envbaos
or Chris Rae's page http://www.chrisrae.com/vba/routines.html

or use the environment variable:

[demo'd from the immediate window]
sstr = environ("Username")
? sStr
OgilvyTW

--
Regards,
Tom Ogilvy

I am trying to prompt users with a message (when they save the file) to fill
in a cell with their LoginName.

This LoginName will be used as the filename to save the file automatically.
But I am getting program error message followed by crashing of Excel.

Please help and advice. Also, can I automate the same process to save the
file in a specific folder?

Many thanks in advance.

Here is the code that I am using below to achieve this:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "Have you filled in your LoginName on the first Sheet - (Jan)?
Please do so to save this file as Timesheet2005_YourLoginName "
Style = vbYesNo
Title = "Save Prompt"
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then
ThisWorkbook.SaveAs filename:=Sheets("Jan").Range("Y23")
Exit Sub
Else
Cancel = True
End If
End Sub

 

[Steve]

Why prompt the user?
Tom's suggestion to grab it out of the environment means less keystrokes for
the user and no false key stokes. If you need it stored in a cell, you can
do this with code as well. The user will have no option. The information
will be accurate.

Alternatively, if you must prompt the user why make them stop, navigate to a
sheet, write some information then save again:

if isblank(Sheets("Jan").Range("Y23")) then
curUser= inputbox "What is your Login Name?" ' look up inputbox in help
for the rest
Sheets("Jan").Range("Y23")) = curUser
end if

Tom
Tried both sites. Could not find the required information. Please Help.

why not just get the information yourself:


See mr Erlandsen's page
http://www.erlandsendata.no/english/index.php?t=envbaos
or Chris Rae's page http://www.chrisrae.com/vba/routines.html

or use the environment variable:

[demo'd from the immediate window]
sstr = environ("Username")
? sStr
OgilvyTW

--
Regards,
Tom Ogilvy

I am trying to prompt users with a message (when they save the file) to fill
in a cell with their LoginName.

This LoginName will be used as the filename to save the file automatically.
But I am getting program error message followed by crashing of Excel.

Please help and advice. Also, can I automate the same process to save
the
file in a specific folder?

Many thanks in advance.

Here is the code that I am using below to achieve this:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "Have you filled in your LoginName on the first Sheet -
(Jan)?
Please do so to save this file as Timesheet2005_YourLoginName "
Style = vbYesNo
Title = "Save Prompt"
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then
ThisWorkbook.SaveAs filename:=Sheets("Jan").Range("Y23")
Exit Sub
Else
Cancel = True
End If
End Sub