Better way to use Multipage Textboxes

[mykulpasskwa]

I'm using a Multipage (13 pages) on a UserForm and on each page I have 8 text boxes. These text boxes get calculated and the resulting value is entered into a cell on the worksheet. I have one set of controls (Enter, Calculate, and Clear) on the UserForm section. The 13 pages represent semesters/terms and the 8 text boxes represent the fees for each semester. I'm trying to figure out the best way to enter different values on each page. I'm pretty new to VBA and this is the first time I'm using Multipage. I have two terms below as an example, but I was planning on doing the same thing for 13 more terms. In the future it may be more than 13--Im trying to add an option where you can insert or delete terms (rows) as needed. I know that it is very bloated, but I don't know how to make it more efficient. Any advice?

Private Sub FeesTotalBtn_Click()
    'Enter button that adds together all of the fees entered into the FeesForm
    Dim resp
    Dim ws As Worksheet
    Dim TandFBalance As Currency
    Dim TandFBalance2 As Currency


    Set ws = Worksheets("Ch. 33 YR")
    'Term 1
    Range("D4") = Val(UgGradFeeTxtBox.Text) + Val(StuActFeeTxtBox.Text) + Val(StuCentFeeTxtBox.Text) + Val(StuRecFeeTxtBox.Text) + Val(HlthPlnFeeTxtBox.Text) + Val(UHCSFeeTxtBox.Text) + Val(Misc1FeeTxtBox.Text) + Val(Misc2FeeTxtBox.Text) 'Adds the fees together
    ws.Range("A139") = UgGradFeeTxtBox.Value 'Saves Undergraduate or Graduate fee ''Check control source for data location
    ws.Range("B139") = StuActFeeTxtBox.Value 'Saves Student Activity fee ''Check control source for data location
    ws.Range("C139") = StuCentFeeTxtBox.Value 'Saves Student Activity fee ''Check control source for data location
    ws.Range("D139") = StuRecFeeTxtBox.Value 'Saves Student Activity fee ''Check control source for data location
    ws.Range("E139") = HlthPlnFeeTxtBox.Value 'Saves Student Activity fee ''Check control source for data location
    ws.Range("F139") = UHCSFeeTxtBox.Value 'Saves Student Activity fee ''Check control source for data location
    ws.Range("G139") = Misc1FeeTxtBox.Value 'Saves Student Activity fee ''Check control source for data location
    ws.Range("H139") = Misc2FeeTxtBox.Value 'Saves Student Activity fee ''Check control source for data location
    'Term 2
    Range("D5") = Val(UgGradFeeTxtBox2.Text) + Val(StuActFeeTxtBox2.Text) + Val(StuCentFeeTxtBox2.Text) + Val(StuRecFeeTxtBox2.Text) + Val(HlthPlnFeeTxtBox2.Text) + Val(UHCSFeeTxtBox2.Text) + Val(Misc1FeeTxtBox2.Text) + Val(Misc2FeeTxtBox2.Text) 'Adds the fees together
    ws.Range("A140") = UgGradFeeTxtBox2.Value
    ws.Range("B140") = StuActFeeTxtBox2.Value
    ws.Range("C140") = StuCentFeeTxtBox2.Value
    ws.Range("D140") = StuRecFeeTxtBox2.Value
    ws.Range("E140") = HlthPlnFeeTxtBox2.Value
    ws.Range("F140") = UHCSFeeTxtBox2.Value
    ws.Range("G140") = Misc1FeeTxtBox2.Value
    ws.Range("H140") = Misc2FeeTxtBox2.Value
    
    
    Unload FeesForm
    'Term1
    TandFBalance = Ch33YR.Range("C4") + Ch33YR.Range("D4") - Ch33YR.Range("E4") + (Ch33YR.Range("F4") * -1 - Ch33YR.Range("G4")) - Ch33YR.Range("H4") + Ch33YR.Range("I4")
    Ch33YR.Range("J4") = TandFBalance
    'Term2
    TandFBalance2 = Ch33YR.Range("C5") + Ch33YR.Range("D5") - Ch33YR.Range("E5") + (Ch33YR.Range("F5") * -1 - Ch33YR.Range("G5")) - Ch33YR.Range("H5") + Ch33YR.Range("I5")
    Ch33YR.Range("J5") = TandFBalance2
End Sub

 

[My Aswer Is This]

Here is a example of how you can do this easily.
I think I helped you once before on another part of this project.


Put this script in a Command Button on your Userform outside the Multipage

Now this script looks at all the TextBoxes on Multipage1 Page 0 which is the First Multipage page

