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 are:

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

Hi unknown,
>
>The problems I'm having are:
>
>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
>.
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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 are:
>
> 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