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 File>save 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

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 File>save 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
>

Joel
Firstly, many thanks for looking into this problem.

K23: Joe Bloggs (Name)
T23: joebloggs (LoginName)

X24 value needs a bit more explanation, here goes:

M23: 2007 (year)
T24: =CONCATENATE("Timesheet",M23,"_",(LOWER(T23)))
U24: =LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)
W24: =CONCATENATE(U24,T24)
and then
X24: =CONCATENATE(W24,".xls")

Once again, many thanks and I look forwrd to hearing from you.




"Joel" wrote:

> 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 File>save 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
> >

I can't repeat the problem. I had to put [Book5.xls] in cell A1 to get
results.

I would start with a new workbook and try exactly what I did. I copied the
subroutines into the Thisworkbook sheet in VBA. Then copied the cells you
had in your last postings including a value in A1. See if it fails. This
only took 5 minutes.

"SU" wrote:

> Joel
> Firstly, many thanks for looking into this problem.
>
> K23: Joe Bloggs (Name)
> T23: joebloggs (LoginName)
>
> X24 value needs a bit more explanation, here goes:
>
> M23: 2007 (year)
> T24: =CONCATENATE("Timesheet",M23,"_",(LOWER(T23)))
> U24: =LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)
> W24: =CONCATENATE(U24,T24)
> and then
> X24: =CONCATENATE(W24,".xls")
>
> Once again, many thanks and I look forwrd to hearing from you.
>
>
>
>
> "Joel" wrote:
>
> > 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 File>save 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
> > >

Joel
Would it be possible to send you a dummy file to check the codes? Apologise
in advance if this breaches any rules.

Many Thanks.

"Joel" wrote:

> I can't repeat the problem. I had to put [Book5.xls] in cell A1 to get
> results.
>
> I would start with a new workbook and try exactly what I did. I copied the
> subroutines into the Thisworkbook sheet in VBA. Then copied the cells you
> had in your last postings including a value in A1. See if it fails. This
> only took 5 minutes.
>
> "SU" wrote:
>
> > Joel
> > Firstly, many thanks for looking into this problem.
> >
> > K23: Joe Bloggs (Name)
> > T23: joebloggs (LoginName)
> >
> > X24 value needs a bit more explanation, here goes:
> >
> > M23: 2007 (year)
> > T24: =CONCATENATE("Timesheet",M23,"_",(LOWER(T23)))
> > U24: =LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)
> > W24: =CONCATENATE(U24,T24)
> > and then
> > X24: =CONCATENATE(W24,".xls")
> >
> > Once again, many thanks and I look forwrd to hearing from you.
> >
> >
> >
> >
> > "Joel" wrote:
> >
> > > 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 File>save 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
> > > >