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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

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