My script enters the results in Range("A1") on the active sheet.

Now this is just a sample so you need to modify where the results are entered.

Now this script looks in all the Textboxes on the Multipage page. So make sure you have no textbox with Text in it or you will get a error

If this works for you check back with me if you need more help

Private Sub CommandButton2_Click()
'Modified  5/15/2019  9:02:00 PM  EDT
Dim ans As Long
Dim i As Long
i = 0
For Each Control In MultiPage1.Pages(i).Controls
    If TypeName(Control) = "TextBox" Then ans = ans + Control.Value
Next
Cells(1, 1).Value = ans
End Sub

 

[My Aswer Is This]

I do not understand what any of this is:

[LEFT][COLOR=#333333][FONT=monospace] Unload FeesForm
    'Term1
    TandFBalance = Ch33YR.Range("C4") + Ch33YR.Range("D4") - Ch33YR.Range("E4") + (Ch33YR.Range("F4") * -1 - Ch33YR.Range("G4")) - Ch33YR.Range("H4") + Ch33YR.Range("I4")
    Ch33YR.Range("J4") = TandFBalance
[/FONT][/COLOR][/LEFT]

Unload sounds like your closing the Userform and still want to continue doing something

 

[My Aswer Is This]

Now if you want to have some textbox's on your Multipage Page but you want to have text in those boxes then you will need to put all the textboxes you do want added up inside a Frame Control

Now this script only adds up the values for those Textboxes inside the Frame.

Note in the script it says Frame1

Now in each Multipage Page you would need a Frame with a different name and modify that script to the proper Frame number

You will see a Frame control in the Toolbox

Put the Frame on the multipage page and put your Textboxes you wanted added up inside this Frame

Doing things this way keeps you from having to refer to each Textbox by name.

Private Sub CommandButton2_Click()
'Modified  5/15/2019  10:35:40 PM  EDT
On Error GoTo M
Dim ans As Long
Dim i As Long
i = 0
For Each Control In MultiPage1.Pages(i).Frame1.Controls
    If TypeName(Control) = "TextBox" Then ans = ans + Control.Value
Next
Cells(1, 1).Value = ans
Exit Sub
M:
MsgBox "We had a problem" & vbNewLine & "You may have a empty Textbox or some other problem"
End Sub

 

[mykulpasskwa]

I do not understand what any of this is:

[LEFT][COLOR=#333333][FONT=monospace] Unload FeesForm
    'Term1
    TandFBalance = Ch33YR.Range("C4") + Ch33YR.Range("D4") - Ch33YR.Range("E4") + (Ch33YR.Range("F4") * -1 - Ch33YR.Range("G4")) - Ch33YR.Range("H4") + Ch33YR.Range("I4")
    Ch33YR.Range("J4") = TandFBalance
[/FONT][/COLOR][/LEFT]

Unload sounds like your closing the Userform and still want to continue doing something

Originally what I had done was set up the textbox to save to a cell, say C4, and then the control source was also C4. So when I type in a number it saves to cell C4 and then next time you open the form that value is still there. I'm sure it's not a great way to do that, but its the only way I could figure to do it.

 

[mykulpasskwa]

Now if you want to have some textbox's on your Multipage Page but you want to have text in those boxes then you will need to put all the textboxes you do want added up inside a Frame Control

Now this script only adds up the values for those Textboxes inside the Frame.

...

You did help me out, and you taught me what multipages were! Great to hear from you again.

Based on your description I think the Frame control option is the way for me to go. I just have to figure out how to do that!

I'll probably be posting a lot over the next few months. I'm trying to build a machine in a different language but I don't know the language.

Thanks again for the help and advice!

 

[My Aswer Is This]

Glad I was able to help you.
Think of a Frame as a Box.
And my script says look at all the textboxes inside the Box.

I'm always adding more things to my script.

Look at this addition and you will see IsNumeric This means it only add items if they are numeric.

So if the cell is left empty or someone accidently enters text the script will not attempt to add that non numeric value.

1+Cake would cause a error.

Private Sub CommandButton2_Click()
'Modified  5/16/2019  6:16:23 PM  EDT
On Error GoTo M
Dim ans As Long
Dim i As Long
i = 0
For Each Control In MultiPage1.Pages(i).Frame1.Controls
    If TypeName(Control) = "TextBox" And IsNumeric(Control.Value) Then ans = ans + Control.Value
Next
Cells(1, 1).Value = ans
Exit Sub
M:
MsgBox "We had a problem" & vbNewLine & "You may have a empty Textbox or some other problem"
End Sub