Replace and overwrite excel file without prompt

Harshil Mehta

Board Regular
Joined
May 14, 2020
Messages
64
Office Version
  1. 2013
Platform
  1. Windows
Requirements before closing the file:
  • Protect all sheets
  • Hide all sheets except sheet name "Permissions"
  • Replace the file without prompt along with password to open the file (Same file name, Same file path)
The below code does not work as required. Not sure where I am going wrong.


VBA Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim a110w As Variant
Dim path As String

a110w = "123" 'P
path = Application.ThisWorkbook.path


Application.DisplayAlerts = False

      
      ThisWorkbook.Unprotect Password:=a110w 
      
      For Each Worksheet In Sheets
      
          Worksheet.Protect Password:=a110w
      Next

     For Each Worksheet In Sheets
      
         If Not Worksheet.Name = "Permissions" Then Worksheet.Visible = xlVeryHidden
      Next
      
      

ThisWorkbook.SaveAs Filename:=path, FileFormat:=50, Password:=a110w
      
      
      
Application.DisplayAlerts = True

      

End Sub
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Victor Moss

Board Regular
Joined
May 3, 2014
Messages
84
Use Private Sub Workbook_Open() if you'd like to keep sheets hidden

Private Sub Workbook_Open()

For Each ws In Sheets
If ws.Name <> "Permissions" Then
ws.Visible = False
End If
Next
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim ws As Worksheet
Dim a110w As Variant
Dim path As String
Dim fName As String

a110w = "123"
path = Application.ThisWorkbook.path
fName = ThisWorkbook.Name

For Each ws In Sheets
ws.Protect
If ws.Name <> "Permissions" Then
ws.Visible = False
End If
Next

Application.DisplayAlerts = False
ThisWorkbook.SaveAs Filename:=path & fName, FileFormat:=52, Password:=a110w
ActiveWorkbook.Close savechanges:=True
Application.DisplayAlerts = True

End Sub
 

Harshil Mehta

Board Regular
Joined
May 14, 2020
Messages
64
Office Version
  1. 2013
Platform
  1. Windows
Use Private Sub Workbook_Open() if you'd like to keep sheets hidden

Private Sub Workbook_Open()

For Each ws In Sheets
If ws.Name <> "Permissions" Then
ws.Visible = False
End If
Next
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim ws As Worksheet
Dim a110w As Variant
Dim path As String
Dim fName As String

a110w = "123"
path = Application.ThisWorkbook.path
fName = ThisWorkbook.Name

For Each ws In Sheets
ws.Protect
If ws.Name <> "Permissions" Then
ws.Visible = False
End If
Next

Application.DisplayAlerts = False
ThisWorkbook.SaveAs Filename:=path & fName, FileFormat:=52, Password:=a110w
ActiveWorkbook.Close savechanges:=True
Application.DisplayAlerts = True

End Sub
Shows an error saying to change the file extension.
I am already using binary format and want the save as too be in binary format only. Apologies, I did not mention it before.
 

Victor Moss

Board Regular
Joined
May 3, 2014
Messages
84
I had the same error this morning, I saved the file AS xlsb first, then closed, reopened, when closing again it worked fine.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,751
Messages
5,638,145
Members
417,010
Latest member
jnuss03

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top