Help with VBA Programming

[owayneb]

I have several textboxes on a form that is suppose to calculate Dimensions, i have a total of ten different rows containing textboxes for the length,width,height and pieces and then a line total.
and at the end i have a final total which adds each rows line total together .
The problem i am having is this , i want when the program is running and it comes upon a row that has null values it will omit those rows and still give me a final total.

this is the code i use for each line
If for example i enter information in line 1 but nothing else in the other lines, the msgbox pops up everytime the program reaches a new line, and then it calculates the total only if all the lines are populated with data.
I don't want this i want if data is enetered only in the first two lines then it will still calculate the final total without activating all the msgbox's
please help




If txtl1 = "" Or txtw1 = "" Or txth1 = "" Or txtp1 = "" Then
MsgBox "Please Enter atleast one line with Dimensions", vbInformation, "Dimensions"
txtl1.SetFocus
Else
dbll1 = txtl1
dblw1 = txtw1
dblh1 = txth1
intp1 = txtp1
dbltotal1 = (dbll1 * dblw1 * dblh1 * intp1) / 366
txttotal1 = Format(dbltotal1, "#,##0.00")
'txtgrandtotal = Format(dbltotal1, "#,##0.00")
End If


ElseIf txtl2 = "" Or txtw2 = "" Or txth2 = "" Or txtp2 = "" Then
MsgBox "Please Enter atleast one line with Dimensions", vbInformation, "Dimensions"
txtl2.SetFocus
Else
dbll2 = txtl2
dblw2 = txtw2
dblh2 = txth2
intp2 = txtp2
dbltotal2 = (dbll2 * dblw2 * dblh2 * intp2) / 366
txttotal2 = Format(dbltotal2, "#,##0.00")
'txtgrandtotal = Format(dbltotal2, "#,##0.00")
End If

dblfinal = (dbltotal1 + dbltotal2) + (dbltotal3 + dbltotal4) + (dbltotal5 + dbltotal6) + (dbltotal7 + dbltotal8) + (dbltotal9 + dbltotal10)

txttotal1 = Format(dbltotal1, "#,##0.00")
txttotal2 = Format(dbltotal2, "#,##0.00")
txttotal3 = Format(dbltotal3, "#,##0.00")
txttotal4 = Format(dbltotal4, "#,##0.00")
txttotal5 = Format(dbltotal5, "#,##0.00")
txttotal6 = Format(dbltotal6, "#,##0.00")
txttotal7 = Format(dbltotal7, "#,##0.00")
txttotal8 = Format(dbltotal8, "#,##0.00")
txttotal9 = Format(dbltotal9, "#,##0.00")
txttotal10 = Format(dbltotal10, "#,##0.00")

txtgrandtotal = Format(dblfinal, "#,##0.00")

[ramesh]

Why not wait until the user clicks some kind of submit button before checking for empty fields? This way you can test that if field 1 and 2 are not null then the calculation can proceed.

[owayneb]

the code is tied to a button click event

[owayneb]

Any Suggestions ?
should i write a function instead of putting all the code in the button click event
or would a select case work better?

[ramesh]

Looking at the code and your explanation again, some suggestions:
1. If you want to use Select/Case (recommended), you should index your text boxes: txtl(0) going up to txtl(9) for 10 length rows, etc.
2. Instead of a message for every line, you can have all accumulated line messages displayed in one message box or in your results area, e.g.
"Error, incomplete information in the following lines: Line 1, Line 5, Line 8. Please complete and resubmit".
3. Reduce your code. You can have your line totals directly access the text boxes
4. You need to validate the responses as numerical or else your formula will crash.

[owayneb]

don't understand what u mean by indexing the textboxes but below is the full code
i was gonna validate the textboxes as soon as i get pass this obstacle
thanks for your suggestions though

