Code Problem: Empty Text Box

[Humpa]

Hi,
I am fairly new to VBA, so this might be rather trivial.

I have a Userform with a Text Box to enter a number.
I want to check, if there was a number entered in the Text Box.
If a number was entered, a certain calculation is to be done with that number
If no number was entered, then this Text Box is rated with a certain grade (e.g. 10) and further calculation is to be done with that grade.

I have written the code below:

Dim A As Variant
Dim B As Variant

Private Sub btnCheck_Click()
If txtA.Text = "" Then
Msg = "You have entered no value for A. Do you want to continue?"
Ans = MsgBox(Msg, vbYesNo)
If Ans = vbNo Then Exit Sub
If Ans = vbYes Then txtA.Text = “#N/A”
End If
A = txtA.Text
If A = “#N/A” Then B = 10
If A > 0 And A <= 400 Then B = 0
If A > 400 Then B = (A - 400) * 0.1

The above does not work. If I exchange "#N/A" with a number, such as "0", then everything works fine.
However, this is not what I want. How do I assign an 'empty' Text Box to a variable?

Thanks,

H.

 

[VBA Geek]

hello - do you want B to be 10 if what was entered in the textbox wasn't a number, if A between 0 and 400 B =0, if A > 400 B = your calculation ?

 

[Norie]

Try replacing the Ifs with this.

Dim A As Variant
Dim B As Variant

Private Sub btnCheck_Click()

    If txtA.Text = "" Or Not IsNumeric(txtA.Text) Then
        Msg = "You have entered no value for A. Do you want to continue?"
        Ans = MsgBox(Msg, vbYesNo)
        If Ans = vbNo Then
            Exit Sub
        Else
            txtA.Text = “#N/A”
        End If
    End If

    A = txtA.Text

    Select Case A
        Case "#N/A"
            B = 10
        Case 0 To 400 
            B = 0
        Case A > 400 
            B = (A - 400) * 0.1
    End Select

 

[Humpa]

Hi,
thanks for offering help.

Yes. B = 10 if the Text Box is empty, or, no number was entered.
B = 0 If A between 0 and 400
If A > 400 Then B = my calculation.

 

[Humpa]

Thanks Norie!
This works perfect. I just learnt a lot

Regards, H.

 

[VBA Geek]

for assigning the value to B you can shorten it to

If IsNumeric(txtA.Text) Then
    B = Application.Max(0, txtA.Text - 400) * 0.01
Else
    B = 10
End If

 

[Rick Rothstein]

for assigning the value to B you can shorten it to

If IsNumeric(txtA.Text) Then
    B = Application.Max(0, txtA.Text - 400) * 0.01
Else
    B = 10
End If

Or maybe even to this...

B = Evaluate(Replace("IF(ISNUMBER(@),0.01*MAX(0,@-400),10)", "@", txtA.Text))

 

[VBA Geek]

I haven't tested yours but since the textbox.text property always returns a string, will ISNUMBER return TRUE if you enter a number?

maybe yes, I haven't tried yet..

EDIT:
tested, it works

Or maybe even to this...

B = Evaluate(Replace("IF(ISNUMBER(@),0.01*MAX(0,@-400),10)", "@", txtA.Text))

 

[Rick Rothstein]

I haven't tested yours but since the textbox.text property always returns a string, will ISNUMBER return TRUE if you enter a number?

maybe yes, I haven't tried yet..

EDIT:
tested, it works

It is text in the VBA world, but once placed inside the string argument to Evaluate, numbers will be unquoted so they will look like numbers to the ISNUMBER function.

 

[Humpa]

Thanks to everybody who contributed to solve my Problem:
Norie: I think, there is a small error in your code (I just found out by try and error):

[COLOR=#323333][FONT=Courier]  Select Case A[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]        Case "#N/A"[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]            B = 10[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]        Case 0 To 400 [/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]            B = 0[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]        Case A > 400 [/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]            B = (A - 400) * 0.1[/FONT][/COLOR]
[COLOR=#323333][FONT=Courier]    End Select[/FONT][/COLOR]

The above produces not the correct output for Case A > 400
To make it correct, one has to use
Case 0 To 400
B=0
Case Is > 400
B = ...