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.
VBA Code:
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:
VBA Code:
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:
VBA Code:
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!