Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Code Problem: Empty Text Box

  1. #1
    New Member
    Join Date
    Dec 2014
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Code Problem: Empty Text Box

    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.


  2. #2
    MrExcel MVP VBA Geek's Avatar
    Join Date
    Dec 2013
    Location
    Kepler 438B
    Posts
    2,855
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Code Problem: Empty Text Box

    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 ?

  3. #3
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    74,609
    Post Thanks / Like
    Mentioned
    43 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Code Problem: Empty Text Box

    Try replacing the Ifs with this.
    Code:
    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
    Last edited by Norie; Dec 16th, 2014 at 06:15 AM.
    If posting code please use code tags.

  4. #4
    New Member
    Join Date
    Dec 2014
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code Problem: Empty Text Box

    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.

  5. #5
    New Member
    Join Date
    Dec 2014
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code Problem: Empty Text Box

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

    Regards, H.

  6. #6
    MrExcel MVP VBA Geek's Avatar
    Join Date
    Dec 2013
    Location
    Kepler 438B
    Posts
    2,855
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Code Problem: Empty Text Box

    for assigning the value to B you can shorten it to

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

  7. #7
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,885
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    28 Thread(s)

    Default Re: Code Problem: Empty Text Box

    Quote Originally Posted by VBA Geek View Post
    for assigning the value to B you can shorten it to

    Code:
    If IsNumeric(txtA.Text) Then
        B = Application.Max(0, txtA.Text - 400) * 0.01
    Else
        B = 10
    End If
    Or maybe even to this...

    Code:
    B = Evaluate(Replace("IF(ISNUMBER(@),0.01*MAX(0,@-400),10)", "@", txtA.Text))
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  8. #8
    MrExcel MVP VBA Geek's Avatar
    Join Date
    Dec 2013
    Location
    Kepler 438B
    Posts
    2,855
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Code Problem: Empty Text Box

    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


    Quote Originally Posted by Rick Rothstein View Post
    Or maybe even to this...

    Code:
    B = Evaluate(Replace("IF(ISNUMBER(@),0.01*MAX(0,@-400),10)", "@", txtA.Text))
    Last edited by VBA Geek; Dec 16th, 2014 at 06:42 AM. Reason: tested

  9. #9
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    33,885
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    28 Thread(s)

    Default Re: Code Problem: Empty Text Box

    Quote Originally Posted by VBA Geek View Post
    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.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  10. #10
    New Member
    Join Date
    Dec 2014
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Code Problem: Empty Text Box

    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):
    Code:
      Select Case A
            Case "#N/A"
                B = 10
            Case 0 To 400 
                B = 0
            Case A > 400 
                B = (A - 400) * 0.1
        End Select
    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 = ...

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •