Worsksheet behaving as if it is grouped, when it isn't

Question

Worksheet(1) is a protected input sheet, where only certain cells are unlocked. There is a button on this sheet that invokes a userform.
The userform has various textboxes and comboboxes etc for input values, which then places the entered data into a second Worksheet(2).
Worksheet(2) has certain cells locked and unlocked based on the values inputted into the userform, and is then protected.

The problem is, when the above finishes, using the arrow keys in Worksheet(2) selects cell addresses that are unlocked in Worksheet(1), and completely ignores the unlocked cells that it should be selecting!

There are "on change" events for some of the unlocked cells in Worksheet(1), but when those same cells are edited in Worksheet(2), the Worksheet(1) events fire!

It's as if both worksheets have been grouped or merged, with Worksheet(1)'s settings overriding those of Worksheet(2).

When the userform is invoked from Worksheet(2) everything works fine! I need the userform to work from Worksheet(1) too.

I've tested the file on another computer with both Excel 2013 & 2016, and I'm still getting this weird behaviour.

The command for the button is:

userform.show

The initialize code is:

Private Sub UserForm_Initialize()
Dim SYear As Long
Dim LYear As Long
Dim FWeek As Long
SYear = Evaluate("SetYear")
If (Year(Date) Mod 4) Then
    LYear = 52
Else
    LYear = 53
End If
FWeek = Val(SYear & LYear)
Sheets("Data").Range("AX1").Value = Evaluate("RetailWeek")
Sheets("Data").Range("AX2").Value = FWeek
WeekSel.List = Array("Week 1", "Current Week")
Channel.List = Array("Company", "Franchise", "eComm")
Me.Top = Application.Top + 20
Me.Left = Application.Left + 200
Me.WFrom.RowSource = "WeekFrom"
Me.WTo.RowSource = "WeekTo"
End Sub

I suggest you post the code that loads the userform and that populates the cells, or preferably a copy of the file. — Rory
I should also have mentioned that using ctrl-pgup or ctrl-pgdn to toggle through other sheets usually "resets" Worksheet(2), exactly as it would with grouped worksheets, and the unlocked cells become selectable again. — Sofistikat
If you use Application.ScreenUpdating = False, do you have also Application.ScreenUpdating = True at the end? — Egan Wolf
Are you showing this form modelessly, or closing it before using the arrow keys? I suspect it's an SDI bug. — Rory

Sofistikat Sofistikat · Accepted Answer · 2016-01-04T15:24:53

I shifted a whole chunk of code out from the userform module and put it into a normal module. There were also a lot of values being read by the userform from Worksheet (1) and copied to Worksheet(2). I diverted those values to a third worksheet, and then to Worksheet (2) after closing the userform, and it now seems to be working ok.

Fingers, toes and eyes crossed!

Worsksheet behaving as if it is grouped, when it isn't

1 Answers