Private Sub cmdcalculate_Click()
Dim dblt1, dblt2, dblt3, dblt4, dblt5, dblt6, dblt7, dblt8, dblt9, dblt10 As Double
Dim dbll1, dbll2, dbll3, dbll4, dbll5, dbll6, dbll7, dbll8, dbll9, dbll10 As Double
Dim dblw1, dblw2, dblw3, dblw4, dblw5, dblw6, dblw7, dblw8, dblw9, dblw10 As Double
Dim dblh1, dblh2, dblh3, dblh4, dblh5, dblh6, dblh7, dblh8, dblh9, dblh10 As Double
Dim dbltotal1, dbltotal2, dbltotal3, dbltotal4, dbltotal5, dbltotal6, dbtotal7, dbltotal8, dbltotal9, dbltotal10
Dim intp1, intp2, intp3, intp4, intp5, intp6, intp7, intp8, intp9, intp10 As Integer
Dim dblfinal As Double


If Optin = True Then


If txtl1 = "" Or txtw1 = "" Or txth1 = "" Or txtp1 = "" Then
MsgBox "Please Enter atleast one line with Dimensions", vbInformation, "Dimensions"
txtl1.SetFocus
Exit Sub
Else
dbll1 = txtl1
dblw1 = txtw1
dblh1 = txth1
intp1 = txtp1
dbltotal1 = (dbll1 * dblw1 * dblh1 * intp1) / 366
txttotal1 = Format(dbltotal1, "#,##0.00")
'txtgrandtotal = Format(dbltotal1, "#,##0.00")
End If


ElseIf txtl2 = "" Or txtw2 = "" Or txth2 = "" Or txtp2 = "" Then
MsgBox "Please Enter atleast one line with Dimensions", vbInformation, "Dimensions"
txtl2.SetFocus
Else
dbll2 = txtl2
dblw2 = txtw2
dblh2 = txth2
intp2 = txtp2
dbltotal2 = (dbll2 * dblw2 * dblh2 * intp2) / 366
txttotal2 = Format(dbltotal2, "#,##0.00")
'txtgrandtotal = Format(dbltotal2, "#,##0.00")
End If


If txtl3 = "" Or txtw3 = "" Or txth3 = "" Or txtp3 = "" Then
MsgBox "Please Enter atleast one line with Dimensions", vbInformation, "Dimensions"
txtl3.SetFocus
Else
dbll3 = txtl3
dblw3 = txtw3
dblh3 = txth3
intp3 = txtp3
dbltotal3 = (dbll3 * dblw3 * dblh3 * intp3) / 366
dbltotal3 = (dbll3 * dblw3 * dblh3 * intp3) / 366
txttotal3 = Format(dbltotal3, "#,##0.00")
'txtgrandtotal = Format(dbltotal3, "#,##0.00")
End If

If txtl4 = "" Or txtw4 = "" Or txth4 = "" Or txtp4 = "" Then
MsgBox "Please Enter atleast one line with Dimensions", vbInformation, "Dimensions"
txtl4.SetFocus
Else
dbll4 = txtl4
dblw4 = txtw4
dblh4 = txth4
intp4 = txtp4
dbltotal4 = (dbll4 * dblw4 * dblh4 * intp4) / 366
dbltotal4 = (dbll4 * dblw4 * dblh4 * intp4) / 366
txttotal4 = Format(dbltotal4, "#,##0.00")
'txtgrandtotal = Format(dbltotal4, "#,##0.00")
End If


If txtl5 = "" Or txtw5 = "" Or txth5 = "" Or txtp5 = "" Then
MsgBox "Please Enter atleast one line with Dimensions", vbInformation, "Dimensions"
txtl5.SetFocus
Else
dbll5 = txtl5
dblw5 = txtw5
dblh5 = txth5
intp5 = txtp5
dbltotal5 = (dbll5 * dblw5 * dblh5 * intp5) / 366
dbltotal5 = (dbll5 * dblw5 * dblh5 * intp5) / 366
txttotal5 = Format(dbltotal5, "#,##0.00")
'txtgrandtotal = Format(dbltotal5, "#,##0.00")
End If


