How many userforms can excel handle?

[mykulpasskwa]

Long story short I'm still about one years old using VBA and I'm making a spreadsheet with multiple semesters in column A. In column B is tuition, and in column C is fees. I just started making this so I haven't gotten very far, but I currently have it set up where when you click on cell C1 a userform ("Fees Form") appears and there's 8 text boxes that adds different fees together. I have the inputs stored out-of-sight in individual cells so that the fees will load each time the form is opened for that semester. I only have this set up for one cell so far, but I was wondering how to best execute this across many semesters. So the next semester would be in C2, the next C3, so on and so forth. Can I use one form ("Fees Form") and when I click on a specific row (say C2) it will load a that semesters fees, and if I click on a different row (C3) then it will load that semesters fees? Or do I need to use a different form for each semester?

Because I will need a form to calculate tuition, another form to calculate scholarships, another for grants, another for xyz...it looks like I'll need somewhere between 36-48 forms if I need a different form for each category in each semester. This doesn't seem practical to me and also I'm not sure how excel will handle it. Any thoughts?

	A	B	C
	Semester	Tuition	Fees
1	Fall	1000	100
2	Spring	2000	200
3	Summer 1	3000	300
4	Summer 2	4000	400

<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[My Aswer Is This]

So if I partially understand.

You want to select a certain cell in column A and then you want some values loaded into The UserForms 8 Textboxes.

Where are these values stored on the sheet

So if you click on A5 what values will be loaded into the 8 Textboxes

And what are the names of these Textboxes?

And after you load all these values into the Userform what do you plan to do next?

I assume you plan to in some way modify them and then send those values back into the sheet.

We should surely not need 30 or more UserForms.

Give me a example.

You select A3 and what exactly should happen.
And lets assume we will be only using One UserForm.

You click on A3 a Userform opens and loads the 8 Textboxes with what data and from where does it get this data

And be specific from what sheet and from what cell and what is the name of each of the 8 Textboxes.

Say something like this.

I click on A8 Then the value in B8 is loaded into Textbox1 and the value in Range(C8) is loaded into TextBox2

And then again what happens next? Do you just plan to view this data in the Userform or do something else with it.

Specifics are always important for us to help you.

Do not say a cell or the Other sheet or column Grades

Say Range("B8") and sheet named "Data" and Column 8

 

[mykulpasskwa]

Column A is just text. Column B is tuition and when you click B1 a tuition form opens with its own set of values, B2 is another form and has its own set of values. Column C is a fees form and when you click C1, C2, C3, etc they all have their own set of values.

I can't tell if the picture I'm trying to attach is actually attaching, but basically the fees form opens up with 8 text boxes named UgGradFeeTextBox, StuActFeeTxtBox, StuCentFeeTxtBox, etc. These text boxes get added together and the total gets entered into a cell for a specific semester/term at its ControlSource

I would like to have 1 form for Fees, but depending on which cell I click it will load that information so Summer 1 will fill the text boxes with different values than Summer 2, and I don't really know thats possible if it is. So the only other thing I can imagine is having a different user form for each semester.

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


Set ws = Worksheets("Ch. 33 YR") 'sets the
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

 

[mykulpasskwa]

Heres a link to the image

https://imgur.com/a/IeVJ3D3

 

[My Aswer Is This]

Yes I was able to see your image.

I have been using UserForms a long time and have never needed to use more then One Userform
Now there is a control you can use it's called Multipage

Multipage is like a Worksheet but on a Userform

Now You can have numerous Multipage Pages all on the same the same Userform
Just like you have numerous sheets in your Workbook.

The multipage has tab's across the top just like a workbook has sheet tabs across the bottom of the workbook

So you can have one Multipage on one Userform
And this Multipage has as many Multipage Pages as you want just like a Workbook can have many sheets
Click on the Multipage Page tab and you now see what looks like a new Userform. You can have all the controls you need on that Multipage Page.


Now I'm one for not giving Textboxes specific names.
I keep the default name like Textbox1 and Textbox2 and Textbox3

That way you can have ten Textboxes for example and use them how you want according to the way you write the script you put in your activate Button.

But now if you used Multipages you would need to use specific Textbox names.


What exactly would be the different's on each Userform?

Can you not Use standard default names for your Textboxes and just write the script to send this data to the correct place.

But if your sure you need 30 some different looking UserForms I would look into Multipages

You can have one Userform look like 30 different Userforms by just adding more pages to your UserForm.

Just like not needing 30 Workbooks when you can use 30 sheets on one Workbook.

To answer your original quest of how many UserForms can Excel Handle I'm not sure of that answer but I'm sure it more then 30 or even 50


Take a quick look at the Userform Multipage control you may be surprised if you have never used it.

Create a Empty UserForm and select Multipage from the toolbox and you will see.
Put all the controls you want on each separate Multipage page

Just think of it like the Multipage is a Workbook and each Multipage page is a different Worksheet.

Hope this helped you some what.

 

[My Aswer Is This]

Here is a example of how I addded up all the Textbox value and entered the results into a range.

Now in this example I have 8 Textboxes using default names Textbox1 to Textbox8

Private Sub CommandButton2_Click()
'Modified  5/10/2019  12:38:17 PM  EDT
Dim i As Long
Dim ans As Long
    For i = 1 To 8
        ans = ans + Controls("Textbox" & i).Value
    Next
ActiveSheet.Range("A1").Value = ans
End Sub

 

[My Aswer Is This]

Now if you just like naming all your Textboxes you can easily loop through like this:

This script adds up all the values in all Textboxes on your Userform
No matter what the name of the Textbox.

Private Sub CommandButton2_Click()
'Modified  5/10/2019  12:51:17 PM  EDT
Dim ans As Long
    For Each Control In Me.Controls
    If TypeName(Control) = "TextBox" Then
        ans = ans + Control.Value
        End If
    Next
ActiveSheet.Range("A1").Value = ans
End Sub

 

[My Aswer Is This]

Now if you use Multipages on your Userform

You can add up all the Textbox values on any particular Multipage page like this no matter the name of the Textbox.

Private Sub CommandButton3_Click()
'Modified  5/10/2019  1:00:42 PM  EDT
Dim ans As Long
For Each Control In Me.MultiPage1.Pages(0).Controls
    If TypeName(Control) = "TextBox" Then
        ans = ans + Control.Value
        End If
    Next
ActiveSheet.Range("A1").Value = ans
End Sub

Multipage pages always start out as 0
So the first Multipage page is 0 and the next one is 1 and so on.

 

[mykulpasskwa]

Thanks so much! I just got back to work and was able to go over this a bit. I didn't realize multipage was even a thing. I think that very much solves my problem! I'll look this over in more detail, but seriously thank you so much!

 

[My Aswer Is This]

Glad you responded back. I find Multipages very helpful I have One UserForm with One Multipage which has about 10 Multipage pages. I give the Multipage Page tab a name related to what is on that Multipage page.

Now common controls like a button to close the UserForm I put on the UserForm outside the Multipage that way the close UserForm button is always visible and I only need one.

You can Tab through your Multipage Pages by clicking on one Multipage Page tab then use your left and right arrow keys on your keyboard to move from one Multipage Page to the next

You can also color your Multipage pages if you want.

If your interested in how to do that let me know.

I actually store my UserForm in My Excel Personal Folder that way it's available on any Workbook

I just open the Userform with a script also stored in my Personal folder. And I activate the opening of the UserForm using a Shortcut key which runs that small script like:UserForm1.Show Modeless

Using Modeless allows you to work on your Worksheet with the UserForm displayed.

But then you may already know this.
Take care.