Adding information from multiple ComboBoxes in a UserForm (Looping?)

**H0NKEN** · 02-07-2012, 11:03 AM

Hi all,

First of all, I'm new to VBA. I've only been at it for 2-3 days so please bear with me if I'm using the wrong terminology. And also, try and be as explicit as you can in your answers.

The problem:
I have a UserForm which consists of eight different ComboBoxes (Cbo1 through Cbo2) and a "OK-CmdButton". What I'm trying to do is to create some sort of "Loop" that checks if the ListIndex for any of the ComboBoxes is different from 1 - and if so - sends the Value for that ListIndex to the first empty cell in the A-column on Sheet2. If any given Combobox still has ListIndex 1 selected (that is; no entery/change has been made) I want the "Loop" to simply skip over that Combobox without doing anything and move on to the next one. Note that ListIndex is set to 1 for all eight ComboBoxes on UserForm_Initialize.

The idea is that each time I enter a Value in any of the ComboBoxes in my UserForm (and click my "OK-Cmdbutton") the list in column A expands with the values I entered in the UserForms Comboboxes.

I think I've gotten it to work with one single ComboBox. Here's the code I used for that:

Private Sub CmdOK_Click()
With Frm1
    Sheets(">>Sheet2").Select
    Range("A1").Select
    Do Until ActiveCell.Value = ""
    ActiveCell.Offset(1, 0).Select
    Loop
On Error Resume Next
    ActiveCell.FormulaR1C1 = .Cbo1.Value
Unload Me
End With

Thanks in advance!
//H0NKEN

**royUK** · 02-07-2012, 11:49 AM

Try



Private Sub CmdOK_Click()
Dim iX As Integer
Dim NextRw As Long

With Sheets(">>Sheet2")
For iX = 1 To 8
   NextRw=.Cells(.Rows.Count, 1).End(xlUp).Row +1
    .Cells(NextRw, 1).Value = Me("Cbo" & iX).Value
    Next iX
End With
Unload Me
End Sub

**H0NKEN** · 02-07-2012, 01:16 PM

That worked like a charm. Thank you very much Roy!

However...I might need some more help if that's okey with you.

First of all I made a misstake by telling you to send the information to column A on Sheet2. It's actually supposed to go in column B on Sheet 2.

Then there's another issue that might be a bit trickier to solve. You see, the choises in the eight ComboBoxes correspond to different Nutritions (e.g. Egg, Chicken, Yoghurt etc.). The list of these foods are stored in Sheet3 in the following logical order.

Example:

Food	Calories	Protein	Carbohydrates	Fat
Egg	140	13	0	10
Chicken	120	23	1	3
Yoghurt	60	4	11	2

As you can see above, each food has its nutritional Value/100g stored in Columns B through E.
Q: Is there a way of sending this information to Sheet2 aswell?
Before I used a very akward solution with VLOOKUP, but I doubt that I cant get that to work together with the code that you just gave me...

Ideally I would like to be able to chose a series of Foods with the alternatives provided in Cbo1 through Cbo8 in the UserForm and then have all the Foods (together with its corresponding nutritional values) send to: First empty row, Columns B-F, Sheet2.

Thank you for your time and effort!
//H0NKEN

Edit* It's hard to describe what I want. Hang on and I'll provide some pictures...

**H0NKEN** · 02-07-2012, 02:09 PM

Here is the layout of the List of foods in Sheet3:
FoodDatabase.jpg

Here is the UserForm that I'm using:
UserForm.jpg

And here is the end result I'm hoping to achieve
Result.jpg

What I haven't mentioned yet is the "Date TextBox" (txtDate). I want the Value from txtDate to be entered alongside every "Food-entery" to the left in Column A.