ActiveWorkbook.Protect issues/questions

Afro_Cookie

New Member
Joined
Mar 17, 2020
Messages
36
Office Version
  1. 2013
Platform
  1. Windows
Part of my Macro is protecting my workbook. If I run the same macro, with the workbook protected, it will unprotect my workbook even though there is nothing in the code telling it to do so.

Is this a common thing with protecting workbooks in VBA?

Below is a sampling of the my code.
VBA Code:
    Worksheets("Dashboard").Visible = False
       
    ActiveWorkbook.Protect "password"

    ActiveWorkbook.Save
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

VBE313

Well-known Member
Joined
Mar 22, 2019
Messages
678
Office Version
  1. 365
Platform
  1. Windows
is there a worksheet event that is unprotecting it?
 

Afro_Cookie

New Member
Joined
Mar 17, 2020
Messages
36
Office Version
  1. 2013
Platform
  1. Windows
Not in this code there isn't. I have a separate macro that unprotects and unhides a bunch of stuff, but they don't interact with each other.

Complete code
VBA Code:
Sub Sort()
' Sort based on date, then inspector, start time of inspection
On Error Resume Next

    ActiveWorkbook.Worksheets("Production 2020").ListObjects("test").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Production 2020").ListObjects("test").Sort.SortFields.Add Key:=Range("test[DATE]"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Production 2020").ListObjects("test").Sort.SortFields.Add Key:=Range("test[SHIFT]"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Production 2020").ListObjects("test").Sort.SortFields.Add Key:=Range("test[QC/Insp.]"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    
    
    With ActiveWorkbook.Worksheets("Production 2020").ListObjects("test").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    Call F0rmat 'Format
           
    Columns("P:AA").EntireColumn.Hidden = True
       
    Worksheets("Dashboard").Visible = False
       
    ActiveWorkbook.Protect "password"
            
    Call Last_Empty 'Empty_cell
    ActiveWorkbook.Save
    
End Sub
 

VBE313

Well-known Member
Joined
Mar 22, 2019
Messages
678
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

check the worksheet module that you are working on, are there any codes in there?
 

Afro_Cookie

New Member
Joined
Mar 17, 2020
Messages
36
Office Version
  1. 2013
Platform
  1. Windows
Only modules that I created. There is no code in any of the sheets or ThisWorkbook.

It doesn't affect functionality, but it removes the purpose of my protecting the workbook.

check the worksheet module that you are working on, are there any codes in there?
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

It appears to be a kind of bug in Excel 2013, but it can be circumvented.
Replace this line
ActiveWorkbook.Protect "password"
with these lines
VBA Code:
With ActiveWorkbook
    If .ProtectStructure Then
        ' do nothing
    Else
        .Protect "Your_Password"
        .Save
    End If
End With
 

Afro_Cookie

New Member
Joined
Mar 17, 2020
Messages
36
Office Version
  1. 2013
Platform
  1. Windows
Perfect! Thanks for the help.

It appears to be a kind of bug in Excel 2013, but it can be circumvented.
Replace this line
ActiveWorkbook.Protect "password"
with these lines
VBA Code:
With ActiveWorkbook
    If .ProtectStructure Then
        ' do nothing
    Else
        .Protect "Your_Password"
        .Save
    End If
End With
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
You're welcome and thanks for letting me know.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,129
Messages
5,599,903
Members
414,345
Latest member
Jonathan43

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