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
on the second multipage you have there, delete the top block of comboboxes and replace the sub with this one. is the top row doing the sort of thing you want?

VBA Code: 
Sub LoadMenuItems()
    Dim MenuItemsCol As Long, LastRow As Long, LastItemRow As Long
    Dim SelWS As Worksheet
    Set SelWS = Sheets(UCase(ListBox1.Value))
   
    With SelWS
        MultiPage1.Pages(1).Caption = "BREAKFAST BUFFET"
        MultiPage1.Value = 1
       
        'ReDim Items(1 To LastRow)  'PSComboBox
       
        ' draw the label
        For menucol = 1 To 3
            Controls("Label" & menucol + 68).Caption = SelWS.Cells(1,menucol * 2 - 1)
            LastItemRow = .Cells(.Rows.Count, menucol * 2 - 1).End(xlUp).Row
            For menuchoices = 1 To 4
               
                ' draw the combobox
                Set mycb = MultiPage1.Pages(1).Controls.Add("Forms.ComboBox.1", "ComboBox" & menucol * 100 + menuchoices, True)
                'Set Items(MenuTypeRow).CB = Controls("ComboBox" & MenuTypeRow)
                mycb.Left = 30 + 300 * (menucol - 1)
                mycb.Height = 24
                mycb.Width = 198
                mycb.Top = (menuchoices - 1) * 30 + 54
                For row2 = 2 To LastItemRow
                    mycb.AddItem .Cells(row2, menucol * 2 - 1)
                Next row2
                mycb.ListRows = LastItemRow - 1
                mycb.TabStop = False
               
               
                DoEvents
            Next menuchoices
        Next menucol
    End With
    DoEvents
End Sub
 
Upvote 0

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
see if this is the right idea
VBA Code: 
Sub LoadMenuItems()
    Dim MenuCol As Long, MenuChoices As Long, LastItemRow As Long
    Dim SelWS As Worksheet, MyCB As Object, MyLab As Object, Ctrl as MSForms.Control
    Set SelWS = Sheets(UCase(ListBox1.Value))
   
    With SelWS
        MultiPage1.Pages(0).Caption = SelWS.Name
        MultiPage1.Value = 0
        'Clear the page
        For Each Ctrl In MultiPage1.Pages(0).Controls
            MultiPage1.Pages(0).Controls.Remove Ctrl.Name
        Next Ctrl
       
        'ReDim Items(1 To LastRow)  'PSComboBox
       
        ' draw the label
        For MenuCol = 1 To 5
            Controls("Label" & MenuCol + 68).Caption = Trim(SelWS.Cells(1, MenuCol * 2 - 1))
            Set MyLab = MultiPage1.Pages(0).Controls.Add("Forms.Label.1", "Label" & MenuCol, True)

            Select Case MenuCol
                Case 1, 2, 3
                    MyLab.Left = 18 + 300 * (MenuCol - 1)
                    MyLab.Top = 30
                Case 4, 5
                    MyLab.Left = 18 + 300 * (MenuCol - 4)
                    MyLab.Top = 198
            End Select
            MyLab.Height = 24
            MyLab.Width = 198
            MyLab.Font.Size = 12
            MyLab.Caption = Trim(SelWS.Cells(1, MenuCol * 2 - 1))
            LastItemRow = .Cells(35, MenuCol * 2 - 1).End(xlUp).Row
           
            If Controls("Label" & MenuCol).Caption <> "" Then
                For MenuChoices = 1 To 4
                    ' draw the combobox
                    Set MyCB = MultiPage1.Pages(0).Controls.Add("Forms.ComboBox.1", "ComboBox" & MenuCol * 100 + MenuChoices, True)
                    'Set Items(MenuTypeRow).CB = Controls("ComboBox" & MenuTypeRow)
                    Select Case MenuCol
                        Case 1, 2, 3
                            MyCB.Left = 30 + 300 * (MenuCol - 1)
                            MyCB.Top = (MenuChoices - 1) * 30 + 54
                        Case 4, 5
                            MyCB.Left = 30 + 300 * (MenuCol - 4)
                            MyCB.Top = (MenuChoices - 1) * 30 + 222
                    End Select
                    MyCB.Height = 24
                    MyCB.Width = 198
                    For row2 = 2 To LastItemRow
                        MyCB.AddItem .Cells(row2, MenuCol * 2 - 1)
                    Next row2
                    MyCB.ListRows = LastItemRow - 1
                    MyCB.TabStop = False
                    DoEvents
                Next MenuChoices
            End If
        Next MenuCol
    End With
    DoEvents
End Sub
 
