Results 1 to 9 of 9

Table data to labels in userform?

This is a discussion on Table data to labels in userform? within the Excel Questions forums, part of the Question Forums category; ...

  1. #1
    Board Regular
    Join Date
    Dec 2014
    Posts
    181

    Default Table data to labels in userform?

    Hi

    I have a table in a sheet.

    I want to import data from the table to labels in a userform.

    The table looks like this
    Column A = Names
    Column B = City
    Column C = Country

    NAMES | CITY | COUNTRY
    Oskar | stockholm | sweden
    Sven | london | england
    Jenny | paris | france
    Lucas | malmŲ | sweden
    Olle | newcastle | england


    I want some VBA code to import the names of those that live in Sweden for example.
    So the label in this example should look like this "Oskar, Lucas"
    In another label i want to import those that live in england for example.
    Like this "Sven, Olle"


    How would i do this?

  2. #2
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    9,096

    Default Re: Table data to labels in userform?

    We need to know the following

    1. UserForm Name
    2. Sheet Name where values are.
    3. Label Names
    4. Textbox name where you will need to put Country name to search for
    5. And are you sure you want these values put into a "Label"

    So if you have 20 Swedens you will need 20 different "Labels" or are you wanting all the Swedens into one "Label"

    And if you want to search for more then one country at a time you will need two textboxes to search for country name.

    So please provide the answer to all these question in specific details.


    And what do you plan to do with these labels after they are filled in?

    Are you sure you do not want these values put into a Listbox or Combobox?
    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"

  3. #3
    Board Regular
    Join Date
    Dec 2014
    Posts
    181

    Default Re: Table data to labels in userform?

    Quote Originally Posted by My Aswer Is This View Post
    We need to know the following

    1. UserForm Name
    2. Sheet Name where values are.
    3. Label Names
    4. Textbox name where you will need to put Country name to search for
    5. And are you sure you want these values put into a "Label"

    So if you have 20 Swedens you will need 20 different "Labels" or are you wanting all the Swedens into one "Label"

    And if you want to search for more then one country at a time you will need two textboxes to search for country name.

    So please provide the answer to all these question in specific details.


    And what do you plan to do with these labels after they are filled in?

    Are you sure you do not want these values put into a Listbox or Combobox?

    I want the values in labels, im building a sort of read only report in a userform.

    Userform name is Personalform
    Sheet name is REGISTER
    Label names are label1 and so on... i can change this in the code later.

    I will use a combobox to pick the country.
    When i select for example SWEDEN
    I want to fill the names of those that are from sweden in one label.

    So search column C for sweden, then on each hit take column A value from that row and put in the value of one label.
    One label could therefor contain one name or many names...

  4. #4
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    9,096

    Default Re: Table data to labels in userform?

    This script will need to be put into a Command Button on your UserForm
    You need to choose a value from the Combobox and then:
    When you click the button the script will run.
    You will need a Combobox named "Combobox1" on your Userform

    And you will need two labels Named "Sweden" and "Germany"

    The labels height will need to be adequate to hold all the names going into it. Each time a new value is put into the label it will be in a new line.


    You will need to add and modify the script to your needs this sample only works for "Sweden" and "Germany"

    This script goes in your Command Button:

    Code:
    Private Sub CommandButton1_Click()
    Application.ScreenUpdating = False
    Dim i As Long
    Dim Lastrow As Long
    Lastrow = Sheets("Register").Cells(Rows.Count, "A").End(xlUp).Row
    Dim ans As String
    Dim anss As String
    anss = ComboBox1.Value
        For i = Lastrow To 1 Step -1
            If Sheets("Register").Cells(i, 3).Value = anss Then
            ans = Cells(i, 1).Value & vbNewLine & ans
            End If
    Controls(anss).Caption = ans
    Next
    Application.ScreenUpdating = True
    End Sub

    This Script goes in the UserForm
    Code:
    Private Sub UserForm_Initialize()
    ComboBox1.AddItem "Sweden"
    ComboBox1.AddItem "Germany"
    End Sub
    Last edited by My Aswer Is This; Jun 19th, 2017 at 01:52 PM.
    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"

  5. #5
    Board Regular
    Join Date
    Dec 2014
    Posts
    181

    Default Re: Table data to labels in userform?

    What am i missing...

    The result is just an empty label (label4 in below code).

    Code:
    Application.ScreenUpdating = False
    Dim i As Long
    Dim Lastrow As Long
    Lastrow = Sheets("register").Cells(Rows.Count, "A").End(xlUp).Row
    Dim ans As String
    Dim anss As String
    anss = ComboBox1.Value
        For i = Lastrow To 1 Step -1
            If Sheets("register").Cells(i, 3).Value = anss Then
            ans = Cells(i, 1).Value & vbNewLine & ans
            End If
    Label4.Caption = ans
    Next
    Application.ScreenUpdating = True

  6. #6
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    9,096

    Default Re: Table data to labels in userform?

    It's because you changed my script:

    Label4.Caption = ans
    Next


    My script said:

    Controls(anss).Caption = ans

    The label has to have a name like "Germany" or "Sweden"

    Not Label14.


    Look in the properties window and in the box where it says "Label14" change that to "Sweden" or "Germany" or some other country name.
    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"

  7. #7
    Board Regular
    Join Date
    Dec 2014
    Posts
    181

    Default Re: Table data to labels in userform?

    Quote Originally Posted by My Aswer Is This View Post
    It's because you changed my script:

    Label4.Caption = ans
    Next


    My script said:

    Controls(anss).Caption = ans

    The label has to have a name like "Germany" or "Sweden"

    Not Label14.


    Look in the properties window and in the box where it says "Label14" change that to "Sweden" or "Germany" or some other country name.

    Thanks, that made it easier!

  8. #8
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    9,096

    Default Re: Table data to labels in userform?

    Glad I was able to help you. Come back here to Mr. Excel next time you need additional assistance.
    Quote Originally Posted by behedwin View Post
    Thanks, that made it easier!
    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"

  9. #9
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    9,096

    Default Re: Table data to labels in userform?

    In case your still watching this thread:
    I learned a way to automatically size your labels. So you do not have to be concerned about making them long enough.

    Try this new script.

    See added line of code marked in red.

    Code:
    Private Sub CommandButton2_Click()
    'Modified 6-20-17 3:35 AM EDT
    Application.ScreenUpdating = False
    Dim i As Long
    Dim Lastrow As Long
    Lastrow = Sheets("Register").Cells(Rows.Count, "A").End(xlUp).Row
    Dim ans As String
    Dim anss As String
    anss = ComboBox1.Value
        For i = Lastrow To 1 Step -1
            If Sheets("Register").Cells(i, 3).Value = anss Then
            ans = Cells(i, 1).Value & vbNewLine & ans
            End If
    Next
    Controls(anss).Caption = ans
    Controls(anss).AutoSize = True 'Added this line of code. It will Automatically size your label
    Application.ScreenUpdating = True
    End Sub
    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"

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
  •  


DMCA.com