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

Code:

---

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

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.

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

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.

Code:

---

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

---

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:

Code:

---

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

---