If txtl6 = "" Or txtw6 = "" Or txth6 = "" Or txtp6 = "" Then
MsgBox "Please Enter atleast one line with Dimensions", vbInformation, "Dimensions"
txtl6.SetFocus
Else
dbll6 = txtl6
dblw6 = txtw6
dblh6 = txth6
intp6 = txtp6
dbltotal6 = (dbll6 * dblw6 * dblh6 * intp6) / 366
dbltotal6 = (dbll6 * dblw6 * dblh6 * intp6) / 366
txttotal6 = Format(dbltotal6, "#,##0.00")
'txtgrandtotal = Format(dbltotal6, "#,##0.00")
End If


If txtl7 = "" Or txtw7 = "" Or txth7 = "" Or txtp7 = "" Then
MsgBox "Please Enter atleast one line with Dimensions", vbInformation, "Dimensions"
txtl7.SetFocus

Else
dbll7 = txtl7
dblw7 = txtw7
dblh7 = txth7
intp7 = txtp7
dbltotal7 = (dbll7 * dblw7 * dblh7 * intp7) / 366
dbltotal7 = (dbll7 * dblw7 * dblh7 * intp7) / 366
txttotal7 = Format(dbltotal7, "#,##0.00")
'txtgrandtotal = Format(dbltotal7, "#,##0.00")
End If


If txtl8 = "" Or txtw8 = "" Or txth8 = "" Or txtp8 = "" Then
MsgBox "Please Enter atleast one line with Dimensions", vbInformation, "Dimensions"
txtl8.SetFocus
Else
dbll8 = txtl8
dblw8 = txtw8
dblh8 = txth8
intp8 = txtp8
dbltotal8 = (dbll8 * dblw8 * dblh8 * intp8) / 366
dbltotal8 = (dbll8 * dblw8 * dblh8 * intp8) / 366
txttotal8 = Format(dbltotal8, "#,##0.00")
'txtgrandtotal = Format(dbltotal8, "#,##0.00")
End If


If txtl9 = "" Or txtw9 = "" Or txth9 = "" Or txtp9 = "" Then
MsgBox "Please Enter atleast one line with Dimensions", vbInformation, "Dimensions"
txtl9.SetFocus
Else
dbll9 = txtl9
dblw9 = txtw9
dblh9 = txth9
intp9 = txtp9
dbltotal9 = (dbll9 * dblw9 * dblh9 * intp9) / 366
dbltotal9 = (dbll9 * dblw9 * dblh9 * intp9) / 366
txttotal9 = Format(dbltotal9, "#,##0.00")
'txtgrandtotal = Format(dbltotal9, "#,##0.00")
End If


If txtl10 = "" Or txtw10 = "" Or txth10 = "" Or txtp10 = "" Then
MsgBox "Please Enter atleast one line with Dimensions", vbInformation, "Dimensions"
txtl10.SetFocus
Else
dbll10 = txtl10
dblw10 = txtw10
dblh10 = txth10
intp10 = txtp10
dbltotal10 = (dbll10 * dblw10 * dblh10 * intp10) / 366
dbltotal10 = (dbll10 * dblw10 * dblh10 * intp10) / 366
txttotal10 = Format(dbltotal10, "#,##0.00")
'txtgrandtotal = Format(dbltotal10, "#,##0.00")
End If
  
 
dblfinal = (dbltotal1 + dbltotal2) + (dbltotal3 + dbltotal4) + (dbltotal5 + dbltotal6) + (dbltotal7 + dbltotal8) + (dbltotal9 + dbltotal10)

 txttotal1 = Format(dbltotal1, "#,##0.00")
 txttotal2 = Format(dbltotal2, "#,##0.00")
 txttotal3 = Format(dbltotal3, "#,##0.00")
 txttotal4 = Format(dbltotal4, "#,##0.00")
 txttotal5 = Format(dbltotal5, "#,##0.00")
 txttotal6 = Format(dbltotal6, "#,##0.00")
 txttotal7 = Format(dbltotal7, "#,##0.00")
 txttotal8 = Format(dbltotal8, "#,##0.00")
 txttotal9 = Format(dbltotal9, "#,##0.00")
 txttotal10 = Format(dbltotal10, "#,##0.00")

