Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompt message and Automated saving!
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompt message and Automated saving!
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 "SU" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompt message and Automated saving!
Tom
Tried both sites. Could not find the required information. Please Help. "Tom Ogilvy" wrote: 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 "SU" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prompt message and Automated saving!
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 "SU" wrote in message ... Tom Tried both sites. Could not find the required information. Please Help. "Tom Ogilvy" wrote: 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 "SU" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
help with message prompt | New Users to Excel | |||
how to disable save prompt on closing excel file in automated mode | Excel Discussion (Misc queries) | |||
Message prompt in VBA | Excel Programming | |||
Prompt before saving | Excel Programming | |||
Prompt before saving | Excel Programming |