Solved: What's wrong with my code?

[Sir Babydum GBE]

Hi,

I have the following code (mostly supplied by the good members of this forum) in a worksheet called "Entry":

[VBA] Private Sub Worksheet_Activate()
Range("B5").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
End Sub
Private Sub Worksheet_Calculate()
Dim cell As Range
On Error GoTo ws_exit:
Application.EnableEvents = False
For Each cell In Range("D1:D" & Me.Cells(Me.Rows.Count, "D").End(xlUp).Row)
With cell
Select Case .Value
Case "Mileage": .Offset(0, 1).NumberFormat = "General"
Case "Overtime (single time equivalent)": .Offset(0, 1).NumberFormat = "#,##0.00_ ;-#,##0.00 "
Case Else: .Offset(0, 1).NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
End Select
End With
Next cell

ws_exit:
Application.EnableEvents = True
End Sub
[/VBA] And in another sheet called "Control Panel" I have the following:
[VBA]Private Sub Worksheet_Calculate()
Application.ScreenUpdating = False
Application.EnableEvents = False

Application.Goto Reference:="CategoryListSort"
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Application.Goto Reference:="ProjectListSort"
Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Application.Goto Reference:="PeopleListSort"
Selection.Sort Key1:=Range("C1"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Range("B5").Select
Selection.End(xlDown).Select
Application.EnableEvents = True
End Sub[/VBA]

When I make a change in "Control Panel", the code works fine. When I make a change in "Entry", the sheet deactivates and "Control Panel", mysteriously, reactivates.

Before I put the code in the Data Entry sheet, everything worked fine.

[Edit - In fact, if I change anything on any of the other sheets, it jumps back to Control Panel]

What am I doing wrong?

Thanks

[Bob Phillips]

Haven't replicated the error, but maybe you are getting an error in one sheet and jumping into the other, so you could clear the error handler

[vba]
Private Sub Worksheet_Calculate()
Dim cell As Range
On Error GoTo ws_exit:
Application.EnableEvents = False
For Each cell In Range("D1" & Me.Cells(Me.Rows.Count, "D").End(xlUp).Row)
With cell
Select Case .Value
Case "Mileage": .Offset(0, 1).NumberFormat = "General"
Case "Overtime (single time equivalent)": .Offset(0, 1).NumberFormat = "#,##0.00_ ;-#,##0.00 "
Case Else: .Offset(0, 1).NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
End Select
End With
Next cell

ws_exit:
Application.EnableEvents = True
On Error GoTo 0
End Sub
[/vba]

[Sir Babydum GBE]

Thanks Mr X,

Nope, not working - I'm attaching a dummy copy in the hopes that you might understand the problem.

Thanks again.

[XLGibbs]

BD,

You have events linked to a calculate event..it would seem that once Entry calculates, the application would as well....thus activating the Control Panel Sheet.

Are you sure you want the second entry to be done on a calculate event instead of a change event?

I am taking a look now to see if I can duplicate the issue.

[XLGibbs]

BD, I see exactly what you are talking about. What happens is the workbook is calculating each time you change a cell, thus enabling the worksheet calculate event on Control Panel and the subsequent Goto Reference...

From what I gather, you want to enter data in Entry, and then have control panel sort and go once you are done...

I am kind of buried at the moment, but if you clarify what you actually want to happen, I can work on it...

But having the events tied to calculation is why it keeps getting triggered.

[Sir Babydum GBE]

XLGibbs

You've already sorted it! I changed the Calculate event to Change event on the Control Panel sheet - and all is well.

Actually, the purpose of the code in Entry is to change the number formatting depending on the data entered. The purpose of the code in Control Panel is to auto sort any additions to my dynamic ranges so that they appear alphabetically in the dropdown lists.

Thanks again both.

BD

[XLGibbs]

Cool!