Last edited:
Upvote 0
diddi said:
on the second multipage you have there, delete the top block of comboboxes and replace the sub with this one. is the top row doing the sort of thing you want?

VBA Code: 
Sub LoadMenuItems()
    Dim MenuItemsCol As Long, LastRow As Long, LastItemRow As Long
    Dim SelWS As Worksheet
    Set SelWS = Sheets(UCase(ListBox1.Value))
  
    With SelWS
        MultiPage1.Pages(1).Caption = "BREAKFAST BUFFET"
        MultiPage1.Value = 1
      
        'ReDim Items(1 To LastRow)  'PSComboBox
      
        ' draw the label
        For menucol = 1 To 3
            Controls("Label" & menucol + 68).Caption = SelWS.Cells(1,menucol * 2 - 1)
            LastItemRow = .Cells(.Rows.Count, menucol * 2 - 1).End(xlUp).Row
            For menuchoices = 1 To 4
              
                ' draw the combobox
                Set mycb = MultiPage1.Pages(1).Controls.Add("Forms.ComboBox.1", "ComboBox" & menucol * 100 + menuchoices, True)
                'Set Items(MenuTypeRow).CB = Controls("ComboBox" & MenuTypeRow)
                mycb.Left = 30 + 300 * (menucol - 1)
                mycb.Height = 24
                mycb.Width = 198
                mycb.Top = (menuchoices - 1) * 30 + 54
                For row2 = 2 To LastItemRow
                    mycb.AddItem .Cells(row2, menucol * 2 - 1)
                Next row2
                mycb.ListRows = LastItemRow - 1
                mycb.TabStop = False
              
              
                DoEvents
            Next menuchoices
        Next menucol
    End With
    DoEvents
End Sub
Click to expand...
Im not sure i understand what you mean by delete the top block of columnboxes. whne i replace the code you gave me and selected breakfast buffet, the headers and comboboxes populated perfectly. in the center column of comboboxes on the top set there are two comboboxes appearing and one does not populate.
 

Attachments

  • this is odd..JPG
    this is odd..JPG
    82.8 KB · Views: 2
Upvote 0
My apologies, i just used the new code you provided, and yes, that is the idea exactly!
 
Upvote 0
ok well i have done a bit of cleaning up as it looked a bit sloppy :) so on your miltipage, delete any labels or comboxes still there. new code... little bit of code does a lot of work LOL you will note all 3 listboxes work now as well. there is a lot to go through in this update. the idea is to generalise further and further until a tiny bit of code covers every item. eg now there is no more code required to do the other 2 listboxes. happy to cover detail further.

VBA Code: 
Private Sub ListBox1_Click()
    LoadMenuItems 0
End Sub

Private Sub ListBox2_Click()
    LoadMenuItems 1
End Sub

Private Sub ListBox3_Click()
    LoadMenuItems 2
End Sub

Sub LoadMenuItems(SelPage As Integer)
    Dim MenuCol As Long, MenuChoices As Long, LastItemRow As Long
    Dim SelWS As Worksheet, MyCB As Object, MyLab As Object
    
    Set SelWS = Sheets(UCase(Controls("ListBox" & SelPage + 1).Value))
    
    With MultiPage1
        .Pages(SelPage).Caption = SelWS.Name
        .Value = SelPage
        'Clear the page
        For Each Ctrl In .Pages(SelPage).Controls
            .Pages(SelPage).Controls.Remove Ctrl.Name
        Next Ctrl
    End With
    
    'Redundant? - ReDim Items(1 To LastRow)  'PSComboBox
        
    For MenuCol = 1 To 5
        ' draw the label
        Set MyLab = MultiPage1.Pages(SelPage).Controls.Add("Forms.Label.1", "Label" & MenuCol, True)
        With MyLab ' assign the properties
            Select Case MenuCol
                Case 1, 2, 3
                    .Left = 18 + 300 * (MenuCol - 1)
                    .Top = 30
                Case 4, 5
                    .Left = 18 + 300 * (MenuCol - 4)
                    .Top = 198
            End Select
            .Height = 24
            .Width = 198
            .Font.Size = 12
            .Caption = Trim(SelWS.Cells(1, MenuCol * 2 - 1))
        End With
        LastItemRow = SelWS.Cells(35, MenuCol * 2 - 1).End(xlUp).Row
            
        If Controls("Label" & MenuCol).Caption <> "" Then
            For MenuChoices = 1 To 4
                ' draw the combobox
                Set MyCB = MultiPage1.Pages(SelPage).Controls.Add("Forms.ComboBox.1", "ComboBox" & MenuCol * 100 + MenuChoices, True)
                'Redundant? - Set Items(MenuTypeRow).CB = Controls("ComboBox" & MenuTypeRow)
                With MyCB ' assign properties and populate with data from worksheet
                    Select Case MenuCol
                        Case 1, 2, 3
                            MyCB.Left = 30 + 300 * (MenuCol - 1)
                            MyCB.Top = (MenuChoices - 1) * 30 + 54
                        Case 4, 5
                            MyCB.Left = 30 + 300 * (MenuCol - 4)
                            MyCB.Top = (MenuChoices - 1) * 30 + 222
                    End Select
                    .Height = 24
                    .Width = 198
                    For row2 = 2 To LastItemRow ' populate the combobox
                        .AddItem SelWS.Cells(row2, MenuCol * 2 - 1)
                    Next row2
                    .ListRows = LastItemRow - 1
                    .TabStop = False
                End With
            Next MenuChoices
        End If
    Next MenuCol
    ' update the screen and controls
    DoEvents
