COMBOBOX VALUES

R

robertmwaring2

Board Regular
Joined
Mar 8, 2019
Messages
132
Office Version
  1. 365
Platform
  1. Windows
I have a userform that contains a mutlipage with 15 pages. All total, there are 475 comboboxes between the pages on this one form (yes i realize this is alot).
I am attempting to compile a list of all the combox values that are not left blank into a single column on a sheet within the workbook the form is apart of (Sheet3.Range("BC2:BC??"). I have used the following code previoulsy, but for whatever reason it now just loops endlessly IF any comboboxes on page 1 of the multipage are left blank.

VBA Code: 
Dim Ctrl As Object
   For Each Ctrl In UFProductionSheet.Controls
      If TypeName(Ctrl) = "ComboBox" Then
         If Ctrl.Value <> "" Then
            Sheet3.Range("BC" & Rows.Count).End(xlUp).Offset(1).Value = Ctrl.Value
         End If
      End If
   Next

I really don't relish the idea of having to allocate a specific cell in the worksheet for each combobox to hold the value of the box on a change event, as I mentioned - there are 475 of them. The code above has worked flawlessly in the past, but I am in the process of creating a revised workbook that would allow for users to update more information and be less restrictive. Somewhere along the line, one of the changes I've made has caused this to stop functioning as it once did. I use the above code as a module that is called on a button click. Can anyone help me?
 
Last edited by a moderator:
Sort by date Sort by votes
diddi said:
so to insert the names into the listbox, you do the following:

VBA Code: 
Private Sub UserForm_Initialize()
    'populate listboxes 1 - 3
    With ListBox1
        .AddItem "Breakfast"
        .AddItem "Breaktime Snack"

you can change what i have in the "" ""
and then add the rest of the items from your sheet names. last line is
VBA Code: 
End With
so there are 2 new things i have used here. firstly when you need to reference something over and over as you have had to do, i have used a With / end with section. this means that i dont have to type "ListBox1" over and over. the hard way is to type
VBA Code: 
ListBox1.additem "...."


and the other new thing is i used an indent to mark for myself that i am using a with block
Click to expand...
Is using the quotes not dynamic - and Ill get to that later?
 
Upvote 0

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
ultimately you would scan the whole workbook for menu types and populate from that. easy to change later. correct. not dunamic. but it is about to be :)
 
Upvote 0
It seems I'm only populating list box 1, should I do the same process for list box 2 and three?
 
Upvote 0
just get listbox1 done and post your code :)
 
Upvote 0
VBA Code: 
Private Sub UserForm_Initialize()

Application.ScreenUpdating = False

   'populate listboxes 1 - 3
    With ListBox1
        .AddItem "Breakfast Buffet"
        .AddItem "Breakfat Plated"
        .AddItem "Breaks"
        .AddItem "Lunch Buffet"
        .AddItem "Lunch Plated"
        .AddItem "Apps and Stations"
        .AddItem "Dinner Buffet"
        .AddItem "Dinner Plated"
        .AddItem "Family Meal"
        .AddItem "Team Breakfast"
        .AddItem "Team Break"
        .AddItem "Team Lunch"
        .AddItem "Team Dinner"
        .AddItem "Blank Master"
    End With
        
        
        
