My Nutrition Form/File

[Sandler]

I have a form with a listbox of all my food items. I am trying to get a label to say the specific food items serving size. For example, Almond Milk Serving Size is 1 Cup. Unfortunately, my code is not allowing the label to post the 1 cup aspect of the label. The information would be gathered from this "NData" worksheet. So, it would be
column C &" "& column B. Below the sheet screen capture, I have listed my current code. Please, help.

NData

	A	B	C	D	E	F	G	H	I	J	K
							Cal
Almond Milk	Cup
AlmondPlus-5XProtein	cup
Almonds-BD_BT	oz
Almonds-BD_HD	oz
Almonds-BD_CBS	oz
Almonds-BD_Strawberry	oz
Almonds-CocoaDusted - Nice!	cup
Almonds-handfulTJ	servings
Apple	servings

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:282px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:18px;"><col style="width:64px;"><col style="width:18px;"><col style="width:64px;"><col style="width:18px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #c0c0c0"]P[/TD]

[TD="bgcolor: #c0c0c0"]C[/TD]

[TD="bgcolor: #c0c0c0"]F[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]2.5[/TD]

[TD="align: right"]31[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]5[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]2[/TD]

[TD="align: right"]42[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]5[/TD]

[TD="align: right"]9[/TD]

[TD="align: right"]12[/TD]

[TD="align: right"]164[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]6[/TD]

[TD="align: right"]6[/TD]

[TD="align: right"]15[/TD]

[TD="align: right"]183[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]6[/TD]

[TD="align: right"]7[/TD]

[TD="align: right"]14[/TD]

[TD="align: right"]178[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]5[/TD]

[TD="align: right"]9[/TD]

[TD="align: right"]12[/TD]

[TD="align: right"]164[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="align: right"]0.25[/TD]

[TD="align: right"]6[/TD]

[TD="align: right"]6[/TD]

[TD="align: right"]12[/TD]

[TD="align: right"]156[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]7[/TD]

[TD="align: right"]8[/TD]

[TD="align: right"]17[/TD]

[TD="align: right"]213[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="align: right"]1[/TD]

[TD="align: right"]0[/TD]

[TD="align: right"]15[/TD]

[TD="align: right"]0[/TD]

[TD="align: right"]60[/TD]

</tbody>

Option Explicit

Public servingInfo As String


Private Sub lbxNutrition_Click()

'Variable used to get info from form and function
Dim nutritionMSG As String
Dim cFood As String

'Get the current food selected in the listbox
cFood = Me.lbxNutrition.Value

'Call the GetServing function, passing in the food selected
servingInfo = GetServing(cFood)

' output the content into the label control on serving size of selected food item
nutritionMSG = cFood & " serving size is " & servingInfo
Me.lblNutritionFacts.Caption = nutritionMSG

'activate the controls to be used by the user
Me.lblNutritionFacts.Enabled = True
Me.txtServings.Enabled = True
End Sub

Public Function GetServing(whichFood As String)
'select the Nutrition Data sheet
Worksheets("NData").Range("A1").Select
'loop through column A until correct food item is found
Dim i As Integer
i = 1
Do While ActiveCell.Value <> ""
If ActiveCell.Value = whichFood Then
' with correct food found pass serving info back to calling procedure
servingInfo = ActiveCell.Offset(0, 2).Value & " " & ActiveCell.Offset(0, 1).Value
Exit Do

End If

i = i + 1
Worksheets("NData").Cells(i, 1).Select
Loop
End Function

 

[Sandler]

I just had to split my Public Function into a separate module and add a header to Range A1 of NData.

This is resolved, thank you!