COMBOBOX VALUES

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:
VBA Code:
 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, 4, 5
                    .Left = 15 + 160 * (MenuCol - 1)
                    .Top = 0
            End Select
            .Height = 30
            .Width = 150
            .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 10
                ' 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, 4, 5
                            MyCB.Left = 15 + 160 * (MenuCol - 1)
                            MyCB.Top = (MenuChoices - 1) * 35 + 30
                    End Select
                    .Height = 23
                    .Width = 155
                    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


Is this more of what you were wanting me to figure out?
 
Upvote 0

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
oooh very good :).
so here is how i did it, and this gives a method for integration

VBA Code:
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
        
    Select Case SelWS.CodeName
        Case "Sheet20", "Sheet23", "Sheet24", "Sheet25", "Sheet26"
            '1 row layout with 10 comboboxes per item
            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
                    .Left = 18 + 162 * (MenuCol - 1)
                    .Top = 12
                    .Height = 15
                    .Width = 156
                    .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 10
                        ' 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
                            .Left = 24 + 162 * (MenuCol - 1)
                            .Top = (MenuChoices - 1) * 30 + 30
                            .Height = 24
                            .Width = 156
                            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
        Case Else
            '2 row layout with 4 comboboxes per item
            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
                                    .Left = 30 + 300 * (MenuCol - 1)
                                    .Top = (MenuChoices - 1) * 30 + 54
                                Case 4, 5
                                    .Left = 30 + 300 * (MenuCol - 4)
                                    .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
    End Select
    ' update the screen and controls
    DoEvents
 
Upvote 0
you can see i dumped the select case, because in every case i do the same thing, so it does not really achieve anything. my numbers are very similar to yours for the properties in both control types. so you can fiddle with those properties if you want to pretty it up. pretty much any property on the left of the VBA window you can define in that same bit of code. so if you want the color changed just use
VBA Code:
.BackColor=&H0080FFFF& ' lemon yellow :(
1613983549916.png
 
Upvote 0
oooh very good :).
so here is how i did it, and this gives a method for integration

VBA Code:
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
       
    Select Case SelWS.CodeName
        Case "Sheet20", "Sheet23", "Sheet24", "Sheet25", "Sheet26"
            '1 row layout with 10 comboboxes per item
            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
                    .Left = 18 + 162 * (MenuCol - 1)
                    .Top = 12
                    .Height = 15
                    .Width = 156
                    .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 10
                        ' 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
                            .Left = 24 + 162 * (MenuCol - 1)
                            .Top = (MenuChoices - 1) * 30 + 30
                            .Height = 24
                            .Width = 156
                            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
        Case Else
            '2 row layout with 4 comboboxes per item
            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
                                    .Left = 30 + 300 * (MenuCol - 1)
                                    .Top = (MenuChoices - 1) * 30 + 54
                                Case 4, 5
                                    .Left = 30 + 300 * (MenuCol - 4)
                                    .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
    End Select
    ' update the screen and controls
    DoEvents
I was imigining an IF somewhere in the beginning but i guess thats because i understand nithing of using CASE. it takes me a good deal of time to "decrypt" what you provide so I can understand it. Much of it far beyond anything I have used thus far . . .
 
Upvote 0
welcome to ask. there is a fair bit that is prolly new. i dont comment code much, but i can explain any of it if you dont see what it is doing.
Select case is very usefule for either multiple branches or branches with multiple choices. you can have 20 different cases if you want (or more) and you can use things like "Case >5" etc. its a very flexible branch to have available to you.
so moving on, if you want to ask about the code feel free, or if you would like to choose the next step in the design, you may also
 
Upvote 0
welcome to ask. there is a fair bit that is prolly new. i dont comment code much, but i can explain any of it if you dont see what it is doing.
Select case is very usefule for either multiple branches or branches with multiple choices. you can have 20 different cases if you want (or more) and you can use things like "Case >5" etc. its a very flexible branch to have available to you.
so moving on, if you want to ask about the code feel free, or if you would like to choose the next step in the design, you may also
I can follow along so far, it is just taking me a minute to grasp , I'm sorry. I iwll follow up with my own research on case when I have free time.
So now, the issue is once a menu item is selected, I need to determine if it requires any additonal information. to do this i use the quantity or choice forms. in order to determine if it does need additional items/special quantity, every combobox section value should be placed in Master Info V1. If V9, V13, V17, V21, V25 cell value is yes then it will check the value of V6, V10, V14, V18, V22 (RESPECTIVELY) - If the value if those cells says quantity, the a quantity form is called to get the information. if it is anything other than quantity then it will get the option form.
 
Upvote 0
thats good to hear. so we shall give it a go. now is it possible to do this calculation without having to use the sheet? there is a massive amount of formulae in the sheets that is likely redundant/duplicated where there may be an algorithm that can get to the answer easily in code
 
Upvote 0
I can follow along so far, it is just taking me a minute to grasp , I'm sorry. I iwll follow up with my own research on case when I have free time.
So now, the issue is once a menu item is selected, I need to determine if it requires any additonal information. to do this i use the quantity or choice forms. in order to determine if it does need additional items/special quantity, every combobox section value should be placed in Master Info V1. If V9, V13, V17, V21, V25 cell value is yes then it will check the value of V6, V10, V14, V18, V22 (RESPECTIVELY) - If the value if those cells says quantity, the a quantity form is called to get the information. if it is anything other than quantity then it will get the option form.
And to try and better explain what i am expecting or needing to happen is:
everytime a combobox selection is changed (including erased/deleted), the value of that combox box should be instantly placed into Sheet3.Range("V1")
Then I need to verify :
1- if ALL of the cells' V9, V13, V17, V21, V25 value is "NO", if so - nothing else need happen until next selection change
2 -if ANY of the cells' V9, V13, V17, V21, V25 value is "YES", for each that does, if the value of the cell three rows above it (if V9 is "yes" and V6="Quantity" - V6 is 3 rows above V9) says "quantity", then the quantity form is called. If the value of that cell is anything other than "quantity" (except blank) then the option form is called
 
Upvote 0
there is also another issue i dont know that you have considered. how do you assign the code for when you choose an item in a combobox when there are no comboboxes on the userform. this is where the bit of spare code comes in, except i have changed it a bit, so you need to insert the correction in 2 places.
 
Upvote 0
thats good to hear. so we shall give it a go. now is it possible to do this calculation without having to use the sheet? there is a massive amount of formulae in the sheets that is likely redundant/duplicated where there may be an algorithm that can get to the answer easily in code
I am under the impression anything is possible, but I do not know how.
 
Upvote 0

Forum statistics

Threads
1,213,524
Messages
6,114,117
Members
448,549
Latest member
brianhfield

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