Excel Quirk - Tab stops working when protect another sheet

1
votes

I just came across a quirk and have no clue why this is happening - I'm using Excel 2010 and have the following situation:

  1. I have 2 sheets - An "input" sheet and a "save" sheet
  2. Save sheet is protected; input sheet is not
  3. I have a VBA Macro that un-protects the save sheet, copies the data across between the 2, then re-protects it
  4. When this macro runs, the input sheet (which was never protected) now has the tab key not working (cannot tab between cells).

To replicate this situation easily, I created a new blank workbook and did the following:

  1. Password protected Sheet2 with the password "Test"

  2. Put in the following simplistic VBA sub:

    Sub WeirdQuirk()
    Sheets("Sheet2").Unprotect Password:="Test"
    Sheets("Sheet1").Range("A1").Copy Sheets("Sheet2").Range("B1")
    Sheets("Sheet2").Protect Password:="Test"
End Sub

When I'm in Sheet1 and run this, afterwards the Tab key is no longer working in the sheet.

Any thoughts on why this is happening / how to fix it?

1
vbaexcel

1 Answers

2
votes

FYI, I did come up with a fix, but it's not what should be done - Hopefully someone else has a better solution that I'll accept, but I do want to post this in case anyone else gets stuck in this situation...

What I ended up putting in to re-enable the tab key was adding in the line of code:

Application.TransitionNavigKeys = False

OR:

Application.TransitionNavigKeys = True

if you want that feature enabled.

Hope it helps other people.