Problem with Worksheet_Change

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a "Home" worksheet with a hyperlink to another hidden "Test"
worksheet. The test is timed and will display "STOP" in cell "u1". In the
sheet code module, I have a subroutine to recalculate so the time elapsed is
updated:
Private Sub Worksheet_SelectionChange(ByVal Target as Range)
ActiveSheet.Calculate
End Sub
I want to use "STOP" as the trigger to return to "Home" worksheet which will
display the test results. I've read some of the other postings and tried the
procedures but it doesn't work.
 
If this isn't what you want then try explaining it again...
Worksheets("Home").Select
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins)



"mike_e." <[email protected]>
wrote in message
I have a "Home" worksheet with a hyperlink to another hidden "Test"
worksheet. The test is timed and will display "STOP" in cell "u1". In the
sheet code module, I have a subroutine to recalculate so the time elapsed is
updated:
Private Sub Worksheet_SelectionChange(ByVal Target as Range)
ActiveSheet.Calculate
End Sub
I want to use "STOP" as the trigger to return to "Home" worksheet which will
display the test results. I've read some of the other postings and tried the
procedures but it doesn't work.
 
To clarify, I am trying to create a procedure when cell "u1" = "STOP" then
Sheets("Home").Select.
I tried adding another subroutine in addition to the SelectChange...:

Private Sub Worksheet_SelectChange(ByVal Target As Range)
ActiveSheet.Calculate
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address="$U$1" and Target.Value="STOP" Then
Sheets("Home").Select
End Sub

Can I have a Worksheet_SelectChange routine and another Worksheet_Change
routine in the same module? I also tried Worksheet_Calculate() but still not
getting the procedure to return to the Home sheet.
 
Yes you can, indeed you must,. They both go in the worksheet code module
associated with that sheet.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Thank you for the responses.
I have a better understanding, but I still can't solve my coding problems. I
have 2 worksheets in the workbook. The "Home" sheet has a macro which opens
the "Test" worksheet. The "Test" sheet is where the user inputs numbers and
has a time limit of 5 minutes. There is a function in cell "u1" that will
display "STOP" when cell "t1">=5 minutes. When "u1" = "STOP", this is the
trigger that I want to close "Test" worksheet and open "Home" worksheet which
displays the test results:

If Target.Address = "$U$1" and Target.Value = "STOP" Then
Sheets("Home").Select
End If

I must be missing something because, the procedure is not working. I
appreciate any help.
Mike.
 
The value "STOP" is a calculated value and will not trigger a worksheet_change
event.

Try worksheet_calculate event

Private Sub Worksheet_SelectChange(ByVal Target As Range)
ActiveSheet.Calculate
End Sub

Private Sub Worksheet_Calculate()
If Me.Range("$U$1").Value = "STOP" Then
Sheets("Home").Select
End If
End Sub


Gord Dibben MS Excel MVP
 
Back
Top