Selected Answer
Actually, the Val() function is the one to use when you convert the numeric text string taken from a TextBox to an algebraic number to sum up. I have reviewed two of your procedures and made some amendments. My target was to make your code more transparent. Perhaps I caught the mistakes that gave rise to your question in the process, and perhaps I introduced new mistakes of my own. I couldn't test for lack of data. Here is the revised code. Please test.
Private Sub lstCheckMi_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim Idx As Integer
Dim Tmp As Variant
Dim i As Integer
' On Error Resume Next ' which error? No ErrHandler!
'find the selected list item
Idx = Me.lstCheckMi.ListIndex
'Col.B = Auto Generated ID seq number
Tmp = Me.lstCheckMi.Column(0, Idx)
Me.AcntId.Value = Format(Tmp, "00")
'Col.C = Auto Date & Weekday of Data Entry
Tmp = CDate(Me.lstCheckMi.Column(1, Idx))
' Format needss a Number, in this case a Date,
' which it converts to a formatted Text
Me.AcntDate.Value = Format(Tmp, "dd mmm yyyy")
'add the values to the text boxes
' your naming of text boxes represents an epic effort to replace
' meaningless generic names with complicated names of no meaning lol:
For i = 1 To 11
' when i = 1 Col = 4 (= "D"). Hence Col = 3 + i
Tmp = "Acnt" & i & Chr(96 + i)
If i = 11 Then Tmp = Left(Tmp, Len(Tmp) - 2) & "5"
Controls(Tmp).Value = lstCheckMi.Column(1 + i, Idx)
Next i
' Err.Clear
End Sub
Private Sub cmdAdd_Click()
Dim DataSH As Worksheet
Dim Addme As Range
Dim Total As Double
Dim SubTtl As Double
Dim Tmp As Variant
Dim C As Long
'set the variable (pretty obvious, isn't it?)
Set DataSH = Sheet1
On Error GoTo errHandler
'set range for the destination
' Count the rows in the same sheet where you set the range
Set Addme = DataSH.Cells(DataSH.Rows.Count, 3).End(xlUp).Offset(1, 0)
'hold in memory and stop screen flicker
Application.ScreenUpdating = False
' The 'Me' keyword identifies the currently active form.
' With no other forms anywhere in sight such identification isn't required.
' You may safely omit it.
If Me.txtDate = "" Then
MsgBox "Not Enough Data."
Exit Sub
End If
'send the values to the database
'add the unique reference ID then all other values
'Col.B = Auto Generated ID seq number
Addme.Offset(0, -1) = DataSH.Range("C6").Value + 1
'Col.C = Date of Data Entry
Tmp = CDate(Me.txtDate.Value)
Me.txtDate.Value = Format(Tmp, "dd mmm yyyy")
Addme.Value = Tmp
For C = 1 To 10
Tmp = "txtAcnt" & i
If i = 1 Then Tmp = Tmp & "a"
SubTtl = Val(Controls(Tmp).Value)
Addme.Offset(0, 3 + C).Value = SubTtl
Total = Total + SubTtl
Next C
Addme.Offset(0, C).Value = Total
'sort the data by "Date"
' DataSH.Select ' don't ever select anything
With DataSH
.Range("B9:N40").Sort Key1:=Range("B9"), Order1:=xlAscending, Header:=xlGuess
End With
'clear the values after entry
' Clear ' what does this do? to whom?
'communicate with the user
MsgBox "Data Added Successfully", vbInformation, "Action report"
'return to interface sheet sheet
' this should still be the sheet that was active when this code started.
' therefore this command is without effect
Sheet2.Select
'reset the form
' On Error GoTo 0
Exit Sub
errHandler:
'if error occurs then show me exactly where the error occurs
' This idea isn't worth the effort during development
' and completely useless thereafter.
' It's not an error handler but and error notifier.
MsgBox "Error " & Err.Number & _
" (" & Err.Description & ") in procedure cmdClear_Click of Form CheckSumAdddb"
End Sub
Intenting is very important in VBA because it makes the code more readable. You used it to subordinate the code to your comments. That isn't a good idea because obviously, if the comments become more prominent, the code itself must become less so. The result is that one has to search for the code between the comments which puts the cart before the horse.
In consequence of the above I tackled your problem by making your code more transparent. Part of this was to change the way the total is made almost entirely. If that didn't solve the problem I'll take another look once you tell me the result of your tests.