'this is page 1 of multipage on production sheet form : labels and drop down lists
Sheet4.Activate
UFProductionSheet.MultiPage1.pg1.Caption = Sheet4.Range("n4").Value
UFProductionSheet.lb1.Caption = Sheet4.Range("a1").Value
UFProductionSheet.lb2.Caption = Sheet4.Range("c1").Value
UFProductionSheet.lb3.Caption = Sheet4.Range("e1").Value
UFProductionSheet.lb4.Caption = Sheet4.Range("g1").Value
UFProductionSheet.lb5.Caption = Sheet4.Range("i1").Value
UFProductionSheet.ComboBox1.List = Sheet4.Range("A2", Range("A2").End(xlDown)).Value
UFProductionSheet.ComboBox2.List = Sheet4.Range("A2", Range("A2").End(xlDown)).Value
UFProductionSheet.ComboBox3.List = Sheet4.Range("A2", Range("A2").End(xlDown)).Value
UFProductionSheet.ComboBox4.List = Sheet4.Range("A2", Range("A2").End(xlDown)).Value
UFProductionSheet.ComboBox5.List = Sheet4.Range("c2", Range("c2").End(xlDown)).Value
UFProductionSheet.ComboBox6.List = Sheet4.Range("c2", Range("c2").End(xlDown)).Value
UFProductionSheet.ComboBox7.List = Sheet4.Range("c2", Range("c2").End(xlDown)).Value
UFProductionSheet.ComboBox8.List = Sheet4.Range("c2", Range("c2").End(xlDown)).Value
UFProductionSheet.ComboBox9.List = Sheet4.Range("e2", Range("e2").End(xlDown)).Value
UFProductionSheet.ComboBox10.List = Sheet4.Range("e2", Range("e2").End(xlDown)).Value
UFProductionSheet.ComboBox11.List = Sheet4.Range("e2", Range("e2").End(xlDown)).Value
UFProductionSheet.ComboBox12.List = Sheet4.Range("e2", Range("e2").End(xlDown)).Value
UFProductionSheet.ComboBox13.List = Sheet4.Range("g2", Range("g2").End(xlDown)).Value
UFProductionSheet.ComboBox14.List = Sheet4.Range("g2", Range("g2").End(xlDown)).Value
UFProductionSheet.ComboBox15.List = Sheet4.Range("g2", Range("g2").End(xlDown)).Value
UFProductionSheet.ComboBox16.List = Sheet4.Range("g2", Range("g2").End(xlDown)).Value
UFProductionSheet.ComboBox17.List = Sheet4.Range("i2", Range("i2").End(xlDown)).Value
UFProductionSheet.ComboBox18.List = Sheet4.Range("i2", Range("i2").End(xlDown)).Value
UFProductionSheet.ComboBox19.List = Sheet4.Range("i2", Range("i2").End(xlDown)).Value
UFProductionSheet.ComboBox20.List = Sheet4.Range("i2", Range("i2").End(xlDown)).Value

End Sub
 
Upvote 0
robertmwaring2 said:
VBA Code: 
Private Sub UserForm_Initialize()

Application.ScreenUpdating = False

   'populate listboxes 1 - 3
    With ListBox1
        .AddItem "Breakfast Buffet"
        .AddItem "Breakfat Plated"
        .AddItem "Breaks"
        .AddItem "Lunch Buffet"
        .AddItem "Lunch Plated"
        .AddItem "Apps and Stations"
        .AddItem "Dinner Buffet"
        .AddItem "Dinner Plated"
        .AddItem "Family Meal"
        .AddItem "Team Breakfast"
        .AddItem "Team Break"
        .AddItem "Team Lunch"
        .AddItem "Team Dinner"
        .AddItem "Blank Master"
    End With
      
      
      
