I have 4 separate user forms that have a variable number of textboxes and labels based on a user defined parameter. For this question I will be looking at one in particular, however let me first list my global variables:
Option Explicit
Public BuildingNumber As Long
Public UnitNumber As Long
Public FixtureNumber As Long
Public array1() As String
Public array2() As String
Public array3() As String
Public array4() As String
At the very bottom is my current code for my first userform. The rest of my userforms are fairly similar in terms of code they just operate for different constraints. I am trying to set the value entered into txtBox 1 through however many I have get saved into an array that then is used when i click on a commandbutton on the last userform which has the following code:
Private Sub CommandButton1_Click()
Dim i As Long
Dim j As Long
Dim s As Long
Dim iFirst As String
Dim iLast As String
Dim iRange As Range
s = 0
Cells(5, 1).Value = "Units"
For i = 1 To BuildingNumber
Cells(4, 1 + i + s).Value = array1(i)
iFirst = Selection.Offset(4, 1 + i + s).Address
iLast = Selection.Offset(4, 1 + i + s + array2(i)).Address
Set iRange = Range(iFirst & ":" & iLast)
iRange.Merge
For j = 1 To array2(i)
Cells(5, 1 + j + s).Value = "Floor " & j
s = s + j
Next j
Next i
End Sub
Array1 in the above code is my building name and array2 is the number of floors that building has. Essentially I am trying to make it look like the below table except the building name gets merged based on the number of floors that building has. So Building 1 would be merged with the next 3 cells and building 2 would merge with the next 2 cells. However the number of floors changes from projects to project so I want it to be user defined.
I apologize if this is confusing, if it is please let me know and will try to make my question more concise. Thank you for helping with this!
Option Explicit
Private Sub UserForm_Initialize()
Dim i As Long
BuildingNumber = InputBox("How Many Buildings Have Units?", "Enter Building Number")
UnitNumber = InputBox("How Many Unit Types Are There on This Job?", "Enter Unit Number")
FixtureNumber = InputBox("How Many Fixture Types Are There in Units?", "Enter Fixture 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 And i <= 50 Then
.Height = 20
.Width = 75
.Left = 300
.Top = 20 * (i - 25) * 1
ElseIf 50 < i And i <= 75 Then
.Height = 20
.Width = 75
.Left = 500
.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 And i <= 50 Then
.Height = 20
.Width = 75
.Left = 220
.Top = 20 * (i - 25) * 1
ElseIf 50 < i And i <= 75 Then
.Height = 20
.Width = 75
.Left = 420
.Top = 20 * (i - 50) * 1
Else
End If
End With
Next i
If i <= 25 Then
UserForm1.Height = 640
UserForm1.Width = 220
ElseIf 25 < i And i <= 50 Then
UserForm1.Height = 640
UserForm1.Width = 420
ElseIf 50 < i And i <= 75 Then
UserForm1.Height = 640
UserForm1.Width = 620
Else
End If
End Sub
Private Sub CommandButton1_Click()
UserForm2.Show
End Sub
Option Explicit
Public BuildingNumber As Long
Public UnitNumber As Long
Public FixtureNumber As Long
Public array1() As String
Public array2() As String
Public array3() As String
Public array4() As String
At the very bottom is my current code for my first userform. The rest of my userforms are fairly similar in terms of code they just operate for different constraints. I am trying to set the value entered into txtBox 1 through however many I have get saved into an array that then is used when i click on a commandbutton on the last userform which has the following code:
Private Sub CommandButton1_Click()
Dim i As Long
Dim j As Long
Dim s As Long
Dim iFirst As String
Dim iLast As String
Dim iRange As Range
s = 0
Cells(5, 1).Value = "Units"
For i = 1 To BuildingNumber
Cells(4, 1 + i + s).Value = array1(i)
iFirst = Selection.Offset(4, 1 + i + s).Address
iLast = Selection.Offset(4, 1 + i + s + array2(i)).Address
Set iRange = Range(iFirst & ":" & iLast)
iRange.Merge
For j = 1 To array2(i)
Cells(5, 1 + j + s).Value = "Floor " & j
s = s + j
Next j
Next i
End Sub
Array1 in the above code is my building name and array2 is the number of floors that building has. Essentially I am trying to make it look like the below table except the building name gets merged based on the number of floors that building has. So Building 1 would be merged with the next 3 cells and building 2 would merge with the next 2 cells. However the number of floors changes from projects to project so I want it to be user defined.
| Building 1 | Building 2 | ||||||
| Units | Floor 1 | Floor 2 | Floor 3 | Floor 4 | Floor 1 | Floor 2 | Floor 3 |
I apologize if this is confusing, if it is please let me know and will try to make my question more concise. Thank you for helping with this!
Option Explicit
Private Sub UserForm_Initialize()
Dim i As Long
BuildingNumber = InputBox("How Many Buildings Have Units?", "Enter Building Number")
UnitNumber = InputBox("How Many Unit Types Are There on This Job?", "Enter Unit Number")
FixtureNumber = InputBox("How Many Fixture Types Are There in Units?", "Enter Fixture 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 And i <= 50 Then
.Height = 20
.Width = 75
.Left = 300
.Top = 20 * (i - 25) * 1
ElseIf 50 < i And i <= 75 Then
.Height = 20
.Width = 75
.Left = 500
.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 And i <= 50 Then
.Height = 20
.Width = 75
.Left = 220
.Top = 20 * (i - 25) * 1
ElseIf 50 < i And i <= 75 Then
.Height = 20
.Width = 75
.Left = 420
.Top = 20 * (i - 50) * 1
Else
End If
End With
Next i
If i <= 25 Then
UserForm1.Height = 640
UserForm1.Width = 220
ElseIf 25 < i And i <= 50 Then
UserForm1.Height = 640
UserForm1.Width = 420
ElseIf 50 < i And i <= 75 Then
UserForm1.Height = 640
UserForm1.Width = 620
Else
End If
End Sub
Private Sub CommandButton1_Click()
UserForm2.Show
End Sub
