Code problem with text box
 

I have a user form with a text box. The user enters a rate and a return on
assets figure is calculated. My problem is that when the rate is blank, if
the user zeroes it out, an overflow error occurs. I think this is due to the
fact that when it's blank a division by zero is performed downstream. I tried
to write some code to deal with this but it doesn't seem to work. Any help
would be appreciated.

Private Sub txtvolsp_Change()
If txtvolsp.Text = " " Then
spvol = 0.00001
Else
spvol = CLng(txtvolsp.Value)
End If
End Sub


--
Paul

Code problem with text box
 

Paul

First, you probably don't want to use the Change event because it fires
before the user is done entering their stuff. If you want to enter 15% and
you type 0.15, the Change event fires like

0 fires . fires 1 fires 5 fires

Use the BeforeUpdate event to check that the value is valid. If it's not,
you can set the Cancel argument to True which will cancel the update. Then
use the AfterUpdate event to do your calculation.

Why your method doesn't work is hard to say without seeing the rest of the
code. Maybe spvol isn't scoped properly.

--
Dick Kusleika
Excel MVP
Daily Dose of Excel
www.dicks-blog.com

Paul wrote:
I have a user form with a text box. The user enters a rate and a
return on assets figure is calculated. My problem is that when the
rate is blank, if the user zeroes it out, an overflow error occurs. I
think this is due to the fact that when it's blank a division by zero
is performed downstream. I tried to write some code to deal with this
but it doesn't seem to work. Any help would be appreciated.

Private Sub txtvolsp_Change()
If txtvolsp.Text = " " Then
spvol = 0.00001
Else
spvol = CLng(txtvolsp.Value)
End If
End Sub