End Sub

Private Sub UserForm_Initialize()
    Dim WS As Worksheet
    For Each WS In ActiveWorkbook.Worksheets
        If Left(WS.Name, 1) <> "-" Then
            ListBox1.AddItem WorksheetFunction.Proper(WS.Name)
        End If
    Next
    
    ListBox2.List = ListBox1.List
    ListBox3.List = ListBox1.List
End Sub
 
Upvote 0
there are some more examples of indenting to see here.
my logic with indents is to define regions in the code by indenting inside code block keyword pairs. if i get to the other end of the code and the indents dont return to none then i have missed an exit keyword.
these are some of the blocks i indent:
For / Next
If / Else / End if
With / End With
While / Wend
Sub / End Sub (Function / End Function) (Type / End Type) etc
Open / Close
Select / End Select
The indenting helps especially when things get nested and a bit hard to sort out. you will also see that each indent block can have a line rule at the left margin and that the lines never cross.
 

Attachments

  • Untitled-1.jpg
    Untitled-1.jpg
    135.1 KB · Views: 3
Upvote 0
diddi said:
ok well i have done a bit of cleaning up as it looked a bit sloppy :) so on your miltipage, delete any labels or comboxes still there. new code... little bit of code does a lot of work LOL you will note all 3 listboxes work now as well. there is a lot to go through in this update. the idea is to generalise further and further until a tiny bit of code covers every item. eg now there is no more code required to do the other 2 listboxes. happy to cover detail further.

VBA Code: 
Private Sub ListBox1_Click()
    LoadMenuItems 0
End Sub

Private Sub ListBox2_Click()
    LoadMenuItems 1
End Sub

Private Sub ListBox3_Click()
    LoadMenuItems 2
End Sub

Sub LoadMenuItems(SelPage As Integer)
    Dim MenuCol As Long, MenuChoices As Long, LastItemRow As Long
    Dim SelWS As Worksheet, MyCB As Object, MyLab As Object
  
    Set SelWS = Sheets(UCase(Controls("ListBox" & SelPage + 1).Value))
  
    With MultiPage1
        .Pages(SelPage).Caption = SelWS.Name
        .Value = SelPage
        'Clear the page
        For Each Ctrl In .Pages(SelPage).Controls
            .Pages(SelPage).Controls.Remove Ctrl.Name
        Next Ctrl
    End With
  
    'Redundant? - ReDim Items(1 To LastRow)  'PSComboBox
      
    For MenuCol = 1 To 5
        ' draw the label
        Set MyLab = MultiPage1.Pages(SelPage).Controls.Add("Forms.Label.1", "Label" & MenuCol, True)
        With MyLab ' assign the properties
            Select Case MenuCol
                Case 1, 2, 3
                    .Left = 18 + 300 * (MenuCol - 1)
                    .Top = 30
                Case 4, 5
                    .Left = 18 + 300 * (MenuCol - 4)
                    .Top = 198
            End Select
            .Height = 24
            .Width = 198
            .Font.Size = 12
            .Caption = Trim(SelWS.Cells(1, MenuCol * 2 - 1))
        End With
        LastItemRow = SelWS.Cells(35, MenuCol * 2 - 1).End(xlUp).Row
          
        If Controls("Label" & MenuCol).Caption <> "" Then
            For MenuChoices = 1 To 4
                ' draw the combobox
                Set MyCB = MultiPage1.Pages(SelPage).Controls.Add("Forms.ComboBox.1", "ComboBox" & MenuCol * 100 + MenuChoices, True)
                'Redundant? - Set Items(MenuTypeRow).CB = Controls("ComboBox" & MenuTypeRow)
                With MyCB ' assign properties and populate with data from worksheet
                    Select Case MenuCol
                        Case 1, 2, 3
                            MyCB.Left = 30 + 300 * (MenuCol - 1)
                            MyCB.Top = (MenuChoices - 1) * 30 + 54
                        Case 4, 5
                            MyCB.Left = 30 + 300 * (MenuCol - 4)
                            MyCB.Top = (MenuChoices - 1) * 30 + 222
                    End Select
                    .Height = 24
                    .Width = 198
                    For row2 = 2 To LastItemRow ' populate the combobox
                        .AddItem SelWS.Cells(row2, MenuCol * 2 - 1)
                    Next row2
                    .ListRows = LastItemRow - 1
                    .TabStop = False
                End With
            Next MenuChoices
        End If
    Next MenuCol
    ' update the screen and controls
    DoEvents
End Sub

Private Sub UserForm_Initialize()
    Dim WS As Worksheet
    For Each WS In ActiveWorkbook.Worksheets
        If Left(WS.Name, 1) <> "-" Then
            ListBox1.AddItem WorksheetFunction.Proper(WS.Name)
        End If
    Next
  
    ListBox2.List = ListBox1.List
    ListBox3.List = ListBox1.List
End Sub
Click to expand...

diddi said:
there are some more examples of indenting to see here.
my logic with indents is to define regions in the code by indenting inside code block keyword pairs. if i get to the other end of the code and the indents dont return to none then i have missed an exit keyword.
these are some of the blocks i indent:
For / Next
If / Else / End if
With / End With
While / Wend
Sub / End Sub (Function / End Function) (Type / End Type) etc
Open / Close
Select / End Select
The indenting helps especially when things get nested and a bit hard to sort out. you will also see that each indent block can have a line rule at the left margin and that the lines never cross.
Click to expand...
the only thing that needs to be adjusted is the number of comboboxes available when Apps ans Stations, Team Breakfast, Team Break, Team Lunch, or Team Dinner are selected. the names of these sheets may change, so referencing them by sheet # is fine. there needs to be 10 comboboxes for each column
 
Upvote 0
so heres something for you to think about... This is the bit that draws the label:
VBA Code: 
        With MyLab ' assign the properties
            .Left = 18 + 300 * (MenuCol - 4)
            .Top = 198
            .Height = 24
            .Width = 198
            .Font.Size = 12
            .Caption = Trim(SelWS.Cells(1, MenuCol * 2 - 1))
        End With
how should the properties be chosen. this is a pattern recognising skill. you can check back on your old version to see where the labels were. basically, you need to spread the 5 labels over the page so there will be room for 10 comboboxes underneath (rather than the 2 row layout). MenuCol wil go from 1 to 5
 
Upvote 0
diddi said:
so heres something for you to think about... This is the bit that draws the label:
VBA Code: 
        With MyLab ' assign the properties
            .Left = 18 + 300 * (MenuCol - 4)
            .Top = 198
            .Height = 24
            .Width = 198
            .Font.Size = 12
            .Caption = Trim(SelWS.Cells(1, MenuCol * 2 - 1))
        End With
how should the properties be chosen. this is a pattern recognising skill. you can check back on your old version to see where the labels were. basically, you need to spread the 5 labels over the page so there will be room for 10 comboboxes underneath (rather than the 2 row layout). MenuCol wil go from 1 to 5
Click to expand...
VBA Code: 
       With MyLab ' assign the properties
            .Left = 15 + 160 * (MenuCol - 5)
            .Top = 0
            .Height = 30
            .Width = 150
            .Font.Size = 12
            .Caption = Trim(SelWS.Cells(1, MenuCol * 2 - 1))
        End With
 
Upvote 0
robertmwaring2 said:
VBA Code: 
       With MyLab ' assign the properties
            .Left = 15 + 160 * (MenuCol - 5)
            .Top = 0
            .Height = 30
            .Width = 150
            .Font.Size = 12
            .Caption = Trim(SelWS.Cells(1, MenuCol * 2 - 1))
        End With
Click to expand...
I hope that was supposed to be the answer you were looking for.
though im not sure how or where to intergrate that
 
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
473
NoSparks
N
F
  • Question
How to get optionbutton values from two different groups
Replies
2
Views
262
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
448
AndyEd
A
N
  • Solved
Copy Listbox Selections from a Worksheet Back to the Userform
Replies
2
Views
620
NorthbyNorthwest
N

Forum statistics

Threads
1,215,432
Messages
6,124,858
Members
449,194
Latest member
HellScout

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