Autofill SaveAs File Name box using read only workbook

newbieX · #1 05-08-2015, 03:25 PM

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

excelledsoftware · #2 05-20-2015, 11:16 PM

I think the only issue is the 3rd line of code. The workbook name does not include the file extension at least not in my experience. Change it to

Code:

 If ThisWorkbook.Name = "Test" Then

and see if that fixes it.

Thanks

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
VBS saveas API behaviour is different from Menu saveas	Ahmed AbuZekry	Word VBA	2	03-23-2015 02:14 AM
SaveAs docm file from template	ptmuldoon	Word VBA	7	03-09-2015 05:38 PM
I need an index file containing the names of all the worksheets in a workbook.	retiredstuart	Excel	3	09-12-2014 06:26 AM
the saveas macro in excel 2010 is not transferring the macros to the new workbook	missmr28	Excel Programming	3	06-20-2012 05:30 AM
Read text Report file with VBA and write parsed fields to Excel workbook	tpcervelo	Excel Programming	1	01-05-2012 10:14 PM