txtgrandtotal = Format(dblfinal, "#,##0.00")

'Else
If Optcm = True Then

If txtl1 = "" Or txtw1 = "" Or txth1 = "" Or txtp1 = "" Then
MsgBox "Please Enter atleast one line with Dimensions", vbInformation, "Dimensions"
txtl1.SetFocus
Else
dbll1 = txtl1
dblw1 = txtw1
dblh1 = txth1
intp1 = txtp1
dbltotal1 = (dbll1 * dblw1 * dblh1 * intp1) / 6000
End If

If txtl2 = "" Or txtw2 = "" Or txth2 = "" Or txtp2 = "" Then
MsgBox "Please Enter atleast one line with Dimensions", vbInformation, "Dimensions"
Else
dbll2 = txtl2
dblw2 = txtw2
dblh2 = txth2
intp2 = txtp2
dbltotal2 = (dbll2 * dblw2 * dblh2 * intp2) / 6000
End If

If txtl3 = "" Or txtw3 = "" Or txth3 = "" Or txtp3 = "" Then
MsgBox "Please Enter atleast one line with Dimensions", vbInformation, "Dimensions"
Else
dbll3 = txtl3
dblw3 = txtw3
dblh3 = txth3
intp3 = txtp3
dbltotal3 = (dbll3 * dblw3 * dblh3 * intp3) / 6000
End If

If txtl1 = "" Or txtw1 = "" Or txth1 = "" Or txtp1 = "" Then
MsgBox "Please Enter atleast one line with Dimensions", vbInformation, "Dimensions"
Else
dbll4 = txtl4
dblw4 = txtw4
dblh4 = txth4
intp4 = txtp4
dbltotal4 = (dbll4 * dblw4 * dblh4 * intp4) / 6000
End If

If txtl5 = "" Or txtw5 = "" Or txth5 = "" Or txtp5 = "" Then
MsgBox "Please Enter atleast one line with Dimensions", vbInformation, "Dimensions"
Else
dbll5 = txtl5
dblw5 = txtw5
dblh5 = txth5
intp5 = txtp5
dbltotal5 = (dbll5 * dblw5 * dblh5 * intp5) / 6000
End If

If txtl6 = "" Or txtw6 = "" Or txth6 = "" Or txtp6 = "" Then
MsgBox "Please Enter atleast one line with Dimensions", vbInformation, "Dimensions"
Else
dbll6 = txtl6
dblw6 = txtw6
dblh6 = txth6
intp6 = txtp6
dbltotal6 = (dbll6 * dblw6 * dblh6 * intp6) / 6000
End If

If txtl7 = "" Or txtw7 = "" Or txth7 = "" Or txtp7 = "" Then
MsgBox "Please Enter atleast one line with Dimensions", vbInformation, "Dimensions"
Else
dbll7 = txtl7
dblw7 = txtw7
dblh7 = txth7
intp7 = txtp7
dbltotal7 = (dbll7 * dblw7 * dblh7 * intp7) / 6000
End If

If txtl8 = "" Or txtw8 = "" Or txth8 = "" Or txtp8 = "" Then
MsgBox "Please Enter atleast one line with Dimensions", vbInformation, "Dimensions"
Else
dbll8 = txtl8
dblw8 = txtw8
dblh8 = txth8
intp8 = txtp8
dbltotal8 = (dbll8 * dblw8 * dblh8 * intp8) / 6000
End If

If txtl9 = "" Or txtw9 = "" Or txth9 = "" Or txtp9 = "" Then
MsgBox "Please Enter atleast one line with Dimensions", vbInformation, "Dimensions"
Else
dbll9 = txtl9
dblw9 = txtw9
dblh9 = txth9
intp9 = txtp9
dbltotal9 = (dbll9 * dblw9 * dblh9 * intp9) / 6000
End If