'this is page 1 of multipage on production sheet form : labels and drop down lists
Sheet4.Activate
UFProductionSheet.MultiPage1.pg1.Caption = Sheet4.Range("n4").Value
UFProductionSheet.lb1.Caption = Sheet4.Range("a1").Value
UFProductionSheet.lb2.Caption = Sheet4.Range("c1").Value
UFProductionSheet.lb3.Caption = Sheet4.Range("e1").Value
UFProductionSheet.lb4.Caption = Sheet4.Range("g1").Value
UFProductionSheet.lb5.Caption = Sheet4.Range("i1").Value
UFProductionSheet.ComboBox1.List = Sheet4.Range("A2", Range("A2").End(xlDown)).Value
UFProductionSheet.ComboBox2.List = Sheet4.Range("A2", Range("A2").End(xlDown)).Value
UFProductionSheet.ComboBox3.List = Sheet4.Range("A2", Range("A2").End(xlDown)).Value
UFProductionSheet.ComboBox4.List = Sheet4.Range("A2", Range("A2").End(xlDown)).Value
UFProductionSheet.ComboBox5.List = Sheet4.Range("c2", Range("c2").End(xlDown)).Value
UFProductionSheet.ComboBox6.List = Sheet4.Range("c2", Range("c2").End(xlDown)).Value
UFProductionSheet.ComboBox7.List = Sheet4.Range("c2", Range("c2").End(xlDown)).Value
UFProductionSheet.ComboBox8.List = Sheet4.Range("c2", Range("c2").End(xlDown)).Value
UFProductionSheet.ComboBox9.List = Sheet4.Range("e2", Range("e2").End(xlDown)).Value
UFProductionSheet.ComboBox10.List = Sheet4.Range("e2", Range("e2").End(xlDown)).Value
UFProductionSheet.ComboBox11.List = Sheet4.Range("e2", Range("e2").End(xlDown)).Value
UFProductionSheet.ComboBox12.List = Sheet4.Range("e2", Range("e2").End(xlDown)).Value
UFProductionSheet.ComboBox13.List = Sheet4.Range("g2", Range("g2").End(xlDown)).Value
UFProductionSheet.ComboBox14.List = Sheet4.Range("g2", Range("g2").End(xlDown)).Value
UFProductionSheet.ComboBox15.List = Sheet4.Range("g2", Range("g2").End(xlDown)).Value
UFProductionSheet.ComboBox16.List = Sheet4.Range("g2", Range("g2").End(xlDown)).Value
UFProductionSheet.ComboBox17.List = Sheet4.Range("i2", Range("i2").End(xlDown)).Value
UFProductionSheet.ComboBox18.List = Sheet4.Range("i2", Range("i2").End(xlDown)).Value
UFProductionSheet.ComboBox19.List = Sheet4.Range("i2", Range("i2").End(xlDown)).Value
UFProductionSheet.ComboBox20.List = Sheet4.Range("i2", Range("i2").End(xlDown)).Value

End Sub
Click to expand...
I dont want to get ahead of myself, but with this new layout and if the comboboxes are going to have the entire sheets of menu items listed from column W on the corrwsponding pages, I do not actually need the comboboxes to be broken up into sections like they are. Unless they are going to actually going to remain as is and actually populate from the individual columns (A, C, E, G, I). I also dont see how I will even fit the list boxes on the sheets with 55 boxes. You can tell me to shut up - I just think too much sometimes. And maybe I don't fully realize your "re-vision" yet.
 
Upvote 0
perfect already :) so your plan is to do the code again for listbox2 and 3?
try this instead

VBA Code: 
Private Sub UserForm_Initialize()
    'populate listboxes 1 - 3
    With ListBox1
        .AddItem "Breakfast Buffet"
        .AddItem "Breakfat Plated"
        .AddItem "Breaks"
        .AddItem "Lunch Buffet"
        .AddItem "Lunch Plated"
        .AddItem "Apps and Stations"
        .AddItem "Dinner Buffet"
        .AddItem "Dinner Plated"
        .AddItem "Family Meal"
        .AddItem "Team Breakfast"
        .AddItem "Team Break"
        .AddItem "Team Lunch"
        .AddItem "Team Dinner"
        .AddItem "Blank Master"
    End With
   
    ListBox2.List = ListBox1.List
    ListBox3.List = ListBox1.List
End Sub

a list box is just a display tool for an array. the whole array is stored in the property ".List"
so i can copy the array from list to list
 
Upvote 0
all those comboboxes are RIP hahah
 
Upvote 0
And I do not want to seen ungrateful, but I am already up past my "bedtime" on a "school night". I would typically be sleeping by now (have to be up at 3am for work) but I am willing to hold out til midnight to learning. I am, however, off all day Monday and Tuesday to dedicate to any assignment I need to accomplish.
 
Upvote 0
oh wow, why didn't i think of that, its GENIUS. that saves SOOO much time, and im guessing vastly more efficient.

I assume then I should have actually just deleted the combobox code alltogether then?
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Dynamic Named Range not working with Userform ComboBox
Replies
6
Views
474
NoSparks
N
F
  • Question
How to get optionbutton values from two different groups
Replies
2
Views
263
NoSparks
N
B
  • Solved
Check Combobox is empty
2
Replies
14
Views
1K
smozgur
smozgur
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
451
AndyEd
A
N
  • Solved
Copy Listbox Selections from a Worksheet Back to the Userform
Replies
2
Views
622
NorthbyNorthwest
N

Forum statistics

Threads
1,215,455
Messages
6,124,935
Members
449,195
Latest member
Stevenciu

Share this page

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