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]

you only learn by getting assistance. you have a project and an opportunity to improve your skill with something relevant. just copy the whole code section uf the userform. not interested in the worksheet unless i need specific format of some data. i can show you how i indent. its not hard and the vba window does it quickly. if you want to put it in the code window, paste it in. select the whole block and click as per pic.

 

[robertmwaring2]

I hope I'm understanding your instructions correctly.
I have included below only sections of the code for the userform because when I tried to post the entirety of the code, the page just froze (it is ALOT). I'm sure there is an easier way to write it, but I have not reached that level yet, I am merely just beginning. I do want to thank you for your patience and willingness to assist/help me learn more - it sincerely means alot.

As I am still learning, I have used formulas throughout the workbook to help where I can (create lists of data without spaces, copy data from one page to another, etc. . .) but in doing so - the file size has gotten rather large and the file takes a few seconds to open ( ifear it may take a minute or more on a slower computer)

The code below is for when the form initializes. it is used to fill the labels o the form and populate the comboboxes woith value. As I mentioned there are 15 pages on a multipage in the form. Most of these pages contain 20 comboboxes, but there are 5 that contain 55 each (total of 475 comboboxes). I used the method below because the list that populates the comboboxes is dynamic and may contain more or less data from tiem to time.

Private Sub UserForm_Initialize()

Application.ScreenUpdating = False

'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

'there is more basically identical code for 14 more pages on the mutlipage within the same form, the only difference being the sheet name where the information is held and the combobox names.

end sub

Once all the comboboxes are selected on the form, the user is to click a button. On the button click, the following code runs:

Private Sub cmdbSubmitInfo_Click()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
'This compiles all the ComboBox data from the userform to Master Info Sheet in single Column (BC)
Call ListComboboxSelections
'copies selections to production, formats cells on production sheet, and removes all dashes on production sheet
Call CopytoProductionSheet
Call FormatCells
'CREATES NEW SHEET
Call CREATENEWSHEET
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Unload UFProductionSheet
Workbooks("PRODUCTION MENU WORK BOOK.xlsm").Close SaveChanges:=False
End Sub

The macros "ListComboboxSelections" above is the one I am having issues with. When this code begins, it immediately goes to the form initalize code and reads every line before returning to the macro to finish, but just loops and loops, and loops (no error code, just wont stop), I have also noticed that I cannot ide any of the worksheets in the workbook or I do get an error when it attempts to run. For example, it any of the sheets listed in the form initialzation code are hideen, the macro will fail. I get a Runtime error code 1004, Range of Object worksheet failed message. Just to calrify, the code in the macro is as follows:

Sub ListComboboxSelections()
    ' update sheet3 with no blanks
    Dim Ctrl As MSforms.Control, CtrlCount As Long
    With Sheet3
        For Each Ctrl In UFProductionSheet.Controls
            If TypeName(Ctrl) = "ComboBox" Then
                If Ctrl.Value <> "" Then
                    CtrlCount = CtrlCount + 1
                    .Cells(CtrlCount, 55) = Ctrl.Value
                End If
            End If
        Next
    End With
End Sub

I tried my best to explain the issue and I hope you can understand, if not please feel free to asl for clarification. I realize my terminology may be incorrect or confusing and for that I apologize.

Below is the link to the code for the userform in its entirety if that helps. I have alss linked the code for each of the macros that are run after the button click on the form. I have been able to determine, however, that each of these other macros run without issue - it is just the first one labeled "ListComboboxSelections" where it gets stuck and I suspect it has something to do with the way I have populated the comboboxes at form initialize. I say this because the code works fine if I populate the comboboxes via a named range within the workbook. That will cause the workbook size to increase drastically as there would be 75 additional named ranges, so I am trying to avoid this.

Module 3 Code Here
Module 2 Code Here
Module 5 Code Here
Module 1 Code Here
Form Initialize Code Here

I realize this is ALOT, and I sincerely appreciate your efforts and time, but I DO NOT want to monopolize it, so if this is too much to deal with I completely understand if you want to "walk away". No hard feeling!

 

[diddi]

thanks for 'taking a risk' so to speak. when you are online we can discuss your project live rather than with 10 hour intervals i will have a look now to familiarise myself with the task

 

[robertmwaring2]

I'm available for the next two hours if you have questions

 

[diddi]

super! so what i am doing at the moment is trying to cut down the complexity of the code. how much detail do you want in terms of instruction. i am happy to go step by step if thats of benefit to you

 

[diddi]

well i have managed to remove about 22,000 lines from the project so it will start to run quicker and file size will drop

 

[robertmwaring2]

I am (if it isn't already evident) a novice, to say the least. I welcome any opportunity to learn this, but at times struggle to understand. I'm a big "why" kinda person and like to know how things go together. That being said, the more instruction the better. My ultimate goal would be to one day be able to eliminate the many columns of formulas I have used in the workbook to compensate for my lack of vba knowledge and trim the file size down drastically.

 

[diddi]

can you screenshot the userform UFProductionSheet pls

 

[robertmwaring2]

Yikes! 22,000. I hadn't realized there was even that much to it. Now I feel horrible. It wasn't my intention to have you redo it all (but it's appreciated if it's more efficient). I just hope in able to understand what you've done for future reference or if something changes. My first project was created based on fixed data, not anticipating it would need to change so frequently or drastically. I've learned a bit more since then, though clearly not enough . . . . This project is for creating production sheets for my culinary team

 

[diddi]

ok i am happy to do details. what i am doing is looking for patterns in your code which has been duplicated. so as you would have discovered very quickly is that writing all the combobox code would have been a painful exercise LOL