Adding a year today total textbox.

josros60

Well-known Member
Joined
Jun 27, 2010
Messages
778
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

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
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
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

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

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

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