Consulting

Results 1 to 5 of 5

Thread: Help with error checking

  1. #1
    VBAX Regular
    Joined
    Jul 2014
    Posts
    54
    Location

    Help with error checking

    Hi all

    Error checking is not typically something I've thought much about in the past but in this one instance I'm looking to employ it and I'm a little stuck.

    I'm working on a template at the moment for purchase ledger which requires that the user put in the total of the invoice first (ComboBox100) and then go into more detail on the breakdown of the invoice on the same userform page.

    I'm employing a multipage userform with a commandbutton which takes the user on to the next page. I figured the easiest way to do the error checking would be to compare the value of the Total with the total of each of the fields in the breakdown.

    The breakdown is structured with 8 rows of ComboBoxes with options for "Principal", "Interest", "Costs" and "Credit" which the user can select from as they require and a field beside it (another ComboBox) to hold the value.

    Principal
    Interest
    Costs
    Credit
    1000.00

    I had defined PrincipalOne to PrincipalEight, InterestOne to InterestEight, CostsOne to CostsEight, CreditOne to CreditEight and TotalOne to TotalEight as Single.

    Then in the CommandButton I'd tried:-

    Private Sub CommandButton1.Click()
    If Me.ComboBox1.Value = "Principal" Then
    iPrincipalOne = Me.ComboBox2.Value
    Else
    End If
    If Me.ComboBox1.Value = "Interest" Then
    iInterestOne = Me.ComboBox2.Value
    Else
    End If
    If Me.ComboBox1,Value = "Costs" Then
    iCostsOne = Me.ComboBox2.Value"
    Else
    End If
    If Me.ComboBox1,Value = "Credit" Then
    iCreditOne = Me.ComboBox2.Value"
    Else
    End If
    If Me.ComboBox1.Value = vbNullString Then
    iPrincipalOne = vbNullString
    iInterestOne = vbNullString
    iCostsOne = vbNullString
    iCreditOne = vbNullString
    End If
    End Sub
    
    iTotal = iPrincipalOne + iInterestOne + iCostsOne -iCreditOne
    
    If Me.ComboBox100.Value <> iTotal Then
    Msgbox ("Something is fishy here..."
    Else
    Multipage1.Value = 2
    End If
    Unfortunately I can't seem to make it work and I don't know why.

    Any ideas?

    Thanks very much in advance as always

    Dav

  2. #2
    If we overlook the commas instead of periods in the syntax, the odd extraneous quote and the un-closed brackets, your code doesn't make any sense.

    You appear primarily to be using two combo boxes, but you are assigning values to undeclared variables all from the same combobox2 based on the value of combobox1. That combo box will have only one value yet you seem to be applying it conditionally to one of four variables and then hoping to add them up? I cannot imagine the conditions where that could be possible, let alone produce the correct result. Then finally you refer to combobox100 with no explanation of how that fits into the whole.

    I think you need to explain your thinking a little better on this occasion.
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  3. #3
    VBAX Regular
    Joined
    Jul 2014
    Posts
    54
    Location
    Hi Graham

    Sorry about the various typos - that's what comes of typing it too quickly freehand.

    I'll try to explain what I'm trying to achieve a little better. I've numbered each of the ComboBoxes for ease of reference.

    I have a Userform with a box for the total value of the invoice at the top followed immediately thereafter by eight rows of two ComboBoxes to allow the user to break down the invoice in more detail:-


    Total: [COMBOBOX100]
    1000.00



    [COMBOBOX1]
    Principal
    Interest
    Costs
    Paid
    [COMBOBOX2]
    [COMBOBOX3]
    Principal
    Interest
    Costs
    Paid
    [COMBOBOX4]
    [COMBOBOX5]
    Principal
    Interest
    Costs
    Paid
    [COMBOBOX6]
    [COMBOBOX7]
    Principal
    Interest
    Costs
    Paid
    [COMBOBOX8]
    [COMBOBOX9]
    Principal
    Interest
    Costs
    Paid
    [COMBOBOX10]
    [COMBOBOX11]
    Principal
    Interest
    Costs
    Paid
    [COMBOBOX12]
    [COMBOBOX13]
    Principal
    Interest
    Costs
    Paid
    [COMBOBOX14]
    [COMBOBOX15]
    Principal
    Interest
    Costs
    Paid
    [COMBOBOX16]













































    As I said each ComboBox in the left-hand column has four drop-down options (five if you count being blank and having no value) and in the right-hand column the value for each subset.

    I had considered assigning single variables to ComboBoxes2, 4, 6, 8, 10, 12, 14 and 16 (TotalOne to TotalEight), which I could then add together to create another variable called "GrandTotal". Then I'd just have to compare GrandTotal to the value entered by the user in ComboBox100, see if it matched and if so move to the next page of the Userform, or if it didn't match throw a message in the users face to check their addition.

    I realised I'd have difficulty however if the user chose "Paid" as the value in any of the left-hand ComboBoxes as the value entered in the corresponding right-hand ComboBox would have to be deducted rather than added. I wasn't sure how to make that work and that's why I decided instead to try to assign variables to each potential option in the left-hand column. I could add up all eight of the Principal, Interest and Costs values and then deduct all eight of the Paid values from that to get to where I wanted to be.

    I'm aware that this is likely making it far more complicated than it needs to be.

    I hope this makes more sense than my initial post.

    Thanks again

    Dav

  4. #4
    It makes some more sense, but it doesn't explain the second column of combo boxes nor the use of a combo box for the total. I don't see why these items are not text boxes as you appear to be adding values to them.

    If you name the total text box at the top TextTotal and the eight combo boxes on the left ComboBox1 to ComboBox8 and replace the combo boxes on the right with 8 text boxes TextBox1 to TextBox8 then the following code should do the job. Basically the Initialization sets the values in the combo boxes. When you click the command button the macro loops through the combo box controls and depending on the selection in the combo box either adds or subtracts the value in the text box of the same number to the sub total. Finally the sub total is compared with the total and if they match you will see a confirmation message and if not a warning. What you do with the values then will have to fit in with the rest of the process.


    Option Explicit
    
    Private Sub CommandButton1_Click()
    Dim oCtrl As Control
    Dim strBox As String
    Dim i As Integer
    Dim lngSubTotal As Long: lngSubTotal = 0
    Dim lngTotal As Long
        lngTotal = TextTotal.Value
        For i = 1 To 8
            For Each oCtrl In Controls
                If TypeName(oCtrl) = "ComboBox" Then
                    If i = Val(GetNum(oCtrl.Name)) Then
                        strBox = "TextBox" & GetNum(oCtrl.Name)
                        Select Case oCtrl.Value
                            Case "Principal", "Interest", "Costs"
                                lngSubTotal = lngSubTotal + Controls(strBox).Value
                            Case "Paid"
                                lngSubTotal = lngSubTotal - Controls(strBox).Value
                            Case Else
                        End Select
                    End If
                End If
            Next oCtrl
        Next i
        If Not lngSubTotal = lngTotal Then
            MsgBox "The entered values amount to " & lngSubTotal & vbCr & _
                   "and should be " & lngTotal
            Exit Sub
        Else
            MsgBox "The values match"
        End If
        Unload Me
    End Sub
    
    Private Sub UserForm_Initialize()
    Dim oCtrl As Control
        For Each oCtrl In Controls
            If TypeName(oCtrl) = "ComboBox" Then
                oCtrl.Clear
                oCtrl.AddItem "[Select Item]"
                oCtrl.AddItem "Principal"
                oCtrl.AddItem "Interest"
                oCtrl.AddItem "Costs"
                oCtrl.AddItem "Paid"
                oCtrl.ListIndex = 0
            End If
        Next oCtrl
    End Sub
    
    Function GetNum(strText As String) As String
    Dim strNum As String
    Dim i As Integer
        strNum = ""
        For i = 1 To Len(strText)
            If Mid(strText, i, 1) >= "0" And Mid(strText, i, 1) <= "9" Then
                strNum = strNum + Mid(strText, i, 1)
            End If
        Next
        GetNum = strNum
    End Function
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  5. #5
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,334
    Location
    I am with Graham and can't comprehend your use of comboboxes for entering values. None the less, I would Change events.

    Assume your grand total is cboGT, the left 8 cboType1 through 8 and the right 8 cbo1 through 8:

    Option Explicit
    Private Sub UserForm_Initialize()
    Dim lngIndex As Long
      With cboType1
        .AddItem "Principle"
        .AddItem "Interest"
        .AddItem "Costs"
        .AddItem "Paid"
      End With
      On Error Resume Next
      For lngIndex = 2 To 8
        Controls("cboType" & CStr(lngIndex)).List = cboType1.List
      Next
    End Sub
    'Add as many changes events as needed.  Here I only show enough for 3 column pairs
    Private Sub cboType1_Change()
      Validate
    End Sub
    Private Sub cboType2_Change()
      Validate
    End Sub
    Private Sub cboType3_Change()
      Validate
    End Sub
    Private Sub cbo1_Change()
      Validate
    End Sub
    Private Sub cbo2_Change()
      Validate
    End Sub
    Private Sub cbo3_Change()
      Validate
    End Sub
    Private Sub cboGT_Change()
      Validate
    End Sub
    
    
    Sub Validate()
    Dim lngIndex As Long
    Dim dblTotal
      cmdOK.Enabled = False
      dblTotal = 0
      On Error Resume Next
      For lngIndex = 1 To 8
        If Controls("cboType" & lngIndex).Value = "Paid" Then
          dblTotal = dblTotal - Controls("cbo" & lngIndex).Value
          
        Else
          dblTotal = dblTotal + Controls("cbo" & lngIndex).Value
        End If
      Next
      If dblTotal = CDbl(cboGT.Value) Then cmdOK.Enabled = True
    End Sub
    Greg

    Visit my website: http://gregmaxey.com

Posting Permissions

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