Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Feb 2008
    Location
    LOUISVILLE, Kentucky, USA
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Here' is my problem.

    I have four textbox's that have a numeric value each one gets totaled and then needs to be shown on the user form as well as being inputted into the spreadsheet.
    my problem is is intnum4 is blank or zero I get an error. apparently it needs something in intnum4. But what if it's blank? this is my problem and it can't only happen to int num4 but to intnum2 and intnum 3 as well . can anybody assist me please.

    Code:
    Dim STRTOTAL As String
    Dim INTNUM1  As Integer
    Dim INTNUM2 As Integer
    Dim intNUM3 As Integer
    Dim intNUM4, INTANSWER As Integer
      
    STRTOTAL = Me.txttotal.Value
    INTNUM1 = txtamt1.Value
    INTNUM2 = txtamt2.Value
    intNUM3 = txtamt3.Value
    intNUM4 = txtamt4.Value
     
    If STRTOTAL = "" Then
    INTANSWER = INTNUM1 + INTNUM2 + intNUM3 + intNUM4
     Me.txttotal.Value = " SUM; " & INTANSWER
       
       End If

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts
    Quote Originally Posted by CROSSFYRE2 View Post
    I have four textbox's that have a numeric value each one gets totaled and then needs to be shown on the user form as well as being inputted into the spreadsheet.
    my problem is is intnum4 is blank or zero I get an error.
    The error is assigning a non-numeric (null) value from the form control to an integer variable? VBA has a couple of different type and conversion-related functions that might be useful.

    Using IsNumeric() to test the value in advance:
    Code:
    Dim myInt As Integer
    With ActiveDocument.FormFields
        If IsNumeric(.Item("Text1").Result) Then
            myInt = .Item("Text1").Result
        Else
            myInt = 0
        End If
    End With
    Using Val() to force the contents to a numeric value (null or text becomes zero):
    Code:
    Dim myInt As Integer
    With ActiveDocument.FormFields
        myInt = Val(.Item("Text1").Result)
    End With

  4. #3
    2 Star Lounger
    Join Date
    Feb 2008
    Location
    LOUISVILLE, Kentucky, USA
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm sorry but I did not understand that... can you please explain in laments terms. I'm not that great with excel or visual basic.

  5. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts
    Quote Originally Posted by CROSSFYRE2 View Post
    can you please explain in laments terms.
    If you assign anything except a numeric value to an integer, you will get a type mismatch error. You either can check the contents of the textbox in advance and only assign numeric values, or you can pre-convert the contents to an integer value by running it through the Val() function. The latter is the easiest fix to your code:

    Code:
    Dim STRTOTAL As String
    Dim INTNUM1  As Integer
    Dim INTNUM2 As Integer
    Dim intNUM3 As Integer
    Dim intNUM4, INTANSWER As Integer
      
    STRTOTAL = Me.txttotal.Value
    INTNUM1 = Val(txtamt1.Value)
    INTNUM2 = Val(txtamt2.Value)
    intNUM3 = Val(txtamt3.Value)
    intNUM4 = Val(txtamt4.Value)
     
    If STRTOTAL = "" Then
    INTANSWER = INTNUM1 + INTNUM2 + intNUM3 + intNUM4
     Me.txttotal.Value = " SUM; " & INTANSWER
       
       End If
    Does that help?

    == Edit ==

    If that doesn't help, please post a document with your form attached for further review.

  6. #5
    2 Star Lounger
    Join Date
    Feb 2008
    Location
    LOUISVILLE, Kentucky, USA
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts
    yes that does, thank you very much sir. it is appreciated, but what if it's currency what do I have to do to then? is there anything special for that?

  7. #6
    2 Star Lounger
    Join Date
    Feb 2008
    Location
    LOUISVILLE, Kentucky, USA
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts
    here is my app that i'm testing with the original one is really huge if you would like to see that I can download that as well sir. I just don't want to scare anybody off trying to help a newbie at visual basic. I am not a pro like your self, most of my code comes from my school books and books I checked out at the library.
    Attached Files Attached Files

  8. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,158
    Thanks
    200
    Thanked 781 Times in 715 Posts
    Here's your file with some rework.

    What I did is initialize all the textboxes to 0 in a Form_Initialize() event.
    I also changed TextBox5 to a label since the user won't be changing it.
    I also added the Option Explicit command at the top of the module, this will tell you if you try to use a variable that is not Dimmensioned (Dim).

    What I didn't do but you could do if desired is have the sum recalculated after each entry by adding an After_Update or On_Exit event to each of the 4 textboxes then move the code that calculates the total to a separate sub routine and call that routine from each of the textbox events.

    I hope this helps.
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  9. #8
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts
    Quote Originally Posted by CROSSFYRE2 View Post
    what if it's currency what do I have to do to then? is there anything special for that?
    For currency, you're likely to have decimal values, so you would want to use a "single" instead of an "integer." I haven't looked at your workbook; hopefully RetiredGeek was able to address this issue.

  10. #9
    2 Star Lounger
    Join Date
    Feb 2008
    Location
    LOUISVILLE, Kentucky, USA
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts
    thank you sir, but even though the user will not be using the lbl's they still need to have the amount add to the spreadsheet. the lbl works great but it does not show in the spreedsheet. any ideas?

  11. #10
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,158
    Thanks
    200
    Thanked 781 Times in 715 Posts
    Ok,

    Here's a workbook that should show you how to do what you're asking. Of course, some changes will be required to make it fit your production environment.

    Good Luck!
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


Posting Permissions

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