Protect sheet not working in one macro but in one other

Andresen

New Member
Joined
Nov 10, 2021
Messages
32
Office Version
  1. 365
Platform
  1. Windows
I have been struggling a lot with protecting and unprotecting in a macro.

Right now I have three sheets.
1: Excel sheet with no macro. Called PPP. It is this sheet that has all data that I would like the macro to work with.
2: A bigger macro sheet. With no data in it. Called Macro sheet.
3: A small macro sheet with only the three subs below. Called Protect macro sheet.
In the Macro sheet I have also to variables as Private

I have the three subs below copied from the Protect macro book to the Macro book. I run the macros on the PPP book.

When running those three macros in the Protect macro book it works perfect.
When running those three macros in the Macro book it never works. I get Run-time error '9': Subscript out of range when comming to "ThisWorkbook.Worksheets..." in both UnprotectSheet and ProtectSheet macro.

//Stefan




Sub UnprotectAndProtectBOM()
Call UnprotectSheet("BOM")
Call ProtectSheet("BOM")
End Sub

Sub ProtectSheet(SheetName As String)
ThisWorkbook.Worksheets(SheetName).Protect Password:="purchase", DrawingObjects:=False, Contents:=True, Scenarios:= _
False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowInsertingColumns:=False, AllowInsertingRows _
:=False, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=False, _
AllowDeletingRows:=False, AllowSorting:=False, AllowFiltering:=True, _
AllowUsingPivotTables:=True
End Sub

Sub UnprotectSheet(SheetName As String)
ThisWorkbook.Worksheets(SheetName).Unprotect "purchase"
End Sub


In the Macro book I have those two public Variables
Public VStop As String
Public Inputsheet As String
 

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.
I am a bit confused. "ThisWorkbook" refers to the workbook that contains the code. Are you running it on the workbook that contains the code or are you trying to run it on the ActiveWorkbook.
The error message indicates that the SheetName does not exist.
 
Upvote 0
Solution
I am a bit confused. "ThisWorkbook" refers to the workbook that contains the code. Are you running it on the workbook that contains the code or are you trying to run it on the ActiveWorkbook.
The error message indicates that the SheetName does not exist.
Thanks, you nailed it!
It was ThisWorkbook that was the problem. It is through strange that I could run the macro from one other new excel workbook and it was working. But anyhow. Now it is working from my Macro workbook.

And when it comes to that the sheet is not available. Then it must be in the macro workbook it means.

It is working and my headache are removed.
Thanks again!!!
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

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
Back
Top