Activate new Saveas sheet

Alphacsulb · Mar 31, 2011

Code:

Private Sub Workbook_Open()
    If ThisWorkbook.Name <> "2 CITY Invoice MASTER 2011 NEW - Copy - Back Up.xls" Then Exit Sub

        'Retrieve file name to use for Save
        fileSaveName = Application.GetSaveAsFilename( _
            fileFilter:="Excel Files (*.xls), *.xls")

        'If user specified file name, perform Save and display msgbox
        If fileSaveName <> False Then
            ActiveWorkbook.SaveAs Filename:=fileSaveName, FileFormat:=xlNormal

            MsgBox "Save as " & fileSaveName
        End If
    Windows(fileSaveName).Activate
    ActiveSheet.Unprotect
End Sub

My code says, if the name of this worksheet is "2 CITY Invoice MASTER 2011 NEW - Copy - Back Up.xls" then prompt to SaveAs.

Then in a message box tell me where I saved the new file.

Then I want to Unprotect the new file that was "Saved As", but I get an error on the Windows(fileSaveName).Activate line.

If I remove this line then it unprotects my original document not the new one. It's probably an easy fix but I can't figure it out.

VoG · Apr 1, 2011

I think that is because fsavename includes the path. Try removing the path like this

Code:

Private Sub Workbook_Open()
    If ThisWorkbook.Name <> "2 CITY Invoice MASTER 2011 NEW - Copy - Back Up.xls" Then Exit Sub

        'Retrieve file name to use for Save
        filesavename = Application.GetSaveAsFilename( _
            fileFilter:="Excel Files (*.xls), *.xls")

        'If user specified file name, perform Save and display msgbox
        If filesavename <> False Then
            ActiveWorkbook.SaveAs Filename:=filesavename, FileFormat:=xlNormal

            MsgBox "Save as " & filesavename
        End If
        i = InStrRev(filesavename, "\")
        fname = Right(filesavename, Len(filesavename) - i)
        MsgBox fname 'remove once working correctly
    Workbooks(fname).Activate
    ActiveSheet.Unprotect
End Sub

Alphacsulb · Apr 1, 2011

That appears to work with a some slight modifications. Yesterday I ended up going a different route which is longer but works.

It was using a formula in a cell to get the file name then using the cell to activate the sheet using the following method: MyFile = Range("J1").Value ' Get File Name
Windows(MyFile).Activate ' Activate New File based on cell value

Thanks for your suggestion, I appreciate learning about different methods to get a job done.

Activate new Saveas sheet

Alphacsulb

Active Member

Excel Facts

VoG

Legend

Alphacsulb

Active Member

Similar threads

Forum statistics

Share this page

Activate new Saveas sheet

Alphacsulb

Active Member

Excel Facts

VoG

Legend

Alphacsulb

Active Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock