Protect sheet not working in one macro but in one other

[Andresen]

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

 

[Alex Blakenburg]

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.

 

[Andresen]

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!!!

 

[Alex Blakenburg]

You're welcome. Glad I could help.