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

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

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

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

> 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" <[email protected]> wrote in message
news:[email protected]...
> 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