If txtl10 = "" Or txtw10 = "" Or txth10 = "" Or txtp10 = "" Then
MsgBox "Please Enter atleast one line with Dimensions", vbInformation, "Dimensions"
Else
dbll10 = txtl10
dblw10 = txtw10
dblh10 = txth10
intp10 = txtp10
dbltotal10 = (dbll10 * dblw10 * dblh10 * intp10) / 6000
End If



dblfinal = (dbltotal1 + dbltotal2) + (dbltotal3 + dbltotal4) + (dbltotal5 + dbltotal6) + (dbltotal7 + dbltotal8) + (dbltotal9 + dbltotal10)

 txttotal1 = Format(dbltotal1, "#,##0.00")
 txttotal2 = Format(dbltotal2, "#,##0.00")
 txttotal3 = Format(dbltotal3, "#,##0.00")
 txttotal4 = Format(dbltotal4, "#,##0.00")
 txttotal5 = Format(dbltotal5, "#,##0.00")
 txttotal6 = Format(dbltotal6, "#,##0.00")
 txttotal7 = Format(dbltotal7, "#,##0.00")
 txttotal8 = Format(dbltotal8, "#,##0.00")
 txttotal9 = Format(dbltotal9, "#,##0.00")
 txttotal10 = Format(dbltotal10, "#,##0.00")

txtgrandtotal = Format(dblfinal, "#,##0.00")

Optin = False


End If
End If
End Sub

Private Sub cmdexit_Click()
Unload Me
End Sub

Private Sub UserForm_Initialize()
Optin = True
End Sub

this is the interface

the image didn't load in the last post , i think it should load in this one

[owayneb]

I changed my approach and used the ISNUMERIC function instead of is null and it work like a charm

If txtl1 = "" Or txtw1 = "" Or txth1 = "" Or txtp1 = "" Then
MsgBox "Please Enter atleast one line with Dimensions", vbInformation, "Dimensions"
txtl1.SetFocus
Exit Sub
Else
dbll1 = txtl1
dblw1 = txtw1
dblh1 = txth1
intp1 = txtp1
dbltotal1 = (dbll1 * dblw1 * dblh1 * intp1) / 366
txttotal1 = Format(dbltotal1, "#,##0.00")
End If

If IsNumeric(txtl2) And IsNumeric(txtw2) And IsNumeric(txth2) And IsNumeric(txtp2) Then
dbll2 = txtl2
dblw2 = txtw2
dblh2 = txth2
intp2 = txtp2
dbltotal2 = (dbll2 * dblw2 * dblh2 * intp2) / 366
txttotal2 = Format(dbltotal2, "#,##0.00")
End If

If IsNumeric(txtl3) And IsNumeric(txtw3) And IsNumeric(txth3) And IsNumeric(txtp3) Then
dbll3 = txtl3
dblw3 = txtw3
dblh3 = txth3
intp3 = txtp3
dbltotal3 = (dbll3 * dblw3 * dblh3 * intp3) / 366
dbltotal3 = (dbll3 * dblw3 * dblh3 * intp3) / 366
txttotal3 = Format(dbltotal3, "#,##0.00")
End If

If IsNumeric(txtl4) And IsNumeric(txtw4) And IsNumeric(txth4) And IsNumeric(txtp4) Then
dbll4 = txtl4
dblw4 = txtw4
dblh4 = txth4
intp4 = txtp4
dbltotal4 = (dbll4 * dblw4 * dblh4 * intp4) / 366
dbltotal4 = (dbll4 * dblw4 * dblh4 * intp4) / 366
txttotal4 = Format(dbltotal4, "#,##0.00")
End If

[iKlsR]

please get an ide and a good tutorial and stop playing with dialog boxes and ok buttons in notepad..