Adding a year today total textbox.

J

josros60

Well-known Member
Joined
Jun 27, 2010
Messages
786
Office Version
  1. 365
hi,

how can I add a year today total at the end of list each of the box in the code below represent amount per moth (January-December)


here is the code:

Code: 
COGSform_All.TextBox_Results1.Value = .Cells(.Range(strAddress).Row, 2).Value                COGSform_All.TextBox_Results2.Value = .Cells(.Range(strAddress).Row, 1).Value
                
               COGSform_All.TextBox_Results3.Value = Format(.Cells(.Range(strAddress).Row, 9).Value, "$#,##0.00;($#,##0.00)")
                COGSform_All.TextBox_Results4.Value = Format(.Cells(.Range(strAddress).Row, 10).Value, "$#,##0.00;($#,##0.00)")
                COGSform_All.TextBox_Results5.Value = Format(.Cells(.Range(strAddress).Row, 11).Value, "$#,##0.00;($#,##0.00)")
                COGSform_All.TextBox_Results6.Value = Format(.Cells(.Range(strAddress).Row, 12).Value, "$#,##0.00;($#,##0.00)")
                COGSform_All.TextBox_Results7.Value = Format(.Cells(.Range(strAddress).Row, 13).Value, "$#,##0.00;($#,##0.00)")
                COGSform_All.TextBox_Results8.Value = Format(.Cells(.Range(strAddress).Row, 14).Value, "$#,##0.00;($#,##0.00)")
                COGSform_All.TextBox_Results9.Value = Format(.Cells(.Range(strAddress).Row, 15).Value, "$#,##0.00;($#,##0.00)")
                COGSform_All.TextBox_Results10.Value = Format(.Cells(.Range(strAddress).Row, 16).Value, "$#,##0.00;($#,##0.00)")
                COGSform_All.TextBox_Results11.Value = Format(.Cells(.Range(strAddress).Row, 17).Value, "$#,##0.00;($#,##0.00)")
                COGSform_All.TextBox_Results12.Value = Format(.Cells(.Range(strAddress).Row, 18).Value, "$#,##0.00;($#,##0.00)")
                COGSform_All.TextBox_Results13.Value = Format(.Cells(.Range(strAddress).Row, 19).Value, "$#,##0.00;($#,##0.00)")
                COGSform_All.TextBox_Results14.Value = Format(.Cells(.Range(strAddress).Row, 20).Value, "$#,##0.00;($#,##0.00)")

thank you
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
Do you need something like this:

Code: 
  COGSform_All.TextBox_Results1.Value = .Cells(.Range(strAddress).Row, 2).Value
  COGSform_All.TextBox_Results2.Value = .Cells(.Range(strAddress).Row, 1).Value
  COGSform_All.TextBox_Results3.Value = Format(.Cells(.Range(strAddress).Row, 9).Value, "$#,##0.00;($#,##0.00)")
  COGSform_All.TextBox_Results4.Value = Format(.Cells(.Range(strAddress).Row, 10).Value, "$#,##0.00;($#,##0.00)")
  COGSform_All.TextBox_Results5.Value = Format(.Cells(.Range(strAddress).Row, 11).Value, "$#,##0.00;($#,##0.00)")
  COGSform_All.TextBox_Results6.Value = Format(.Cells(.Range(strAddress).Row, 12).Value, "$#,##0.00;($#,##0.00)")
  COGSform_All.TextBox_Results7.Value = Format(.Cells(.Range(strAddress).Row, 13).Value, "$#,##0.00;($#,##0.00)")
  COGSform_All.TextBox_Results8.Value = Format(.Cells(.Range(strAddress).Row, 14).Value, "$#,##0.00;($#,##0.00)")
  COGSform_All.TextBox_Results9.Value = Format(.Cells(.Range(strAddress).Row, 15).Value, "$#,##0.00;($#,##0.00)")
  COGSform_All.TextBox_Results10.Value = Format(.Cells(.Range(strAddress).Row, 16).Value, "$#,##0.00;($#,##0.00)")
  COGSform_All.TextBox_Results11.Value = Format(.Cells(.Range(strAddress).Row, 17).Value, "$#,##0.00;($#,##0.00)")
  COGSform_All.TextBox_Results12.Value = Format(.Cells(.Range(strAddress).Row, 18).Value, "$#,##0.00;($#,##0.00)")
  COGSform_All.TextBox_Results13.Value = Format(.Cells(.Range(strAddress).Row, 19).Value, "$#,##0.00;($#,##0.00)")
  COGSform_All.TextBox_Results14.Value = Format(.Cells(.Range(strAddress).Row, 20).Value, "$#,##0.00;($#,##0.00)")
 Dim total, i
  For i = 9 To 20
    total = total + .Cells(.Range(strAddress).Row, i).Value
  Next
 
