Populate Userform Textboxes from Range

urobee

Board Regular
Joined
Jan 30, 2015
Messages
98
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
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:

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
So you have 40 multipages and each page has 30 Textboxes?
Wow.

Please explain what your ultimate goal is.

Your scripts shown only seem to be loading values into the UserForm.

What will you do with all this data after it's loaded into the UserForm?

Or do you mean you have one multipage with 40 tabs on the Multipage.

A Multipage is like a Workbook with as many sheets as you want.
 
Last edited:
Upvote 0
RsBqhAJ
Here is an image from my Userform:
https://imgur.com/a/VeDmPnr

There is one multipage on the Userform and this multipage has tons of tabs (i named these tabs to "page" on the original post).
(On the picture You can see only 6 tabs.)

This will be a database and SPC chart viewer for Quality purposes in my Company. (SPC means in Quality Assurance: Statistical Process Control)
Every parameter has own column on the worksheet and has own tab on the Userform.
So every textbox has its own cell on the worksheet and vice versa.
The User will load the previously entered data from the worksheet to the Userform's corresponding tab and will enter some new data then click the OK button, so the data will be stored on the worksheet.
I'm know there are some much more easier way to enter the data, but I need to do this in this way through the Userform.
 
Last edited:
Upvote 0
This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.
 
Upvote 0
Thanks for Your answers by the way!

Maybe I ask the question in too compicated style. So it's not Your fault of course! :)

BUT! I checked the code once more and i found the mistake:
I set the textboxes name and the variants in a wrong way...

This was the original code and the biggest mistake:
Code:
If ctl.Name Like "tb_" & y & "_1*" Then

And the working code:
Code:
If ctl.Name Like "tb_1_1_" & i Then

Here is the working code with the variants:
Code:
x = MultiPage1.ValueFor i = 1 To 30
For Each ctl In Me.Controls
If ctl.Name Like "tb_1_1_" & i 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 IN THE ORIGINAL CODE'''  



End Select
End If
Next ctl
Next i
 
Upvote 0
Glad you figured out that piece of the puzzle.
I cannot imagine having One Multipage with a whole mass amount of tabs with a lot of textboxes on each tab.

I wish you the best at getting all this running the way you want.
 
Upvote 0

Forum statistics

Threads
1,223,264
Messages
6,171,081
Members
452,377
Latest member
bradfordsam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top