Load data from worksheet into Userform

Nitehawkhp

New Member
Joined
Dec 2, 2016
Messages
37
I have a column of data in a worksheet and need to load that data into text boxes on a userform.
I have done this in the past, however, I can’t get the code to work in this new situation.

A sample of the worksheet data consists of the following:

RowDescriptionYes/NoDistance
1Vendor User Response User Response
2Category User Response User Response
3Series User Response User Response
4Orientation User Response User Response

<tbody>
</tbody>

I need a macro that will transfer the data into Userform

The Description column will be loaded into TextBoxes on a Userform called:

Textbox1
Textbox2
Textbox3
Textbox4

I have used the following code in a different worksheet and it works.

Code:
    Dim i As Integer
        Sheets("Players").Select
        Range("C2").Select
        ToCount = Range("G2")
    
        For i = 1 To ToCount
            Controls("Player" & i) = Cells(i + 1, 3)
        Next i
However, when I use it now I get a compile error
Sub or Function not defined

Code:
    Dim i As Integer
        Sheets("Data Validation").Select
        ToCount = Range("X1").Value
        
    
        For i = 1 To ToCount
            Controls("Data" & i) = Cells(i + 1, 3)
        Next i
I have changed the sheets name to match the new worksheet and,
range name for the data.

I have looked up controls and tried to figure out what was needed for that command, but
I don't understand.

Thanks for any help you can provide me.

Rod
 
Last edited:

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,464
Office Version
365
Platform
Windows
Rod

Try preceding Controls with Me.
Code:
Me.Controls("Data" & i) = Cells(i+1, 3)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,824
Office Version
365
Platform
Windows
Are your textboxes called Data1, Data2 etc?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,824
Office Version
365
Platform
Windows
Can you please supply the full code that you are using and point out which part gets highlighted.
 

Nitehawkhp

New Member
Joined
Dec 2, 2016
Messages
37
Can you please supply the full code that you are using and point out which part gets highlighted.
The bold italicized area is where the error happens.
The error is:

"Compile error:

Sub of Function not defined"


Code:
Sub Validation_Button()
'
'
'


    ValidationResetButtonAdjustmentForm.Show
    Dim i As Integer
        Sheets("Data Validation").Select
        ToCount = Range("X1").Value

        
    
        For i = 1 To ToCount
           [I][B] Controls("Data" & i) = [/B][/I]Cells(i + 1, 3)
        Next i
    Application.ScreenUpdating = True




End Sub
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,824
Office Version
365
Platform
Windows
Where is the "Validation" button located? and are the Data1 textboxes on the "ValidationResetButtonAdjustmentForm" userform?
 

Nitehawkhp

New Member
Joined
Dec 2, 2016
Messages
37
Where is the "Validation" button located? and are the Data1 textboxes on the "ValidationResetButtonAdjustmentForm" userform?
Fluff,

I just realized I have been using the wrong VB code.

This VB code was used in a previous situation to load data from a userform onto a worksheet, not filling the userform with data on a worksheet.
I'm so sorry for wasting your time on trying to solve this problem. However, I do have the following issue with this same userform and worksheet.
I'm trying to use the following code to load the userform named "ValidationResetButtonAdjustment" with data from the worksheet named "Data Validation."


Code:
    Data1.Value = Sheets("Data Validation").Range("X2").Value
    Data2.Value = Sheets("Data Validation").Range("X3").Value
    Data3.Value = Sheets("Data Validation").Range("X4").Value
    Data4.Value = Sheets("Data Validation").Range("X5").Value
    Data5.Value = Sheets("Data Validation").Range("X6").Value
    Data6.Value = Sheets("Data Validation").Range("X7").Value
    Data7.Value = Sheets("Data Validation").Range("X8").Value
    Data8.Value = Sheets("Data Validation").Range("X9").Value
    Data9.Value = Sheets("Data Validation").Range("X10").Value
    Data10.Value = Sheets("Data Validation").Range("X11").Value
    Data11.Value = Sheets("Data Validation").Range("X12").Value
    Data12.Value = Sheets("Data Validation").Range("X13").Value
    Data13.Value = Sheets("Data Validation").Range("X14").Value
When I execute this code I get the error "Run-time error '424': Object required"
Will you still help me solve this error, after wasting your time on the previous mistake?

Again I'm very sorry for asking for help with the wrong VB code.

Rod
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,728
Office Version
2013
Platform
Windows
I tried this line of code and it worked for me:
Data1.Value = Sheets("Data Validation").Range("X2").Value

Do you have a TextBox on your Userform Named Data1 ??
Do you have a sheet named Data Validation ??
 

Nitehawkhp

New Member
Joined
Dec 2, 2016
Messages
37
I tried this line of code and it worked for me:
Data1.Value = Sheets("Data Validation").Range("X2").Value

Do you have a TextBox on your Userform Named Data1 ??
Do you have a sheet named Data Validation ??
Yes. I do have a TextBox on my Userform Named Data1 and a sheet named Data Validation.


Rod
 

Watch MrExcel Video

Forum statistics

Threads
1,089,879
Messages
5,410,934
Members
403,335
Latest member
ddaveryos

This Week's Hot Topics

Top