What value do you have in K23, T23, X24. i cannot repeat you problem. The
program doesn't seem to be saving the file with the user name. It is saving
the file under the same name that it was open with. If I call the file
Temp.xls it save the file as temp.xls???

"SU" wrote:

I have a spreadsheet where the users input their loginname and the file is
saved automatically using the loginname. Everything seems to work fine except
that when I save the file through Filesave command both prompts (see below)
keeps on appearing in a loop.

Please suggest a solution. Many thanks in advance.


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'
Dim Response, MyString, Msg, Style, Title, Help, Ctxt
Dim MyFileName As String
Const SaveSheet As String = "Jan"
Const SaveCell As String = "X24"
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 = Sheets("Jan").Range("X24").Value
' Check if T23 - LoginName is empty
If wb.Worksheets("Jan").Range("T23") < "" Then
'If not, then verify LoginName
If MsgBox("Your LoginName is (" & Sheets("Jan").Range("T23").Value &
")," & _
vbCrLf & " and Your Name is (" & Sheets("Jan").Range("K23").Value & "),
" & _
vbCrLf & " Is this Correct?", vbYesNo, "Save Prompt") = vbYes Then
'If name is correct, Save file
Application.DisplayAlerts = False
Application.EnableEvents = False
wb.SaveAs strFName
Application.EnableEvents = True
Application.DisplayAlerts = True
Else
'If LoginName is NOT correct, prompt to change it
MsgBox "Please enter CORRECT LoginName in Cell T23 of Sheet Jan"
End If
Else
'Prompt for LoginName
MsgBox "You have not entered your LoginName" & vbCrLf & _
"on the first sheet (" & SaveSheet & "), in cell (T23)"
End If
End Sub
'
'
Sub Savefile()
'
Dim Response, MyString, Msg, Style, Title, Help, Ctxt
Dim MyFileName As String
Const SaveSheet As String = "Jan"
Const SaveCell As String = "X24"
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 = Sheets("Jan").Range("X24").Value
' Check if T23 - LoginName is empty
If wb.Worksheets("Jan").Range("T23") < "" Then
'If not, then verify LoginName
If MsgBox("Your LoginName is (" & Sheets("Jan").Range("T23").Value &
")," & _
vbCrLf & " and Your Name is (" & Sheets("Jan").Range("K23").Value & "),
" & _
vbCrLf & " Is this Correct?", vbYesNo, "Save Prompt") = vbYes Then
'If name is correct, Save file
Application.DisplayAlerts = False
Application.EnableEvents = False
wb.SaveAs strFName
Application.EnableEvents = True
Application.DisplayAlerts = True
Else
'If LoginName is NOT correct, prompt to change it
MsgBox "Please enter CORRECT LoginName in Cell T23 of Sheet Jan"
End If
Else
'Prompt for LoginName
MsgBox "You have not entered your LoginName" & vbCrLf & _
"on the first sheet (" & SaveSheet & "), in cell (T23)"
End If
End Sub
'
'
Private Sub Workbook_Open()

End Sub