COMBOBOX VALUES

[robertmwaring2]

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.

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?

 

[diddi]

                    For MenuChoices = 1 To 10
                        ' draw the combobox
                        CBID = SelPage * 100 + MenuCol * 20 + MenuChoices
                        Set MyCB = MultiPage1.Pages(SelPage).Controls.Add("Forms.ComboBox.1", "ComboBox" & CBID, True)
                        Set Items(CBID).CB = Controls("ComboBox" & CBID)

                    For MenuChoices = 1 To 4
                        ' draw the combobox
                        CBID = SelPage * 100 + MenuCol * 20 + MenuChoices
                        Set MyCB = MultiPage1.Pages(SelPage).Controls.Add("Forms.ComboBox.1", "ComboBox" & CBID, True)
                        Set Items(CBID).CB = Controls("ComboBox" & CBID)

same thing - 2 places
and then make a change right at the top

    Dim Items(400) As New PSComboBox

 

[diddi]

just saw a mistake. correct this line in both

CBID = SelPage * 100 + MenuCol * 20 + MenuChoices -20

 

[diddi]

so bit of explanation. CBID is the combobox ID - a unique number the identifies that combobox. it contains encoded info...
if the ID is 143, then it is the combobox on page 2, in the 3rd column, 3rd one down
the page number is the hundreds part. the column is in groups of 20 (allowing for enough comboboxes per column) and the units is the row number of the combobox.
this allows us to write 1 piece of code for any combox no matter how many there are. the CBID allows us to know which combobox is using the code

now for a surprise...
put a stop here

run it and <F8> to step forward. keep stepping until you get the userform up (you may need to bring the window forward) then make a combobox choice

 

[robertmwaring2]

i get a message box that says you made choice # at item #
what exactly do those numbers tell me?

 

[diddi]

yes thats working correctly in that case. did you notice when you step that a new piece of code appeared that was not there previously. this is a special module which i can discuss later.
the choice is CBID and the item is the selection made in the combobox. so this bit of code is able to report about any combobox that gets placed onto the userform. it reports the type of action, which combobox and what was chosen. so it is the event handler for the combobox added programmatically (it is called a 'class')
this 1 region of code replaces the 475 lots of code you were going to have to write, plus every time you modified your implementation you would have been doing 475 changes
its very cool and an elegant solution to producing messy code.

 

[robertmwaring2]

yes thats working correctly in that case. did you notice when you step that a new piece of code appeared that was not there previously. this is a special module which i can discuss later.
the choice is CBID and the item is the selection made in the combobox. so this bit of code is able to report about any combobox that gets placed onto the userform. it reports the type of action, which combobox and what was chosen. so it is the event handler for the combobox added programmatically (it is called a 'class')
this 1 region of code replaces the 475 lots of code you were going to have to write, plus every time you modified your implementation you would have been doing 475 changes
its very cool and an elegant solution to producing messy code.

Sounds helpful, but i fear im getting way out of my elemnt quickly

 

[diddi]

thats ok. it is a useful tool which will save heaps of code. just treat it as a black box for now.

 

[robertmwaring2]

ok. hope your day is going well.

 

[diddi]

yeh. at work atm, but if you are around in about 8 hours i will prolly be free. cheers

 

[robertmwaring2]

been playing around with the forms, making them fit the screen and not be so large