0

I have the following piece of code which calls a few different subs:

Sub DropDown2_Change()
 'Packtype/Brand-packtype change
  Sheets("planning interface").unprotect
  Application.ScreenUpdating = False
  Call clear_data
  If Sheets("load").range("Q1") = True Then
    packtypeform.Show
  End If
  If Sheets("load").range("Q1") = False Then
    brandpacktypeform.Show
  End If
  Call formatting
  With Sheets("planning interface")
    .Protect
    .EnableSelection = xlUnlockedCells
  End With
  application.screenupdating = true
End Sub

It works great unless I press ctrl+shift+pageup (or pagedown) to switch between sheets I am viewing and return to the sheet with the dropdown.

I get a Run-time error '1004': Unprotect method of worksheet class failed Is there a known reason why switching sheets using ctrl+shift+pageup would break the unprotect code that i have?

Community
  • 1
  • 1
scott
  • 2,235
  • 1
  • 14
  • 18
  • Sometimes Excel wants the sheet be the active sheet in order to do something. Whether that is the reason or not, you should instead look into [protecting from UI only](http://stackoverflow.com/q/125449/11683). – GSerg Oct 19 '12 at 15:02

1 Answers1

0

CTRL+SHIFT+PGUP Are used to select sheets, not tab between them - so as you tab though you are actually selecting multiple sheets.

When you run the code you have multiple sheets selected, this is preventing you from protecting one.

You can use CTRL+PGUP to tab between sheets

SWa
  • 4,343
  • 23
  • 40
  • CTRL+PGUP does not work when the sheet is protected, but I can live with not using CTRL+SHIF+PGUP – scott Oct 19 '12 at 15:05