Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Summing Up textboxes

0

Hello, I've been working on Excel Userform database, I've been having a bit of difficulty summing up a number of textbox values and show the result as a total in another textbox.'

Val' returned a '0' result, the only one I had some success was 'Clng', the only issue I 'm coming across that Clng isn't adding the fractions.

I just wonder if there another way where I can sum up the textbox values.

Thanks

Answer
Discuss

Answers

0
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.

Discuss

Discussion

Thanks Variatus, I will study your answer and apply and test.
jdgrapes (rep: 14) Apr 28, '20 at 6:13 pm
Hi Variatus, Not sure if I should have made a fresh question or add to the discussion.
I did a test run, and i got a error at location
For C = 1 To 10         Tmp = "txtAcnt" & i
So I didn't get to check if the Add textboxes worked.
Many Thanks
jdgrapes (rep: 14) Apr 30, '20 at 6:55 pm
No. This problem must be solved here. Fact is, there shouldn't be a problem. So, first, what's the error you get (number & description, please). Make sure it's that line! Try this code in a standard code module:-
Private Sub TryOut()
 
    Dim Tmp As Variant
    Dim i As Integer
    
    Tmp = Date
    For i = 1 To 3
        Tmp = "Acc" & i & Chr(96 + i)
        Debug.Print i, Tmp
    Next i
End Sub

Tell me if it throws an error. Check the References (Tools > References) and see if any of those checked are marked as "missing". VBA likes to throw inexplicable errors like this when, in fact, it fails to compile. Rem out all code, anywhere in your project, except this procedure and what else you need for testing it and see if the error persists.
Variatus (rep: 4889) Apr 30, '20 at 8:19 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login