populate userform with list of names from a range and assign a corresponding checkbox to each name...

kbishop94

Active Member
Joined
Dec 5, 2016
Messages
307
Not sure if this is possible, but I thought I'd ask anyway.

I'd like a create a userform that, upon opening, will capture a list of names from a specific range on a worksheet, and then take each individual string from each cell within that range and assign them to a label that will populate on the form. Along with each name that gets populated on the form, a corresponding checkbox would then be created and assigned to each specific string (each name) that was previously captured and populated on the form...

It's probably easier just to show exactly what I am trying to create here:

The range of names includes names that would get captured and populated on the form are located in row 1 on the main worksheet starting at row N and goes over to the last name:

example9.PNG

Ultimately I would like to form to look like this when its opened and the script is run:
(the example I created shows columns of names that go 26 labels deep and is 4 columns wide... this would be enough to cover any amount of names that could possibly populate the form. they do not have to be like this.... I only made this as an example to show what I am trying to recreate)
(also, please pay no attention to the fact that the names are repeating starting in the 3rd column... this userform is just a 'mock-up' and I simply copy-and-pasted the names from the worksheet onto the labels on this example form in order to illustrate what I ultimately would hope that the form will look like if I can get this to work...)


example5.PNG

The names on the main worksheet can vary depending on which button can be selected which then filters the names down to just those people that work in the department that represents each button:
(my example shows a specific department (a pink 'tolling department' button) and the corresponding names that apply to just those names. all other names not applicable to the specific button that is selected are not visible)


example10.PNG

After the user narrows down the names to just those visible on the screen (in row 1, as shown in the previous example), and hits the worksheet tab that runs the script to open the userform, I would like it to look something like this (the example below shows the same 10 names that was previously filtered on the main worksheet shown above):
(the example below shows what I ultimately would like the user form to look like when the form is started after the names have been filtered down)

example8.PNG


If what I am asking is not possible, what suggestions would anyone have that might ultimately do what I am wanting this code to do?


(also, if anyone would be interested in helping me with this request, and would like to see what I currently have and how my workbook functions, I would be happy to share it... just let me know. :)

everything in it is just what I have pieced together and made to work over the 5 years that I have had so many gracious mrexcel users help me with and get it to function the way that I needed. Thanks again!)
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,830
Office Version
  1. 365
Platform
  1. Windows
I will help you. Just letting you know this is not a simple request. It's not like I can just pop out a quick answer and solve the problem. Do you want to work together? Would you consider some suggestions from somebody that has created a ton of forms, both in userforms (vba) and sheet forms?

I used to think that userforms were cool and the only way to go. Now I believe that sheet forms are way more flexible and easier to create. Not only are they easier to create, they interface with your database by using formulas rather than having to use VBA to load the starting values.

You could create the form you showed by using formulas that reference the names from the table. It's a two way street; any input you give the form can be copied or referenced by formula. Share your workbook.
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,901
One way to do this would be to not use checkboxes, but to use a mult-select listbox with the ListStyle set to fmListStyleOption

VBA Code:
ListBox1.ListStyle = fmListStyleOption
ListBox1.MultiSelect = fmMultiSelectMulti
ListBox1.List = Range("A1:A5").Value
 

kbishop94

Active Member
Joined
Dec 5, 2016
Messages
307
I will help you. Just letting you know this is not a simple request. It's not like I can just pop out a quick answer and solve the problem. Do you want to work together? Would you consider some suggestions from somebody that has created a ton of forms, both in userforms (vba) and sheet forms?

I used to think that userforms were cool and the only way to go. Now I believe that sheet forms are way more flexible and easier to create. Not only are they easier to create, they interface with your database by using formulas rather than having to use VBA to load the starting values.

You could create the form you showed by using formulas that reference the names from the table. It's a two way street; any input you give the form can be copied or referenced by formula. Share your workbook.

Many thanks to you, sir. (y)

Yes, I understand and recognize that this goes well beyond the usual "hey everyone, I need some script added to my code to do this" type of post."

And yes I absolutely will consider suggestions or alternatives that might achieve the same desired affect that I am looking for.

I will upload my workbook and code later today. THANK YOU very much for offering to help.
 

kbishop94

Active Member
Joined
Dec 5, 2016
Messages
307

ADVERTISEMENT

One way to do this would be to not use checkboxes, but to use a mult-select listbox with the ListStyle set to fmListStyleOption

VBA Code:
ListBox1.ListStyle = fmListStyleOption
ListBox1.MultiSelect = fmMultiSelectMulti
ListBox1.List = Range("A1:A5").Value

Thank you for the suggestion. I had not thought of using a multiselection list box(!)

I mocked up a quick test form using a multiselection box, and yes I believe that would work. However, and unfortunately, I also believe that it would require the user to perform a significant amount of 'clicks' and 'selections' in order to capture all of the names that might need to be selected.

But, that being said, if I am unable to achieve my desired affect by utilizing the efficient functionality of something like checkboxes, then at least I now know that I have something to 'fall back on' that can work and I can use that. (so thats a win for sure(!) ) 🍻(y) thanks!
 

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,830
Office Version
  1. 365
Platform
  1. Windows
I really like using single cells for checkboxes. Using the Winding font and adding symbols for Checked and unchecked boxes I can control the checkboxes using some vba to toggle the values. I can control the size of the checkboxes better. I can get the value of each checkbox better.

VBA Code:
[CODE=vba]Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

  If Not Intersect(Target, Range("Checkboxes")) Is Nothing Then
    Cancel = True
    Application.EnableEvents = False
    If Target.Value <> "þ" Then          'Not Checked
      Target.Value = "þ"
    Else
      Target.Value = "o"
    End If
    Application.EnableEvents = True
  End If
  
    
End Sub
[/CODE]
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,901
Thank you for the suggestion. I had not thought of using a multiselection list box(!)

I mocked up a quick test form using a multiselection box, and yes I believe that would work. However, and unfortunately, I also believe that it would require the user to perform a significant amount of 'clicks' and 'selections' in order to capture all of the names that might need to be selected.

But, that being said, if I am unable to achieve my desired affect by utilizing the efficient functionality of something like checkboxes, then at least I now know that I have something to 'fall back on' that can work and I can use that. (so thats a win for sure(!) ) 🍻(y) thanks!
No matter the controls of the interface, the user will have to click on the screen just as many times, regarless of which tools are on the interface. If you prefer the ListBox could be set to fmMultiSelectExtended.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,303
Messages
5,595,324
Members
413,986
Latest member
Elizsk

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