Last edited:
Upvote 0
thank you.

Little confused I did post code and added textbox called Year to date and and named box TextboxTotal but when run code doesn't show grand total in the textbox:

Code: 
Private Sub ListBox_Results_Click()'Go to selection on sheet when result is clicked


Dim strAddress As String
Dim l As Long
Dim total, i
  For i = 9 To 20
    For l = 0 To ListBox_Results.ListCount
        If ListBox_Results.Selected(l) = True Then
            strAddress = ListBox_Results.List(l, 1)
            ActiveSheet.Range(strAddress).Select
            'Populate textboxes with results
            With ActiveSheet
                COGSform_All.TextBox_Results1.Value = .Cells(.Range(strAddress).Row, 2).Value
                COGSform_All.TextBox_Results2.Value = .Cells(.Range(strAddress).Row, 1).Value
                'COGSform_All.TextBox_Results1.Value = .Cells(.Range(strAddress).Row, 1).Value
                'COGSform_All.TextBox_Results2.Value = .Cells(.Range(strAddress).Row, 2).Value
                COGSform_All.TextBox_Results3.Value = Format(.Cells(.Range(strAddress).Row, 9).Value, "$#,##0.00;($#,##0.00)")
                COGSform_All.TextBox_Results4.Value = Format(.Cells(.Range(strAddress).Row, 10).Value, "$#,##0.00;($#,##0.00)")
                COGSform_All.TextBox_Results5.Value = Format(.Cells(.Range(strAddress).Row, 11).Value, "$#,##0.00;($#,##0.00)")
                COGSform_All.TextBox_Results6.Value = Format(.Cells(.Range(strAddress).Row, 12).Value, "$#,##0.00;($#,##0.00)")
                COGSform_All.TextBox_Results7.Value = Format(.Cells(.Range(strAddress).Row, 13).Value, "$#,##0.00;($#,##0.00)")
                COGSform_All.TextBox_Results8.Value = Format(.Cells(.Range(strAddress).Row, 14).Value, "$#,##0.00;($#,##0.00)")
                COGSform_All.TextBox_Results9.Value = Format(.Cells(.Range(strAddress).Row, 15).Value, "$#,##0.00;($#,##0.00)")
                COGSform_All.TextBox_Results10.Value = Format(.Cells(.Range(strAddress).Row, 16).Value, "$#,##0.00;($#,##0.00)")
                COGSform_All.TextBox_Results11.Value = Format(.Cells(.Range(strAddress).Row, 17).Value, "$#,##0.00;($#,##0.00)")
                COGSform_All.TextBox_Results12.Value = Format(.Cells(.Range(strAddress).Row, 18).Value, "$#,##0.00;($#,##0.00)")
                COGSform_All.TextBox_Results13.Value = Format(.Cells(.Range(strAddress).Row, 19).Value, "$#,##0.00;($#,##0.00)")
                COGSform_All.TextBox_Results14.Value = Format(.Cells(.Range(strAddress).Row, 20).Value, "$#,##0.00;($#,##0.00)")
                total = total + .Cells(.Range(strAddress).Row, i).Value
            End With
            GoTo EndLoop
        End If
    Next l
      Next


EndLoop:
    
End Sub
 
Upvote 0
I am also confused.
You can put your original but complete code.

Which cells do you want to add and where do you want to put the result?
 
Upvote 0
Hi,

I didn't explained it well what I want to add all boxes from 9-20 to show in the new textbox i added called TextboxYTD.

here is the orginal code:

Code: 
Private Sub ListBox_Results_Click()'Go to selection on sheet when result is clicked


