Table data to labels in userform?

behedwin

Active Member
Joined
Dec 10, 2014
Messages
399
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?
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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?
 
Upvote 0
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...
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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 [COLOR=#ff0000]'Added this line of code. It will Automatically size your label[/COLOR]
Application.ScreenUpdating = True
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

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