Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sub Exits Unexpectedly
I have written a macro that basically just shows some comparisons between
team members in a userform. The problem I have is a sub in the userform that is called whenever a combobox is changed. If I follow through the code it will just quit in the middle of the sub where i have marked and goes back to the combobox _change sub that called it and finishs it out. Any help would be appreciated. Private Sub Userform_Update() Select Case Score1.Value - Score2.Value Case Is = 0 WScore.Text = "Tied" MOVScore.Text = "Tied" Case Is 0 WScore.Text = User1.Text MOVScore.Value = Format(expression:=Score1.Text / Score2.Text - 1, Format:="Percent") Case Is < 0 WScore.Text = User2.Text MOVScore.Value = Format(expression:=Score2.Text / Score1.Text - 1, Format:="Percent") End Select Select Case WU1.Value - WU2.Value Case Is = 0 WWU.Text = "Tied" MOVWU.Text = "Tied" Case Is 0 WWU.Text = User1.Text MOVWU.Value = Format(expression:=WU1.Text / WU2.Text - 1, Format:="Percent") Case Is < 0 WWU.Text = User2.Text MOVWU.Value = Format(expression:=WU2.Text / WU1.Text - 1, Format:="Percent") End Select Select Case CPU71.Value - CPU72.Value Case Is = 0 WCPU7.Text = "Tied" MOVCPU7.Text = "Tied" Case Is 0 WCPU7.Text = User1.Text MOVCPU7.Value = Format(expression:=CPU71.Text / CPU72.Text - 1, Format:="Percent") Case Is < 0 WCPU7.Text = User2.Text MOVCPU7.Value = Format(expression:=CPU72.Text / CPU71.Text - 1, Format:="Percent") ///HERE IS WHERE IT EXITS/// End Select Select Case CPU501.Value - CPU502.Value Case Is = 0 WCPU50.Text = "Tied" MOVCPU50.Text = "Tied" Case Is 0 WCPU50.Text = User1.Text MOVCPU50.Value = Format(expression:=CPU501.Text / CPU502.Text - 1, Format:="Percent") Case Is < 0 WCPU50.Text = User2.Text MOVCPU50.Value = Format(expression:=CPU502.Text / CPU501.Text - 1, Format:="Percent") End Select Select Case Global1.Value - Global2.Value Case Is = 0 WGlobal.Text = "Tied" MOVGlobal.Text = "Tied" Case Is 0 WGlobal.Text = User2.Text MOVGlobal.Value = Format(expression:=Global2.Value / Global1.Value - 1, Format:="Percent") Case Is < 0 WGlobal.Text = User1.Text MOVGlobal.Value = Format(expression:=Global1.Value / Global2.Value - 1, Format:="Percent") End Select End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sub Exits Unexpectedly
A Sub can't just jump out like that, so the code must be executing, but not
having any effect you can see. For example, if you put a MsgBox "I AM HERE!" just before your End Sub line, I am sure you will get the message box when the sub is called. So there must be something else you are seeing - or not seeing. You are subtracting two numbers and checking if the result is less than zero, zero, or greater than zero. This seems fine but there is one other possible result: if the result is Null. It is possible to get a Null result from a subtraction if either or both values being subtracted are Null; e.g. 5-Null = Null and Null-Null = Null. I would put a breakpoint in the code just before your Select Case statements that do not seem to be functioning, and then when in debug mode check what the values actually are. Try ? CPU501.Value in the immediate pane, as well as ? CPU502.Value and ? CPU501.Value - CPU502.Value. If you are getting Nulls add that as another case to your Select Case statement. Same thing for the other Select Case. -- - K Dales "Walker" wrote: I have written a macro that basically just shows some comparisons between team members in a userform. The problem I have is a sub in the userform that is called whenever a combobox is changed. If I follow through the code it will just quit in the middle of the sub where i have marked and goes back to the combobox _change sub that called it and finishs it out. Any help would be appreciated. Private Sub Userform_Update() Select Case Score1.Value - Score2.Value Case Is = 0 WScore.Text = "Tied" MOVScore.Text = "Tied" Case Is 0 WScore.Text = User1.Text MOVScore.Value = Format(expression:=Score1.Text / Score2.Text - 1, Format:="Percent") Case Is < 0 WScore.Text = User2.Text MOVScore.Value = Format(expression:=Score2.Text / Score1.Text - 1, Format:="Percent") End Select Select Case WU1.Value - WU2.Value Case Is = 0 WWU.Text = "Tied" MOVWU.Text = "Tied" Case Is 0 WWU.Text = User1.Text MOVWU.Value = Format(expression:=WU1.Text / WU2.Text - 1, Format:="Percent") Case Is < 0 WWU.Text = User2.Text MOVWU.Value = Format(expression:=WU2.Text / WU1.Text - 1, Format:="Percent") End Select Select Case CPU71.Value - CPU72.Value Case Is = 0 WCPU7.Text = "Tied" MOVCPU7.Text = "Tied" Case Is 0 WCPU7.Text = User1.Text MOVCPU7.Value = Format(expression:=CPU71.Text / CPU72.Text - 1, Format:="Percent") Case Is < 0 WCPU7.Text = User2.Text MOVCPU7.Value = Format(expression:=CPU72.Text / CPU71.Text - 1, Format:="Percent") ///HERE IS WHERE IT EXITS/// End Select Select Case CPU501.Value - CPU502.Value Case Is = 0 WCPU50.Text = "Tied" MOVCPU50.Text = "Tied" Case Is 0 WCPU50.Text = User1.Text MOVCPU50.Value = Format(expression:=CPU501.Text / CPU502.Text - 1, Format:="Percent") Case Is < 0 WCPU50.Text = User2.Text MOVCPU50.Value = Format(expression:=CPU502.Text / CPU501.Text - 1, Format:="Percent") End Select Select Case Global1.Value - Global2.Value Case Is = 0 WGlobal.Text = "Tied" MOVGlobal.Text = "Tied" Case Is 0 WGlobal.Text = User2.Text MOVGlobal.Value = Format(expression:=Global2.Value / Global1.Value - 1, Format:="Percent") Case Is < 0 WGlobal.Text = User1.Text MOVGlobal.Value = Format(expression:=Global1.Value / Global2.Value - 1, Format:="Percent") End Select End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sub Exits Unexpectedly
Okay I changed all my .text to .value and that fixed some of them and I put a
message box after each select Case to let me know it finished. I ran through all of the combobox options and found that whenever it encounters a 0 it jumps out. the text box has 0 in it and when i jump through the code it shows Variable = "0" which shouldn't be interpreted as a null. I put an if x = 0 then x = 0 statement and the if is triggered but the sub just quits. When I step through it jumps out right after the calculation in the previous select case; I don't even get the msgbox that the previous one finished but the calculation is on the userform. I can't figure out why or even how the sub jumps out. "K Dales" wrote: A Sub can't just jump out like that, so the code must be executing, but not having any effect you can see. For example, if you put a MsgBox "I AM HERE!" just before your End Sub line, I am sure you will get the message box when the sub is called. So there must be something else you are seeing - or not seeing. You are subtracting two numbers and checking if the result is less than zero, zero, or greater than zero. This seems fine but there is one other possible result: if the result is Null. It is possible to get a Null result from a subtraction if either or both values being subtracted are Null; e.g. 5-Null = Null and Null-Null = Null. I would put a breakpoint in the code just before your Select Case statements that do not seem to be functioning, and then when in debug mode check what the values actually are. Try ? CPU501.Value in the immediate pane, as well as ? CPU502.Value and ? CPU501.Value - CPU502.Value. If you are getting Nulls add that as another case to your Select Case statement. Same thing for the other Select Case. -- - K Dales "Walker" wrote: I have written a macro that basically just shows some comparisons between team members in a userform. The problem I have is a sub in the userform that is called whenever a combobox is changed. If I follow through the code it will just quit in the middle of the sub where i have marked and goes back to the combobox _change sub that called it and finishs it out. Any help would be appreciated. Private Sub Userform_Update() Select Case Score1.Value - Score2.Value Case Is = 0 WScore.Text = "Tied" MOVScore.Text = "Tied" Case Is 0 WScore.Text = User1.Text MOVScore.Value = Format(expression:=Score1.Text / Score2.Text - 1, Format:="Percent") Case Is < 0 WScore.Text = User2.Text MOVScore.Value = Format(expression:=Score2.Text / Score1.Text - 1, Format:="Percent") End Select Select Case WU1.Value - WU2.Value Case Is = 0 WWU.Text = "Tied" MOVWU.Text = "Tied" Case Is 0 WWU.Text = User1.Text MOVWU.Value = Format(expression:=WU1.Text / WU2.Text - 1, Format:="Percent") Case Is < 0 WWU.Text = User2.Text MOVWU.Value = Format(expression:=WU2.Text / WU1.Text - 1, Format:="Percent") End Select Select Case CPU71.Value - CPU72.Value Case Is = 0 WCPU7.Text = "Tied" MOVCPU7.Text = "Tied" Case Is 0 WCPU7.Text = User1.Text MOVCPU7.Value = Format(expression:=CPU71.Text / CPU72.Text - 1, Format:="Percent") Case Is < 0 WCPU7.Text = User2.Text MOVCPU7.Value = Format(expression:=CPU72.Text / CPU71.Text - 1, Format:="Percent") ///HERE IS WHERE IT EXITS/// End Select Select Case CPU501.Value - CPU502.Value Case Is = 0 WCPU50.Text = "Tied" MOVCPU50.Text = "Tied" Case Is 0 WCPU50.Text = User1.Text MOVCPU50.Value = Format(expression:=CPU501.Text / CPU502.Text - 1, Format:="Percent") Case Is < 0 WCPU50.Text = User2.Text MOVCPU50.Value = Format(expression:=CPU502.Text / CPU501.Text - 1, Format:="Percent") End Select Select Case Global1.Value - Global2.Value Case Is = 0 WGlobal.Text = "Tied" MOVGlobal.Text = "Tied" Case Is 0 WGlobal.Text = User2.Text MOVGlobal.Value = Format(expression:=Global2.Value / Global1.Value - 1, Format:="Percent") Case Is < 0 WGlobal.Text = User1.Text MOVGlobal.Value = Format(expression:=Global1.Value / Global2.Value - 1, Format:="Percent") End Select End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sub Exits Unexpectedly
I figured it out. Once I changed my .text to .value I was jumping out because
of div/0, so I put a statement to omit the calc if either value = 0 and it works. I thought that I would get an error though. I only use an On errror Resume Next statement once but have On Error Goto 0 two lines down. I still don't see how the Sub just quits but thank you very much for the help. "Walker" wrote: Okay I changed all my .text to .value and that fixed some of them and I put a message box after each select Case to let me know it finished. I ran through all of the combobox options and found that whenever it encounters a 0 it jumps out. the text box has 0 in it and when i jump through the code it shows Variable = "0" which shouldn't be interpreted as a null. I put an if x = 0 then x = 0 statement and the if is triggered but the sub just quits. When I step through it jumps out right after the calculation in the previous select case; I don't even get the msgbox that the previous one finished but the calculation is on the userform. I can't figure out why or even how the sub jumps out. "K Dales" wrote: A Sub can't just jump out like that, so the code must be executing, but not having any effect you can see. For example, if you put a MsgBox "I AM HERE!" just before your End Sub line, I am sure you will get the message box when the sub is called. So there must be something else you are seeing - or not seeing. You are subtracting two numbers and checking if the result is less than zero, zero, or greater than zero. This seems fine but there is one other possible result: if the result is Null. It is possible to get a Null result from a subtraction if either or both values being subtracted are Null; e.g. 5-Null = Null and Null-Null = Null. I would put a breakpoint in the code just before your Select Case statements that do not seem to be functioning, and then when in debug mode check what the values actually are. Try ? CPU501.Value in the immediate pane, as well as ? CPU502.Value and ? CPU501.Value - CPU502.Value. If you are getting Nulls add that as another case to your Select Case statement. Same thing for the other Select Case. -- - K Dales |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sub Exits Unexpectedly
I thought that I would get an error though. I only use an On errror
Resume Next statement once but have On Error Goto 0 two lines down. I still don't see how the Sub just quits but thank you very much for the help. Do you have "break on all errors" set in the VB Editor ? Tim -- Tim Williams Palo Alto, CA "Walker" wrote in message ... I figured it out. Once I changed my .text to .value I was jumping out because of div/0, so I put a statement to omit the calc if either value = 0 and it works. I thought that I would get an error though. I only use an On errror Resume Next statement once but have On Error Goto 0 two lines down. I still don't see how the Sub just quits but thank you very much for the help. "Walker" wrote: Okay I changed all my .text to .value and that fixed some of them and I put a message box after each select Case to let me know it finished. I ran through all of the combobox options and found that whenever it encounters a 0 it jumps out. the text box has 0 in it and when i jump through the code it shows Variable = "0" which shouldn't be interpreted as a null. I put an if x = 0 then x = 0 statement and the if is triggered but the sub just quits. When I step through it jumps out right after the calculation in the previous select case; I don't even get the msgbox that the previous one finished but the calculation is on the userform. I can't figure out why or even how the sub jumps out. "K Dales" wrote: A Sub can't just jump out like that, so the code must be executing, but not having any effect you can see. For example, if you put a MsgBox "I AM HERE!" just before your End Sub line, I am sure you will get the message box when the sub is called. So there must be something else you are seeing - or not seeing. You are subtracting two numbers and checking if the result is less than zero, zero, or greater than zero. This seems fine but there is one other possible result: if the result is Null. It is possible to get a Null result from a subtraction if either or both values being subtracted are Null; e.g. 5-Null = Null and Null-Null = Null. I would put a breakpoint in the code just before your Select Case statements that do not seem to be functioning, and then when in debug mode check what the values actually are. Try ? CPU501.Value in the immediate pane, as well as ? CPU502.Value and ? CPU501.Value - CPU502.Value. If you are getting Nulls add that as another case to your Select Case statement. Same thing for the other Select Case. -- - K Dales |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Prompt msg if the worksheet already exits | New Users to Excel | |||
Find if value exits in array | Excel Worksheet Functions | |||
excel exits unexpectedly or hangs the second time I open workbook | Excel Discussion (Misc queries) | |||
way to run sub after user exits textbox | Excel Programming | |||
graceful exits | Excel Programming |