[CROSSFYRE2]

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.

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

[jscher2000]

CROSSFYRE2, on 2010-07-18 09:02, said:

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:

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):

Dim myInt As Integer
With Activedocument.FormFields
    myInt = Val(.Item("Text1").Result)
End With

[CROSSFYRE2]

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.

[jscher2000]

CROSSFYRE2, on 2010-07-18 10:22, said:

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:

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.

This post has been edited by jscher2000: 2010-07-18 12:49

[CROSSFYRE2]

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?

[CROSSFYRE2]

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 file(s)

•  testing.xlsm (14.29K)
  Number of downloads: 4

[RetiredGeek]

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 file(s)

•  SumOnForm.xlsm (16.83K)
  Number of downloads: 2

This post has been edited by RetiredGeek: 2010-07-18 14:07

[jscher2000]

CROSSFYRE2, on 2010-07-18 10:52, said:

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.

[CROSSFYRE2]

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?

[RetiredGeek]

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 file(s)

•  SumOnForm.xlsm (22.68K)
  Number of downloads: 2