Gather ALL Combobox Selections into a Column

Robertmwaring

New Member
Joined
Mar 8, 2019
Messages
15
Hey All,

I am COMPLETELY NEW to VBA and an attempting the "learn as I go" method. I am a chef, and am attempting to create production sheets based on menu selections. I am creating a userform (UFProductionSheet) to make the selections and enter other needed details. All menu selections are purposely made from comboboxes (all of which contain "cb" as the first letters in their names). I have created a button captioned "Submit" that transfers all userform information to specific cells on various sheets in the workbook. There are textboxes with information that go to one sheet ("Production Sheet"), and ALL the comboboxes go to a column in another. ("Info Sheet") There are quite a few comboboxes, and thus far I only know how to enter the value of each into a specific cell (each individually) - which is fine albeit time consuming, but I was wondering if there is a way to compile a list of all combobox values that are not "" (blank) and place that list in a column (Column "CB") in a specific worksheet ("Info Sheet") - that way I can avoid blank cells in that column and perhaps less code? I have tried to research how to accomplish this but apparently am not formulating my question correctly and cannot find an answer. Thanks in advance for the help.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,078
Office Version
365
Platform
Windows
How about
Code:
Private Sub CommandButton2_Click()
   Dim Ctrl As Object

   For Each Ctrl In Me.Controls
      If TypeName(Ctrl) = "ComboBox" Then
         If Ctrl.Value <> "" Then
            Sheets("Info Sheet").Range("CB" & Rows.Count).End(xlUp).Offset(1).Value = Ctrl.Value
         End If
      End If
   Next
End Sub
 

Robertmwaring

New Member
Joined
Mar 8, 2019
Messages
15
Bless Your Soul
This is genious, I have been typing the same line of code repeatedly making a few changes to each for what seems like an enternity now. Thank you so much.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,078
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Forum statistics

Threads
1,081,708
Messages
5,360,781
Members
400,595
Latest member
T_Dubs

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top