UserForm that changes based off another UserForm

[nsook]

I am trying to create a Unit Matrix user form for multiple buildings with different building types. This means that each building can have a different quantity of floors and different unit types in each building. For an example I have attached an image of what a final product would look like.

I have been brainstorming how the best way to do this as this is only my third VBA project and first time using UserForms. I am thinking the best way to do it would be to have a UserForm that asks the number of unit types on the job and the number of buildings on the job. When you hit generate it would open another UserForm that would have a label and text box for the quantity of buildings that were entered in the first UserForm as well as a text box and a spin button. For each building it would ask what the name of the building is and the number of floors in the building. This would be used to generate the number of cells needed in the table for the Unit Matrix. I'm not worried about the Unit names or where the units are located as that will change job to job and can be input manually. For an idea of what I am thinking for my second UserForm I have included a second attachment.

My biggest struggle is trying to figure out how to have the second UserForm change the number of labels/textboxs generated to reflect how many buildings the user input in the first UserForm. I appreciate any help/advice on this!

 

[Rosen]

The easiest way to pass data between userforms is directly. Set some public properties to the userform such as

Public NumberOfBuildings As Long
Public NumberOfUnitTypes As Long

Then before you call the Show method set them by calling the UserForm, exampe:

UserForm2.NumberOfBuildings = varNoB
UserForm2.NumberOfUnitTypes = varNoUT
UserForm2.Show

Where varNoB and varNoUT are set by the values entered into the first UserForm. Then you can call on the above values on the Initialization method to setup the form (add/update controls, etc.)

 

[nsook]

The easiest way to pass data between userforms is directly. Set some public properties to the userform such as

Public NumberOfBuildings As Long
Public NumberOfUnitTypes As Long

Then before you call the Show method set them by calling the UserForm, exampe:

UserForm2.NumberOfBuildings = varNoB
UserForm2.NumberOfUnitTypes = varNoUT
UserForm2.Show

Where varNoB and varNoUT are set by the values entered into the first UserForm. Then you can call on the above values on the Initialization method to setup the form (add/update controls, etc.)

Sorry for the delay in the reply but I wanted to work on my code a bit more and get a better idea of how my code is going to work. My idea has changed from what I initially had planned but I think my new idea will be more beneficial for my team. So right now I have this for UserForm 1:
Option Explicit

Private Sub UserForm_Initialize()
Dim i As Long
Dim BuildingNumber As Long
Dim BuildingList As Long
Dim Array1() As String
BuildingNumber = InputBox("How Many Buildings Have Units?", "Enter Building Number")

Dim txtB1 As Control
ReDim Array1(1 To BuildingNumber)
For i = 1 To BuildingNumber
Set txtB1 = Controls.Add("Forms.TextBox.1")
With txtB1
.Name = "txtBox" & i
.Height = 20
.Width = 75
.Left = 100
.Top = 20 * i * 1
Array1(i) = "txtBox" & i
End With

Next i

Dim lblL1 As Control
For i = 1 To BuildingNumber
Set lblL1 = Controls.Add("Forms.Label.1")
With lblL1
.Caption = "Building " & i & " Name"
.Name = "lbl" & i
.Height = 20
.Width = 75
.Left = 20
.Top = 20 * i * 1
End With
Next i
End Sub

Private Sub CommandButton1_Click()

UserForm2.Show

End Sub

and then for UserForm 2 I have:
Option Explicit

Private Sub UserForm_Initialize()
Dim i As Long
Dim Array2() As String
Dim BuildingNumber As Long
'BuildingNumber = InputBox("How Many Buildings Have Units?", "Enter Building Number")

Dim txtB2 As Control
ReDim Array2(1 To BuildingNumber)
For i = 1 To BuildingNumber
Set txtB2 = Controls.Add("Forms.Textbox.1")
With txtB2
.Name = "TxtBox" & i
.Height = 20
.Width = 50
.Left = 145
.Top = 20 * i * 1
Array2(i) = "TxtBox" & i
End With
Next i

