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:

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. 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
59,920
Office Version
  1. 365
Platform
  1. Windows
Are your textboxes called Data1, Data2 etc?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
59,920
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
59,920
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
17,669
Office Version
  1. 2013
Platform
  1. 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
 

Forum statistics

Threads
1,136,314
Messages
5,675,028
Members
419,544
Latest member
MVPDoug

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
Top