Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Problem with Worksheet_Change
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. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Problem with Worksheet_Change
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." 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. |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Problem with Worksheet_Change
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. "Jim Cone" wrote: 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." 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. |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Problem with Worksheet_Change
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) "mike_e." wrote in message ... 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. "Jim Cone" wrote: 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." 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. |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Problem with Worksheet_Change
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. "Bob Phillips" wrote: 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) "mike_e." wrote in message ... 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. "Jim Cone" wrote: 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." 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. |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Problem with Worksheet_Change
Thanks Gord, your answer solved the problem.
Mike. "Gord Dibben" wrote: 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 On Thu, 12 Jul 2007 11:50:04 -0700, mike_e. wrote: 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. "Bob Phillips" wrote: 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) "mike_e." wrote in message ... 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. "Jim Cone" wrote: 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." 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. |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Problem with Worksheet_Change
Glad to help.
Gord On Thu, 12 Jul 2007 16:50:01 -0700, mike_e. wrote: Thanks Gord, your answer solved the problem. Mike. "Gord Dibben" wrote: 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 On Thu, 12 Jul 2007 11:50:04 -0700, mike_e. wrote: 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. "Bob Phillips" wrote: 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) "mike_e." wrote in message ... 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. "Jim Cone" wrote: 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." 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. |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Problem with Worksheet_Change
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 On Thu, 12 Jul 2007 11:50:04 -0700, mike_e. wrote: 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. "Bob Phillips" wrote: 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) "mike_e." wrote in message ... 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. "Jim Cone" wrote: 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." 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet_Change | Excel Worksheet Functions | |||
Worksheet_Change | Excel Discussion (Misc queries) | |||
Getting around Worksheet_Change() | Excel Worksheet Functions | |||
Problem with function "Worksheet_Change" | Excel Worksheet Functions | |||
Worksheet_change won't run | Excel Discussion (Misc queries) |