Protect Unprotect Sheet and Run-time error '9'

[Andresen]

My macro did work yesterday but did not work today.
Can anyone find the problem?

I get Run-time error '9': Subscript out of range when comming to "ThisWorkbook.Worksheets..." in both UnprotectSheet and ProtectSheet macro.

In my first macro I have

Call UnprotectSheet("BOM")
Call ProtectSheet("BOM")

Sub UnprotectSheet(SheetName As String)
ThisWorkbook.Worksheets(SheetName).Unprotect "purchase"
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

 

[RoryA]

You clearly do not have a sheet called BOM in the workbook with the code.

 

[Andresen]

You clearly do not have a sheet called BOM in the workbook with the code.

That is the strange thing.
Yes I have.

To make it even more strange. To try to make a show case.
* I copied the BOM sheet into a new sheet.
* I put over the code as below.
* In the new book it worked. I tried the macro from the new book into my old sheet. The macro started to work in my old sheet when using the macro from the new sheet.
* I closed down everything.
* Opened the new file and the original file. Tried the macro from the new file on the original files BOM sheet. It did not work.
* I removed the BOM sheet in the new file. And again copied over the BOM sheet from the original file to the new file. The macro did work on the BOM sheet in the new file.
* I did again test on the BOM sheet in the original file. And it did not work.

* I wrote the text above in this Trail
* I did again run the macro from the new file in the original file. (I changed so I had one other sheet active). And it worked. I made the BOM sheet active and it worked.
It is very unstable. What shall I look for?
//S



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

 

[Andresen]

I have tested little more and found this.

That is the strange thing.
Yes I have.

To make it even more strange.
* I tried to make a show case
* I copied over the macro text
* Made a test macro UnprotectAndProtectBOM
* I copied the BOM sheet into a new sheet.
* In the new book it worked. I tried the macro from the new book into my old sheet. The macro started to work in my old sheet when using the macro from the new sheet.
* I copied and replaced the macros into my old sheets macro modules. The UnprotectAndProtectBOM macro was also added.
* I run the macro from the old sheets macro on the old sheet. Did not work
* I run the macro from the new sheets macro on the old sheet. Did work.
* I run the macro from the old sheets macro on the old sheet. Did not work.

I have two public variables in the old macro module. You can see them below. I cannot see it is affecting.

What shall I look for?
//S



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 old macro modules I have two public Variables
Public VStop As String
Public Inputsheet As String