Dim strAddress As String
Dim l As Long


    For l = 0 To ListBox_Results.ListCount
        If ListBox_Results.Selected(l) = True Then
            strAddress = ListBox_Results.List(l, 1)
            ActiveSheet.Range(strAddress).Select
            'Populate textboxes with results
            With ActiveSheet
                COGSform_All.TextBox_Results1.Value = .Cells(.Range(strAddress).Row, 2).Value
                COGSform_All.TextBox_Results2.Value = .Cells(.Range(strAddress).Row, 1).Value
                COGSform_All.TextBox_Results3.Value = Format(.Cells(.Range(strAddress).Row, 9).Value, "$#,##0.00;($#,##0.00)")
                COGSform_All.TextBox_Results4.Value = Format(.Cells(.Range(strAddress).Row, 10).Value, "$#,##0.00;($#,##0.00)")
                COGSform_All.TextBox_Results5.Value = Format(.Cells(.Range(strAddress).Row, 11).Value, "$#,##0.00;($#,##0.00)")
                COGSform_All.TextBox_Results6.Value = Format(.Cells(.Range(strAddress).Row, 12).Value, "$#,##0.00;($#,##0.00)")
                COGSform_All.TextBox_Results7.Value = Format(.Cells(.Range(strAddress).Row, 13).Value, "$#,##0.00;($#,##0.00)")
                COGSform_All.TextBox_Results8.Value = Format(.Cells(.Range(strAddress).Row, 14).Value, "$#,##0.00;($#,##0.00)")
                COGSform_All.TextBox_Results9.Value = Format(.Cells(.Range(strAddress).Row, 15).Value, "$#,##0.00;($#,##0.00)")
                COGSform_All.TextBox_Results10.Value = Format(.Cells(.Range(strAddress).Row, 16).Value, "$#,##0.00;($#,##0.00)")
                COGSform_All.TextBox_Results11.Value = Format(.Cells(.Range(strAddress).Row, 17).Value, "$#,##0.00;($#,##0.00)")
                COGSform_All.TextBox_Results12.Value = Format(.Cells(.Range(strAddress).Row, 18).Value, "$#,##0.00;($#,##0.00)")
                COGSform_All.TextBox_Results13.Value = Format(.Cells(.Range(strAddress).Row, 19).Value, "$#,##0.00;($#,##0.00)")
                COGSform_All.TextBox_Results14.Value = Format(.Cells(.Range(strAddress).Row, 20).Value, "$#,##0.00;($#,##0.00)")
               
            End With
            GoTo EndLoop
        End If
    Next l
     


EndLoop:
    
End Sub

thanks.
 
Upvote 0
Actually after i posted last one i realized I can you add total column in spreadsheet and added a line a code with that and it worked.

thank you so much. but anyway it's good to know if possible just to add textboxes.

here the new code with the line I added in (red).

Code: 
Private Sub ListBox_Results_Click()'Go to selection on sheet when result is clicked


Dim strAddress As String
Dim l As Long


 
    For l = 0 To ListBox_Results.ListCount
        If ListBox_Results.Selected(l) = True Then
            strAddress = ListBox_Results.List(l, 1)
            ActiveSheet.Range(strAddress).Select
            'Populate textboxes with results
            With ActiveSheet
                COGSform_All.TextBox_Results1.Value = .Cells(.Range(strAddress).Row, 2).Value
                COGSform_All.TextBox_Results2.Value = .Cells(.Range(strAddress).Row, 1).Value
                'COGSform_All.TextBox_Results1.Value = .Cells(.Range(strAddress).Row, 1).Value
                'COGSform_All.TextBox_Results2.Value = .Cells(.Range(strAddress).Row, 2).Value
                COGSform_All.TextBox_Results3.Value = Format(.Cells(.Range(strAddress).Row, 9).Value, "$#,##0.00;($#,##0.00)")
                COGSform_All.TextBox_Results4.Value = Format(.Cells(.Range(strAddress).Row, 10).Value, "$#,##0.00;($#,##0.00)")
                COGSform_All.TextBox_Results5.Value = Format(.Cells(.Range(strAddress).Row, 11).Value, "$#,##0.00;($#,##0.00)")
                COGSform_All.TextBox_Results6.Value = Format(.Cells(.Range(strAddress).Row, 12).Value, "$#,##0.00;($#,##0.00)")
                COGSform_All.TextBox_Results7.Value = Format(.Cells(.Range(strAddress).Row, 13).Value, "$#,##0.00;($#,##0.00)")
                COGSform_All.TextBox_Results8.Value = Format(.Cells(.Range(strAddress).Row, 14).Value, "$#,##0.00;($#,##0.00)")
                COGSform_All.TextBox_Results9.Value = Format(.Cells(.Range(strAddress).Row, 15).Value, "$#,##0.00;($#,##0.00)")
                COGSform_All.TextBox_Results10.Value = Format(.Cells(.Range(strAddress).Row, 16).Value, "$#,##0.00;($#,##0.00)")
                COGSform_All.TextBox_Results11.Value = Format(.Cells(.Range(strAddress).Row, 17).Value, "$#,##0.00;($#,##0.00)")
                COGSform_All.TextBox_Results12.Value = Format(.Cells(.Range(strAddress).Row, 18).Value, "$#,##0.00;($#,##0.00)")
                COGSform_All.TextBox_Results13.Value = Format(.Cells(.Range(strAddress).Row, 19).Value, "$#,##0.00;($#,##0.00)")
                COGSform_All.TextBox_Results14.Value = Format(.Cells(.Range(strAddress).Row, 20).Value, "$#,##0.00;($#,##0.00)")
