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

[kbishop94]

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:



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...)



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)




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)




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!)

 

[Jeffrey Mahoney]

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]

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

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

 

[kbishop94]

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.

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]

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

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(!) ) ? thanks!

 

[Jeffrey Mahoney]

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.

[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]

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(!) ) ? 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.