Dim lblL2 As Control
For i = 1 To BuildingNumber
Set lblL2 = Controls.Add("Forms.Label.1")
With lblL2
.Caption = "Number of Floors in Building " & i
.Name = "Lbl" & i
.Height = 20
.Width = 125
.Left = 20
.Top = 20 * i * 1
End With
Next i
End Sub

Private Sub CommandButton1_Click()


End Sub

I am going to have the command button for my second userform run a macro that populates data based on the responses in each userform. I was trying to get the BuildingNumber response obtained in the first userform to be stored as a global variable so it could be referenced in my second userform without having to reask the user. I also want the response for BuildingNumber as well as the data stored in array1 and array2 to be global variables that I can reference to in my macro. I tried declaring them as public variables in my macro as shown below but when it goes to run my second userform it is giving me a runtime error 9 and says subscript out of range. I appreciate any help with this!

Option Explicit
Public BuildingNumber
Public array1
Public array2

 

[Rosen]

By defining BuildingNumber locally you are stopping VBA from looking to the globally defined BuildingNumber variable. Instead it is looking at your locally defined variable and trying to create an array from 1 To 0 (which isn't a thing and why it is throwing an error at you). If you remove the Dim BuildingNumber As Long statements (that is plural as you will need to do it in both userforms) and you should be good to go. (same with Array1 and Array2)

Hope that helps!

 

[nsook]

This solved my initial question however I wanted to ask a follow up before marking this as answered completely. I modified the above code a bit and can be seen below. I am trying to make my text and label boxes automatically get moved to a new column in the user form when it exceeds a certain value. When i = 26 it properly moves from the bottom of the first column and creates a second column as I was expecting, however when i=51 it does not move to a new column and ends up going outside the dimensions of my user form. Can you see anything in the code that is causing this to not work properly? Additionally is there something I could put at the end of the userform to automatically size the user form to best fit the number of text boxes and label boxes? Thank you for your help!

Private Sub UserForm_Initialize()
Dim i As Long
'Dim BuildingNumber As Long
Dim BuildingList As Long
Dim array1() As String
BuildingNumber = InputBox("How Many Buildings Have Units?", "Enter Building Number")

Dim txtB1 As Control
ReDim array1(1 To BuildingNumber)
For i = 1 To BuildingNumber
Set txtB1 = Controls.Add("Forms.TextBox.1")
With txtB1
.Name = "txtBox" & i
If i <= 25 Then
.Height = 20
.Width = 75
.Left = 100
.Top = 20 * i * 1
ElseIf 25 < i <= 50 Then
.Height = 20
.Width = 75
.Left = 300
.Top = 20 * (i - 25) * 1
ElseIf 50 < i <= 75 Then
.Height = 20
.Width = 75
.Left = 550
.Top = 20 * (i - 50) * 1
Else
End If
array1(i) = "txtBox" & i
End With

Next i

Dim lblL1 As Control
For i = 1 To BuildingNumber
Set lblL1 = Controls.Add("Forms.Label.1")
With lblL1
.Caption = "Building " & i & " Name"
.Name = "lbl" & i
If i <= 25 Then
.Height = 20
.Width = 75
.Left = 20
.Top = 20 * i * 1
ElseIf 25 < i <= 50 Then
.Height = 20
.Width = 75
.Left = 220
.Top = 20 * (i - 25) * 1
ElseIf 50 < i <= 75 Then
.Height = 20
.Width = 75
.Left = 470
.Top = 20 * (i - 50) * 1
Else
End If
End With
Next i
End Sub

 

[Rosen]

The statement 'ElseIf 25 < i <= 50 Then' is true for 51+ Change it to 'ElseIf i <= 50 Then' and it will stop being true for 51 and will move on to the next 'ElseIf' line. There is no need to compare 25 < i for if this was false it would have been captured in the prior If statement and never been compared in the following ElseIf statement. But if you really want to have it in there then you can write the statement as follows: ElseIf 25 < i And i <= 50 Then

Why this works this way? That will take bigger brained boovs then me.

 

[nsook]

This fixed it thank you so much!