Hy,
(It will be long... )
I have a sheet with some data in columns like this example, but the actual worksheet has 30 rows and 40 cols:[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]15[/TD]
[TD][/TD]
[TD]4[/TD]
[TD][/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]18[/TD]
[TD][/TD]
[TD]85[/TD]
[TD][/TD]
[TD]27[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD]47[/TD]
[TD][/TD]
[TD]57[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]30[/TD]
[TD][/TD]
[TD]67[/TD]
[TD][/TD]
[TD]29[/TD]
[/TR]
</tbody>[/TABLE]
(yes, i wanted to populate the numbers from every second column)
The Userform has a Multipage with the exact same amount of Pages as many columns the Worksheet has.
Every Page (on the multipage) has 30 textboxes where i wanted to populate the data from the sheet column-by-column.
Example:
Page 1 data: B1:B30
Page 2 data: D1:D30
Page 3 data: F1:F30
etc...
I have a working code for this right now, but this code is too long and "hardcoded"
plus i must create all the textboxes on every page...
So my idea is:
I created only the first 30 textboxes in a Frame on a separated area of the workbook and when the userform is appeared, the Frame with the textboxes just moved to the right place on the multipage area. In this way enough to make the 30 textboxes those appears on every page of the multipage area.
Here is a small part of my original (Long) code:
All of the data populated when the userform appeared
My idea for the new code is (with 3 columns for example):
Just the actual needed data appears on the Page.
The problem: The data populated only from the Column B, when I select the first page. The rest of the pages doesn't change those show the data from the column B.
I'm totally stuck on this, please help me if it possible. Thanks!
(It will be long... )
I have a sheet with some data in columns like this example, but the actual worksheet has 30 rows and 40 cols:[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]15[/TD]
[TD][/TD]
[TD]4[/TD]
[TD][/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]18[/TD]
[TD][/TD]
[TD]85[/TD]
[TD][/TD]
[TD]27[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD]47[/TD]
[TD][/TD]
[TD]57[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]30[/TD]
[TD][/TD]
[TD]67[/TD]
[TD][/TD]
[TD]29[/TD]
[/TR]
</tbody>[/TABLE]
(yes, i wanted to populate the numbers from every second column)
The Userform has a Multipage with the exact same amount of Pages as many columns the Worksheet has.
Every Page (on the multipage) has 30 textboxes where i wanted to populate the data from the sheet column-by-column.
Example:
Page 1 data: B1:B30
Page 2 data: D1:D30
Page 3 data: F1:F30
etc...
I have a working code for this right now, but this code is too long and "hardcoded"
plus i must create all the textboxes on every page...
So my idea is:
I created only the first 30 textboxes in a Frame on a separated area of the workbook and when the userform is appeared, the Frame with the textboxes just moved to the right place on the multipage area. In this way enough to make the 30 textboxes those appears on every page of the multipage area.
Here is a small part of my original (Long) code:
All of the data populated when the userform appeared
Code:
Private Sub btn_Load_Click()
Dim ctl As Control
Dim A, B, C As Long
For Each ctl In Me.Controls
If ctl.Name Like "tb_1_1_*" Then
A = A + 1
ctl.Value = Worksheets("Journal").Range("B6:B35")(A)
End If
If ctl.Name Like "tb_1_2_*" Then
B = B + 1
ctl.Value = Worksheets("Journal").Range("C6:C35")(B)
End If
If ctl.Name Like "tb_2_1_*" Then
C = C + 1
ctl.Value = Worksheets("Journal").Range("D6:D35")(C)
End If
'''REST OF THE CODE...'''
Next ctl
End sub
My idea for the new code is (with 3 columns for example):
Just the actual needed data appears on the Page.
Code:
Private Sub MultiPage1_Change() Dim ctl As Control
Dim A As Long
Dim x As Integer
x = MultiPage1.Value
y = x + 1
For Each ctl In Me.Controls
If ctl.Name Like "tb_" & y & "_1*" Then
A = A + 1
Select Case x
Case 0
ctl.Value = Worksheets("Journal").Range("B6:B35")(A)
Case 1
ctl.Value = Worksheets("Journal").Range("D6:D35")(A)
Case 2
ctl.Value = Worksheets("Journal").Range("F6:F35")(A)
'''MORE CASES'''
End Select
End If
Next ctl
End Sub
The problem: The data populated only from the Column B, when I select the first page. The rest of the pages doesn't change those show the data from the column B.
I'm totally stuck on this, please help me if it possible. Thanks!
Last edited: