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?

 

[robertmwaring2]

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

doubt i will be awake at that hour though.

 

[diddi]

i believe there will be some simple vba that will do o lot of that index and lookup work which will save heaps of size and reduce all those cell formulas

 

[robertmwaring2]

i believe there will be some simple vba that will do o lot of that index and lookup work which will save heaps of size and reduce all those cell formulas

Hello! Hope all is well!

That would be amazing! Im not all that great at formulas - but do pretty good i think. It does eat up data size though when creating lists. If I knew how to create them using vba and keep them updated real time, that would most definitely save data size, but probably be more efficient - in either case, as I said - AMAZING! I can explain what it all is and how they correspond to each other if needed.
I've been playing around with a few additional forms as well.
I added a password to the button to exit the Welcome form (that enables you to edit the workbook), to keep anyone from tampering with things they dont understand (lol)
I also edited the welcome page a bit too (sorry - but love your idea)
This is the "Flow" I would like the forms to work and their function I hope to accomplish:
welcome page - all details must be entered before you can move forward. the button to begin new production sheet will lead to a nother form, asking if you are creating a custom sheet or from regular menu. If reg menu is selected, i need the production sheet form to load, if the custom button is selected, I need another form to load asking if they need to create just the one production sheet, or do you have others. If only the one, then it will run the code, create the new sheet, and close the workbook without saving it, Leaving the new production sheet open and ready to save or print or mail or . . .if more than one sheet is to be created and the other button is clicked, I would like to somehow have the new sheet created in new workbook as before, but only start the welcome page process all over. The only issue being the information may have changed during use that will need to be undone, or in a place that can be erased.

from the production form, if its button is clicked, I want the same form asking if its just one or multuple sheets needed to load, if one sheet button is clicked - same procedure as above - create new sheet, close workbook without saving. and likewise, it more than one is needed it would still create new sheet, but resart workbook as if it were freshly opened.

Had a long work week, but am free the rest of this evening and off the next two days as well if you have some spare time to continue (assuming you still care to)

 

[diddi]

i am here now if you are still about

 

[robertmwaring2]

i am here now if you are still about

yes I am, thank you!

 

[diddi]

so last we talked you stepped into the code and found that when you changed the combobox a message appeared
this is exactly what i wanted. basically, even tho there is no code for the comboboxes, it now knows what to do.
so we can now move on and i can ask a new question... what should happen when the combobox is selected? answer it loads the values back to the sheet to determine the "Yes"/"No" .
if you want to see that working you can update this bit

Sub CB_change()
    If CB.ListIndex = -1 Then Exit Sub
       
    Dim SelCB As Integer
    SelCB = Val(Mid(CB.Name, 9))
    MsgBox "you chose " & SelCB & "  item " & CB.ListIndex
    With Sheets("-Master Info-")
        .Cells(1, 22) = CB.Value
        .Calculate
        
        For framenum = 1 To 6
            If .Cells(framenum * 4 + 5, 22) = "YES" Then
                If .Cells(framenum * 4 + 1, 22) = "QUANTITY" Then
                    UFQUANTITY.Show
                Else
                    UFCHOICE.Show
                End If
            End If
        Next framenum
    End With
    
    DoEvents
End Sub

did you find where this bit of code actually is?

 

[diddi]

at this point, however, i would like to understand the logic for determining the Yes/No
This can then be incorporated into that new bit of code

 

[robertmwaring2]

this may be hard to explain, but ill give it a try:
the purpose of the question is to determine if the combobox selection also requires additional selections. it could be any number of up to 6 extra selections. so, for example, you choose americana from the first column of the breakfast buffet list. Americana requires a breakfast meat selection. this can be determined by searching each menu sheet in the range O2:T35. If the selection is found on any menu sheet in that range, the header of the column it is found in is whats required. looking at the pic below, you will see three menu items on this sheet alone that require a breakfast meat choice and three that require a quantity choice. If an item is listed in more than one category/column, it needs to be asked a question for each of those columns - for example if americana below was in both column A & C, then you would first choose the breakfast meat then the quantity, then you are free to make your next combobox selection from the productionsheet userform. On the -Info- Sheet A:W, are the list of choices for each of the column headers on the menu pages (O:T). If you have the breakfast meat selection required, the you would choose between the items highlighted in the second image. Once you make your selection, we need to find the column that the ingredients for that menu are in. so, we would search breakfast buffet sheet (because americana is on that sheet) in the range Z1:AAW1 for americana. Once we find the column, below it somewhere in the rows of information will be a cell with "Breakfast Meat" in it. On the final production Sheet, this needs to be replaced with the whatever the user chooses as the selection for the meat - perhaps chicken sausage links. I hope this makes sense . . .

 

[diddi]

in the userform, breakfast meats are already listed in their own block

 

[robertmwaring2]

this may be hard to explain, but ill give it a try:
the purpose of the question is to determine if the combobox selection also requires additional selections. it could be any number of up to 6 extra selections. so, for example, you choose americana from the first column of the breakfast buffet list. Americana requires a breakfast meat selection. this can be determined by searching each menu sheet in the range O2:T35. If the selection is found on any menu sheet in that range, the header of the column it is found in is whats required. looking at the pic below, you will see three menu items on this sheet alone that require a breakfast meat choice and three that require a quantity choice. If an item is listed in more than one category/column, it needs to be asked a question for each of those columns - for example if americana below was in both column A & C, then you would first choose the breakfast meat then the quantity, then you are free to make your next combobox selection from the productionsheet userform. On the -Info- Sheet A:W, are the list of choices for each of the column headers on the menu pages (O:T). If you have the breakfast meat selection required, the you would choose between the items highlighted in the second image. Once you make your selection, we need to find the column that the ingredients for that menu are in. so, we would search breakfast buffet sheet (because americana is on that sheet) in the range Z1:AAW1 for americana. Once we find the column, below it somewhere in the rows of information will be a cell with "Breakfast Meat" in it. On the final production Sheet, this needs to be replaced with the whatever the user chooses as the selection for the meat - perhaps chicken sausage links. I hope this makes sense . . .

If it is quantity, then you will locate the column of the menu selection from the form on the sheet it resides, and in the cell 1 row below and 1 column to the right, the quantity the user enters when asked will be placed there. for example, imagine americana was listed under quantity - after you made your breakfast meat selection, you would then enter a quantity. in the sheet pictured, the red cell is where the quantity would go (from the quantity form) and the breakfast meat in red would be replaced with the selction made from the choice form.