[COLOR=#ff0000]                COGSform_All.TextBox_Results15.Value = Format(.Cells(.Range(strAddress).Row, 21).Value, "$#,##0.00;($#,##0.00)")[/COLOR]
[COLOR=#ff0000]                [/COLOR]
            End With
            GoTo EndLoop
        End If
    Next l
      


EndLoop:
    
End Sub
 
Upvote 0
josros60 said:
Actually after i posted last one i realized I can you add total column in spreadsheet and added a line a code with that and it worked.

thank you so much. but anyway it's good to know if possible just to add textboxes.

here the new code with the line I added in (red).

Code: 
Private Sub ListBox_Results_Click()'Go to selection on sheet when result is clicked


Dim strAddress As String
Dim l As Long


 
    For l = 0 To ListBox_Results.ListCount
        If ListBox_Results.Selected(l) = True Then
            strAddress = ListBox_Results.List(l, 1)
            ActiveSheet.Range(strAddress).Select
            'Populate textboxes with results
            With ActiveSheet
                COGSform_All.TextBox_Results1.Value = .Cells(.Range(strAddress).Row, 2).Value
                COGSform_All.TextBox_Results2.Value = .Cells(.Range(strAddress).Row, 1).Value
                'COGSform_All.TextBox_Results1.Value = .Cells(.Range(strAddress).Row, 1).Value
                'COGSform_All.TextBox_Results2.Value = .Cells(.Range(strAddress).Row, 2).Value
                COGSform_All.TextBox_Results3.Value = Format(.Cells(.Range(strAddress).Row, 9).Value, "$#,##0.00;($#,##0.00)")
                COGSform_All.TextBox_Results4.Value = Format(.Cells(.Range(strAddress).Row, 10).Value, "$#,##0.00;($#,##0.00)")
                COGSform_All.TextBox_Results5.Value = Format(.Cells(.Range(strAddress).Row, 11).Value, "$#,##0.00;($#,##0.00)")
                COGSform_All.TextBox_Results6.Value = Format(.Cells(.Range(strAddress).Row, 12).Value, "$#,##0.00;($#,##0.00)")
                COGSform_All.TextBox_Results7.Value = Format(.Cells(.Range(strAddress).Row, 13).Value, "$#,##0.00;($#,##0.00)")
                COGSform_All.TextBox_Results8.Value = Format(.Cells(.Range(strAddress).Row, 14).Value, "$#,##0.00;($#,##0.00)")
                COGSform_All.TextBox_Results9.Value = Format(.Cells(.Range(strAddress).Row, 15).Value, "$#,##0.00;($#,##0.00)")
                COGSform_All.TextBox_Results10.Value = Format(.Cells(.Range(strAddress).Row, 16).Value, "$#,##0.00;($#,##0.00)")
                COGSform_All.TextBox_Results11.Value = Format(.Cells(.Range(strAddress).Row, 17).Value, "$#,##0.00;($#,##0.00)")
                COGSform_All.TextBox_Results12.Value = Format(.Cells(.Range(strAddress).Row, 18).Value, "$#,##0.00;($#,##0.00)")
                COGSform_All.TextBox_Results13.Value = Format(.Cells(.Range(strAddress).Row, 19).Value, "$#,##0.00;($#,##0.00)")
                COGSform_All.TextBox_Results14.Value = Format(.Cells(.Range(strAddress).Row, 20).Value, "$#,##0.00;($#,##0.00)")
[COLOR=#ff0000]                COGSform_All.TextBox_Results15.Value = Format(.Cells(.Range(strAddress).Row, 21).Value, "$#,##0.00;($#,##0.00)")[/COLOR]


[COLOR=#333333]TextboxTotal  = [/COLOR]COGSform_All.TextBox_Results3.Value + COGSform_All.TextBox_Results4.Value + COGSform_All.TextBox_Results5.Value + _
                        COGSform_All.TextBox_Results6.Value + COGSform_All.TextBox_Results7.Value
            End With
            GoTo EndLoop
        End If
    Next l
      


EndLoop:
    
End Sub
Click to expand...

I still don't understand what you need, but see that you already solved it.
Another attempt, I put a sum, is what you need?
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Solved
Help adding total from rows to columns.
Replies
6
Views
394
Peter_SSs
Peter_SSs
J
  • Question Question
Invalid qualifier
Replies
9
Views
533
josros60
J
J
  • Solved
Textbox Editable
2
Replies
12
Views
997
smozgur
smozgur
Johnny Thunder
  • Question Question
VBA Help - Code does not run consistently - Is there an issue?
Replies
1
Views
1K
Johnny Thunder
Johnny Thunder
jski21
  • Solved
VBA Code for Table From Specific Range in Multiple Worksheets Not Working
Replies
3
Views
2K
DanteAmor
DanteAmor

Forum statistics

Threads
1,224,820
Messages
6,181,157
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top