Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automated saving - Please Help & Advice
I am using the following code to save a file using a value in a cell.
Please help and provide detailed answer as I am new with VB. This is what I want to achieve: 1) If cell is empty, prompt the user to enter their LoginName 2) Even if the cell is filled, ask the user if that is the correct LoginName - perhaps by showing the LoginName, but I don't know how to do this! 3) save the file using value in a cell - I would like to save it to a specific folder, but again don't know how to. The problems I'm having a 1) I am getting the message and prompt "Have you entered correct LoginName?" twice 2) Something is wrong with this part of the code: ActiveWorkbook.SaveAs Sheets("Jan").Range("Y23").Value & ".xls" 3) When the Excel Prompt comes with "File already exists do you want to replace it" and the user clicks no another prompt says "visual basic error 400" Please help and provide detailed answer as I am new with VB. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim Msg, Style, Title, Help, Ctxt, Response, MyString If Me.Sheets(1).Range("$T$23").Value = "" Then Beep MsgBox "You have not entered your LoginName on the first sheet - Jan!" Let Cancel = True Exit Sub End If Msg = "Have you entered correct LoginName?" Style = vbYesNo Title = "Save Prompt" Response = MsgBox(Msg, Style, Title) If Response = vbYes Then ActiveWorkbook.SaveAs Sheets("Jan").Range("Y23").Value & ".xls" Exit Sub Else Cancel = True End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automated saving - Please Help & Advice
Hi unknown,
The problems I'm having a 1) I am getting the message and prompt "Have you entered correct LoginName?" twice 2) Something is wrong with this part of the code: ActiveWorkbook.SaveAs Sheets("Jan").Range("Y23").Value & ".xls" try ActiveWorkbook.Save FileName:= Range("Y23").Value & ".xls" instead of that. 3) When the Excel Prompt comes with "File already exists do you want to replace it" and the user clicks no another prompt says "visual basic error 400" with the save command you shouldn't have the problems with replacing. Best Markus Please help and provide detailed answer as I am new with VB. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim Msg, Style, Title, Help, Ctxt, Response, MyString If Me.Sheets(1).Range("$T$23").Value = "" Then Beep MsgBox "You have not entered your LoginName on the first sheet - Jan!" Let Cancel = True Exit Sub End If Msg = "Have you entered correct LoginName?" Style = vbYesNo Title = "Save Prompt" Response = MsgBox(Msg, Style, Title) If Response = vbYes Then ActiveWorkbook.SaveAs Sheets("Jan").Range ("Y23").Value & ".xls" Exit Sub Else Cancel = True End If End Sub . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automated saving - Please Help & Advice
This works fine for me
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim Response, MyString Const SaveSheet As String = "Jan" Const SaveCell As String = "T23" If Sheets(SaveSheet).Range(SaveCell).Value = "" Then Beep MsgBox "You have not entered your LoginName" & vbCrLf & _ "on the first sheet (" & SaveSheet & "), in cell " & SaveCell Cancel = True Exit Sub End If If Sheets(SaveSheet).Range(SaveCell).Value < Environ("Username") Then MsgBox "Your LoginName is on the first sheet (" & SaveSheet & ")," & vbCrLf & _ " in cell " & SaveCell & " is invalid, " & _ " it should be '" & Environ("Username") & "'" Cancel = True Exit Sub End If ActiveWorkbook.SaveAs "c:\myFolder\" & Sheets(SaveSheet).Range(SaveCell).Value & ".xls" End Sub -- HTH RP (remove nothere from the email address if mailing direct) "SU" wrote in message ... I am using the following code to save a file using a value in a cell. Please help and provide detailed answer as I am new with VB. This is what I want to achieve: 1) If cell is empty, prompt the user to enter their LoginName 2) Even if the cell is filled, ask the user if that is the correct LoginName - perhaps by showing the LoginName, but I don't know how to do this! 3) save the file using value in a cell - I would like to save it to a specific folder, but again don't know how to. The problems I'm having a 1) I am getting the message and prompt "Have you entered correct LoginName?" twice 2) Something is wrong with this part of the code: ActiveWorkbook.SaveAs Sheets("Jan").Range("Y23").Value & ".xls" 3) When the Excel Prompt comes with "File already exists do you want to replace it" and the user clicks no another prompt says "visual basic error 400" Please help and provide detailed answer as I am new with VB. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim Msg, Style, Title, Help, Ctxt, Response, MyString If Me.Sheets(1).Range("$T$23").Value = "" Then Beep MsgBox "You have not entered your LoginName on the first sheet - Jan!" Let Cancel = True Exit Sub End If Msg = "Have you entered correct LoginName?" Style = vbYesNo Title = "Save Prompt" Response = MsgBox(Msg, Style, Title) If Response = vbYes Then ActiveWorkbook.SaveAs Sheets("Jan").Range("Y23").Value & ".xls" Exit Sub Else Cancel = True End If End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
pls advice | Excel Worksheet Functions | |||
Advice Please | Excel Worksheet Functions | |||
Almost got it !! but need advice | Excel Worksheet Functions | |||
Prompt message and Automated saving! | Excel Programming | |||
RTD Advice | Excel Programming |