COMBOBOX VALUES

robertmwaring2

Board Regular
Joined
Mar 8, 2019
Messages
104
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:

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,267
Office Version
  1. 2010
Platform
  1. Windows
VBA Code:
                    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)

VBA Code:
                    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
VBA Code:
    Dim Items(400) As New PSComboBox
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,267
Office Version
  1. 2010
Platform
  1. Windows
just saw a mistake. correct this line in both
VBA Code:
CBID = SelPage * 100 + MenuCol * 20 + MenuChoices -20
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,267
Office Version
  1. 2010
Platform
  1. Windows
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
1613988620824.png

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

Board Regular
Joined
Mar 8, 2019
Messages
104
Office Version
  1. 365
Platform
  1. Windows
i get a message box that says you made choice # at item #
what exactly do those numbers tell me?
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,267
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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.
 
Last edited:

robertmwaring2

Board Regular
Joined
Mar 8, 2019
Messages
104
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
May 20, 2004
Messages
3,267
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,267
Office Version
  1. 2010
Platform
  1. Windows
yeh. at work atm, but if you are around in about 8 hours i will prolly be free. cheers
 

robertmwaring2

Board Regular
Joined
Mar 8, 2019
Messages
104
Office Version
  1. 365
Platform
  1. Windows
been playing around with the forms, making them fit the screen and not be so large
 

Watch MrExcel Video

Forum statistics

Threads
1,127,316
Messages
5,623,955
Members
416,002
Latest member
t10k14

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
Top