Automatic Filesave using Loginname

G

Guest

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
 
G

Guest

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???
 
G

Guest

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.
 
G

Guest

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 said:
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 said:
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???
Click to expand...
Click to expand...
 
G

Guest

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 said:
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 said:
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 said:
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???

:

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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA Problem - Please Help 5
Automated saving - Please Help & Advice 2
Prompt message and Automated saving! 3
How to hide password? 1
Resize to last entry in row and copy to sheet 1 3
Iterative consolidation macro can't complete - memory overload? 5
Trying to print specific worksheet from outlook attachment 2
Send attachment via CDO 4

Top