Results 1 to 8 of 8

Thread: Help with VBA Programming

  1. #1
    Join Date
    Sep 2005
    Posts
    33
    Rep Power
    0

    Default Help with VBA Programming

    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")
    http://www.jamaicahappenings.com
    Browse and upload News and events happening in your community for free, also play online games , chat and post your classified ads and much more all for free at

  2. #2
    Join Date
    Aug 2002
    Posts
    6,223
    Rep Power
    0

    Default

    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.
    .
    PC - Ubuntu 15.04 64bit Desktop
    HP Pav G60-236US 3GB RAM Laptop, Ubuntu 15.04 64bit and Win7 Home

    "So Daddy, how come you telling me stealing not right when YOU copying DVDs? How come? How Come?"


    RIP Ramesh ...

  3. #3
    Join Date
    Sep 2005
    Posts
    33
    Rep Power
    0

    Default It is

    the code is tied to a button click event
    http://www.jamaicahappenings.com
    Browse and upload News and events happening in your community for free, also play online games , chat and post your classified ads and much more all for free at

  4. #4
    Join Date
    Sep 2005
    Posts
    33
    Rep Power
    0

    Default

    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?
    http://www.jamaicahappenings.com
    Browse and upload News and events happening in your community for free, also play online games , chat and post your classified ads and much more all for free at

  5. #5
    Join Date
    Aug 2002
    Posts
    6,223
    Rep Power
    0

    Default

    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.
    .
    PC - Ubuntu 15.04 64bit Desktop
    HP Pav G60-236US 3GB RAM Laptop, Ubuntu 15.04 64bit and Win7 Home

    "So Daddy, how come you telling me stealing not right when YOU copying DVDs? How come? How Come?"


    RIP Ramesh ...

  6. #6
    Join Date
    Sep 2005
    Posts
    33
    Rep Power
    0

    Default

    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


    Code:
    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
    Last edited by Arch_Angel; Feb 17, 2012 at 10:07 PM. Reason: added CODE tags. merged multiple posts.
    http://www.jamaicahappenings.com
    Browse and upload News and events happening in your community for free, also play online games , chat and post your classified ads and much more all for free at

  7. #7
    Join Date
    Sep 2005
    Posts
    33
    Rep Power
    0

    Default Got it to work

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

    Code:
    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
    Last edited by Arch_Angel; Feb 18, 2012 at 08:58 PM. Reason: added CODE tags
    http://www.jamaicahappenings.com
    Browse and upload News and events happening in your community for free, also play online games , chat and post your classified ads and much more all for free at

  8. #8
    Join Date
    Oct 2010
    Posts
    63
    Rep Power
    0

    Default

    please get an ide and a good tutorial and stop playing with dialog boxes and ok buttons in notepad..
    ► with me... now ■ and think...

Posting Permissions

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