Sir Babydum GBE
02-24-2006, 07:31 AM
Hi,
I have the following code (mostly supplied by the good members of this forum) in a worksheet called "Entry":
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
And in another sheet called "Control Panel" I have the following:
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
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
I have the following code (mostly supplied by the good members of this forum) in a worksheet called "Entry":
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
And in another sheet called "Control Panel" I have the following:
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
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