Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim saveIt As Boolean
If ThisWorkbook.Name = "Test.xlsm" Then
Sheets("Sheet1").Select
If Not IsEmpty(A1.Value) Then
MsgBox "This workbook is 'read only' Please rename this workbook."
strName = "Please enter a new file name."
ThisPAth = Application.ActiveWorkbook.Path
ThisFile = Range("B1").Value
ActiveWorkbook.SaveAs Filename:=ThisPath & ThisFile & ".xlsm", FileFormat:=52, CreateBackup:=False
Else
MsgBox "Cancelled."
End If
End If
End Sub
I have a password protected workbook (Test.xlsm") that is strictly for data entry. When the user opens the workbook as read only, enters the data, and then exits the workbook/template, I want the SaveAs dialog box that automatically popups have the contents of A1 of Sheet1 to be the "Suggested" file name that is autofilled in the SaveAs box.
I thought if I used the BeforeSave function that I could declare this path/filename but alas, I can not. The autofill box displays "Copy of Test.xlsm". I do not even think it sees the above code.
How can I accomplish autofilling this box with the desired name. Thanks.
Excel 2010