Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Load data from worksheet into Userform
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member Nitehawkhp's Avatar
    Join Date
    Dec 2016
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Load data from worksheet into Userform

    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:

    Row Description Yes/No Distance
    1 Vendor User Response User Response
    2 Category User Response User Response
    3 Series User Response User Response
    4 Orientation User Response User Response

    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 by Nitehawkhp; Jun 13th, 2019 at 08:00 PM.

  2. #2
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    75,154
    Post Thanks / Like
    Mentioned
    62 Post(s)
    Tagged
    6 Thread(s)

    Default Re: Load data from worksheet into Userform

    Rod

    Try preceding Controls with Me.
    Code:
    Me.Controls("Data" & i) = Cells(i+1, 3)
    If posting code please use code tags.

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,203
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Load data from worksheet into Userform

    Are your textboxes called Data1, Data2 etc?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  4. #4
    New Member Nitehawkhp's Avatar
    Join Date
    Dec 2016
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Load data from worksheet into Userform

    Quote Originally Posted by Fluff View Post
    Are your textboxes called Data1, Data2 etc?
    Yes they are called data1, data2, etc.

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,203
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Load data from worksheet into Userform

    Can you please supply the full code that you are using and point out which part gets highlighted.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #6
    New Member Nitehawkhp's Avatar
    Join Date
    Dec 2016
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Load data from worksheet into Userform

    Quote Originally Posted by Fluff View Post
    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
                Controls("Data" & i) = Cells(i + 1, 3)
            Next i
        Application.ScreenUpdating = True
    
    
    
    
    End Sub
    Last edited by Nitehawkhp; Jun 14th, 2019 at 01:47 PM.

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,203
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Load data from worksheet into Userform

    Where is the "Validation" button located? and are the Data1 textboxes on the "ValidationResetButtonAdjustmentForm" userform?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  8. #8
    New Member Nitehawkhp's Avatar
    Join Date
    Dec 2016
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Load data from worksheet into Userform

    Quote Originally Posted by Fluff View Post
    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

  9. #9
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,820
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: Load data from worksheet into Userform

    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 ??
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  10. #10
    New Member Nitehawkhp's Avatar
    Join Date
    Dec 2016
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Load data from worksheet into Userform

    Quote Originally Posted by My Aswer Is This View Post
    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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •