Looping Through Userform Controls

**omagoodness** · 05-23-2016, 08:55 AM

Hello again:
I am currently using the following macro to calculate the cost of a recipe entered using a userform (frmRecipeBox) and then storing the information in tblRecipes on Sheets("Recipe Box"). My question is this, is it possible to reduce the amount of code by using a loop? I have played around with somethings and did a lot of searching but can't find amything that works for me. Again, I am very new at all this and learning as I go. I actually need to calculate the cost of 15 ingredients making the code almost 3 times the amount however that is too much text to post here. I am afraid that so much code will invite trouble at some point.

Sub CalculateRecipeCost()
Set MyRange = Sheets("Inventory").Range("tblProducts") 'data range

'Calculate the cost of ingredient 1
Dim myAmt1 As String, myIng1 As String, myUnit1 As String

myAmt1 = frmRecipeBox.rec5.Value 'quantity
myIng1 = frmRecipeBox.rec4.Value 'ingredient
myUnit1 = frmRecipeBox.rec6.Value 'unit



If myAmt1 = vbNullString Then
frmRecipeBox.Cost1 = 0
Else
    If myUnit1 = "Kilograms" Then
frmRecipeBox.Cost1 = Application.WorksheetFunction.VLookup(myIng1, MyRange, 17, 0) * myAmt1
Else
    If myUnit1 = "Grams" Then
frmRecipeBox.Cost1 = Application.WorksheetFunction.VLookup(myIng1, MyRange, 18, 0) * myAmt1
 Else
    If myUnit1 = "Pounds" Then
frmRecipeBox.Cost1 = Application.WorksheetFunction.VLookup(myIng1, MyRange, 19, 0) * myAmt1
Else
    If myUnit1 = "Ounces Dry" Then
frmRecipeBox.Cost1 = Application.WorksheetFunction.VLookup(myIng1, MyRange, 20, 0) * myAmt1
Else
    If myUnit1 = "Liter" Then
frmRecipeBox.Cost1 = Application.WorksheetFunction.VLookup(myIng1, MyRange, 21, 0) * myAmt1
Else
    If myUnit1 = "Mils" Then
frmRecipeBox.Cost1 = Application.WorksheetFunction.VLookup(myIng1, MyRange, 22, 0) * myAmt1
 Else
    If myUnit1 = "Each" Then
frmRecipeBox.Cost1 = Application.WorksheetFunction.VLookup(myIng1, MyRange, 23, 0) * myAmt1
 Else
    If myUnit1 = "Ounces Liquid" Then
frmRecipeBox.Cost1 = Application.WorksheetFunction.VLookup(myIng1, MyRange, 24, 0) * myAmt1
End If
End If
End If
End If
End If
End If
End If
End If
End If

'Calculate the cost of ingredient 2
Dim myAmt2 As String, myIng2 As String, myUnit2 As String

myAmt2 = frmRecipeBox.rec9.Value 'quantity
myIng2 = frmRecipeBox.rec8.Value 'ingredient
myUnit2 = frmRecipeBox.rec10.Value 'unit


If myAmt2 = vbNullString Then
frmRecipeBox.Cost2 = 0
Else
    If myUnit2 = "Kilograms" Then
frmRecipeBox.Cost2 = Application.WorksheetFunction.VLookup(myIng2, MyRange, 17, 0) * myAmt2
Else
    If myUnit2 = "Grams" Then
frmRecipeBox.Cost2 = Application.WorksheetFunction.VLookup(myIng2, MyRange, 18, 0) * myAmt2
 Else
    If myUnit2 = "Pounds" Then
frmRecipeBox.Cost2 = Application.WorksheetFunction.VLookup(myIng2, MyRange, 19, 0) * myAmt2
Else
    If myUnit2 = "Ounces Dry" Then
frmRecipeBox.Cost2 = Application.WorksheetFunction.VLookup(myIng2, MyRange, 20, 0) * myAmt2
Else
    If myUnit2 = "Liter" Then
frmRecipeBox.Cost2 = Application.WorksheetFunction.VLookup(myIng2, MyRange, 21, 0) * myAmt2
Else
    If myUnit2 = "Mils" Then
frmRecipeBox.Cost2 = Application.WorksheetFunction.VLookup(myIng2, MyRange, 22, 0) * myAmt2
 Else
    If myUnit2 = "Each" Then
frmRecipeBox.Cost2 = Application.WorksheetFunction.VLookup(myIng2, MyRange, 23, 0) * myAmt2
 Else
    If myUnit2 = "Ounces Liquid" Then
frmRecipeBox.Cost2 = Application.WorksheetFunction.VLookup(myIng2, MyRange, 24, 0) * myAmt2
End If
End If
End If
End If
End If
End If
End If
End If
End If

'Calculate the cost of ingredient 3
Dim myAmt3 As String, myIng3 As String, myUnit3 As String

myAmt3 = frmRecipeBox.rec13.Value 'quantity
myIng3 = frmRecipeBox.rec12.Value 'ingredient
myUnit3 = frmRecipeBox.rec14.Value 'unit


If myAmt3 = vbNullString Then
frmRecipeBox.Cost3 = 0
Else
    If myUnit3 = "Kilograms" Then
frmRecipeBox.Cost3 = Application.WorksheetFunction.VLookup(myIng3, MyRange, 17, 0) * myAmt3
Else
    If myUnit3 = "Grams" Then
frmRecipeBox.Cost3 = Application.WorksheetFunction.VLookup(myIng3, MyRange, 18, 0) * myAmt3
 Else
    If myUnit3 = "Pounds" Then
frmRecipeBox.Cost3 = Application.WorksheetFunction.VLookup(myIng3, MyRange, 19, 0) * myAmt3
Else
    If myUnit3 = "Ounces Dry" Then
frmRecipeBox.Cost3 = Application.WorksheetFunction.VLookup(myIng3, MyRange, 20, 0) * myAmt3
Else
    If myUnit3 = "Liter" Then
frmRecipeBox.Cost3 = Application.WorksheetFunction.VLookup(myIng3, MyRange, 21, 0) * myAmt3
Else
    If myUnit3 = "Mils" Then
frmRecipeBox.Cost3 = Application.WorksheetFunction.VLookup(myIng3, MyRange, 22, 0) * myAmt3
 Else
    If myUnit3 = "Each" Then
frmRecipeBox.Cost3 = Application.WorksheetFunction.VLookup(myIng3, MyRange, 23, 0) * myAmt3
 Else
    If myUnit3 = "Ounces Liquid" Then
frmRecipeBox.Cost3 = Application.WorksheetFunction.VLookup(myIng3, MyRange, 24, 0) * myAmt3
End If
End If
End If
End If
End If
End If
End If
End If
End If

'Calculate the cost of ingredient 4
Dim myAmt4 As String, myIng4 As String, myUnit4 As String

myAmt4 = frmRecipeBox.rec17.Value 'quantity
myIng4 = frmRecipeBox.rec16.Value 'ingredient
myUnit4 = frmRecipeBox.rec18.Value 'unit


If myAmt4 = vbNullString Then
frmRecipeBox.Cost4 = 0
Else
    If myUnit4 = "Kilograms" Then
frmRecipeBox.Cost4 = Application.WorksheetFunction.VLookup(myIng4, MyRange, 17, 0) * myAmt4
Else
    If myUnit4 = "Grams" Then
frmRecipeBox.Cost4 = Application.WorksheetFunction.VLookup(myIng4, MyRange, 18, 0) * myAmt4
 Else
    If myUnit4 = "Pounds" Then
frmRecipeBox.Cost4 = Application.WorksheetFunction.VLookup(myIng4, MyRange, 19, 0) * myAmt4
Else
    If myUnit4 = "Ounces Dry" Then
frmRecipeBox.Cost4 = Application.WorksheetFunction.VLookup(myIng4, MyRange, 20, 0) * myAmt4
Else
    If myUnit4 = "Liters" Then
frmRecipeBox.Cost4 = Application.WorksheetFunction.VLookup(myIng4, MyRange, 21, 0) * myAmt4
Else
    If myUnit4 = "Mils" Then
frmRecipeBox.Cost4 = Application.WorksheetFunction.VLookup(myIng4, MyRange, 22, 0) * myAmt4
 Else
    If myUnit4 = "Each" Then
frmRecipeBox.Cost4 = Application.WorksheetFunction.VLookup(myIng4, MyRange, 23, 0) * myAmt4
 Else
    If myUnit4 = "Ounces Liquid" Then
frmRecipeBox.Cost4 = Application.WorksheetFunction.VLookup(myIng4, MyRange, 24, 0) * myAmt4
End If
End If
End If
End If
End If
End If
End If
End If
End If


'Calculate the cost of ingredient 5
Dim myAmt5 As String, myIng5 As String, myUnit5 As String

myAmt5 = frmRecipeBox.rec21.Value 'quantity
myIng5 = frmRecipeBox.rec20.Value 'ingredient
myUnit5 = frmRecipeBox.rec22.Value 'unit


If myAmt5 = vbNullString Then
frmRecipeBox.Cost5 = 0
Else
    If myUnit5 = "Kilograms" Then
frmRecipeBox.Cost5 = Application.WorksheetFunction.VLookup(myIng5, MyRange, 17, 0) * myAmt5
Else
    If myUnit5 = "Grams" Then
frmRecipeBox.Cost5 = Application.WorksheetFunction.VLookup(myIng5, MyRange, 18, 0) * myAmt5
 Else
    If myUnit5 = "Pounds" Then
frmRecipeBox.Cost5 = Application.WorksheetFunction.VLookup(myIng5, MyRange, 19, 0) * myAmt5
Else
    If myUnit5 = "Ounces Dry" Then
frmRecipeBox.Cost5 = Application.WorksheetFunction.VLookup(myIng5, MyRange, 20, 0) * myAmt5
Else
    If myUnit5 = "Liter" Then
frmRecipeBox.Cost5 = Application.WorksheetFunction.VLookup(myIng5, MyRange, 21, 0) * myAmt5
Else
    If myUnit5 = "Mils" Then
frmRecipeBox.Cost5 = Application.WorksheetFunction.VLookup(myIng5, MyRange, 22, 0) * myAmt5
 Else
    If myUnit5 = "Each" Then
frmRecipeBox.Cost5 = Application.WorksheetFunction.VLookup(myIng5, MyRange, 23, 0) * myAmt5
 Else
    If myUnit5 = "Ounces Liquid" Then
frmRecipeBox.Cost5 = Application.WorksheetFunction.VLookup(myIng5, MyRange, 24, 0) * myAmt5
End If
End If
End If
End If
End If
End If
End If
End If
End If

'Add the 5 ingredient amounts together for total recipe cost

frmRecipeBox.rec65.Value = Format(Val(Replace(frmRecipeBox.Cost1.Value, ",", "")) + _
    Val(Replace(frmRecipeBox.Cost2.Value, ",", "")) + _
    Val(Replace(frmRecipeBox.Cost3.Value, ",", "")) + _
    Val(Replace(frmRecipeBox.Cost4.Value, ",", "")) + _
    Val(Replace(frmRecipeBox.Cost5.Value, ",", "")) + _
    Val(Replace(frmRecipeBox.Cost6.Value, ",", "")) + _
    Val(Replace(frmRecipeBox.Cost7.Value, ",", "")) + _
    Val(Replace(frmRecipeBox.Cost8.Value, ",", "")) + _
   Val(Replace(frmRecipeBox.Cost9.Value, ",", "")) + _
   Val(Replace(frmRecipeBox.Cost10.Value, ",", "")) + _
    Val(Replace(frmRecipeBox.Cost11.Value, ",", "")) + _
    Val(Replace(frmRecipeBox.Cost12.Value, ",", "")) + _
    Val(Replace(frmRecipeBox.Cost13.Value, ",", "")) + _
    Val(Replace(frmRecipeBox.Cost14.Value, ",", "")) + _
    Val(Replace(frmRecipeBox.Cost15.Value, ",", "")))
End Sub

**Norie** · 05-23-2016, 09:20 AM

You could use a loop here and I was just about to post some code as I thought I saw a naming pattern.

Unfortunately I was wrong, there is an obvious naming pattern for the variables but not for the controls, though I think there is a pattern.

Anyway, give this a try.

Option Explicit

Sub CalculateRecipeCost()
Dim MyRange As Range
Dim I As Long
Dim myAmt As String, myIng As String, myUnit As String
Dim arrWeights()
Dim Res As Variant

    Set MyRange = Sheets("Inventory").Range("tblProducts")    'data range

    'Calculate the cost of ingredient 1

    arrWeights = Array("Kilograms", "Grams", "Pounds", "Ounces Dry", "Liter", "Mils", "Each", "Ounces Liquid")

    For I = 1 To 5    ' change 5 to reflect no of possible ingredients

        myIng = frmRecipeBox.Controls("rec" & (4 + (I - 1) * 4)).Value    'ingredient
        myAmt = frmRecipeBox.Controls("rec" & (5 + (I - 1) * 4)).Value    'quantity
        myUnit = frmRecipeBox.Controls("rec" & (6 + (I - 1) * 4)).Value    'unit

        If myAmt = vbNullString Then
            frmRecipeBox.Controls("Cost").Value = 0
        Else
            Res = Application.Match(myUnit, arrWeights, 0)
            If Not IsError(Res) Then
                Res = Res + 16
                frmRecipeBox.Controls("Cost" & I).Value = Application.VLookup(myIng, MyRange, Res, 0) * myAmt
            End If
        End If

    Next I

    frmRecipeBox.rec65.Value = Format(Val(Replace(frmRecipeBox.Cost1.Value, ",", "")) + _
                                      Val(Replace(frmRecipeBox.Cost2.Value, ",", "")) + _
                                      Val(Replace(frmRecipeBox.Cost3.Value, ",", "")) + _
                                      Val(Replace(frmRecipeBox.Cost4.Value, ",", "")) + _
                                      Val(Replace(frmRecipeBox.Cost5.Value, ",", "")) + _
                                      Val(Replace(frmRecipeBox.Cost6.Value, ",", "")) + _
                                      Val(Replace(frmRecipeBox.Cost7.Value, ",", "")) + _
                                      Val(Replace(frmRecipeBox.Cost8.Value, ",", "")) + _
                                      Val(Replace(frmRecipeBox.Cost9.Value, ",", "")) + _
                                      Val(Replace(frmRecipeBox.Cost10.Value, ",", "")) + _
                                      Val(Replace(frmRecipeBox.Cost11.Value, ",", "")) + _
                                      Val(Replace(frmRecipeBox.Cost12.Value, ",", "")) + _
                                      Val(Replace(frmRecipeBox.Cost13.Value, ",", "")) + _
                                      Val(Replace(frmRecipeBox.Cost14.Value, ",", "")) + _
                                      Val(Replace(frmRecipeBox.Cost15.Value, ",", "")))
End Sub

PS You could make things a lot easier if you only used one set of textboxes for the ingredients and had the user add each ingredient to a 3 column listbox.

It would also probably be a lot easier, for the user and you, if you used a combobox for the weights.

**omagoodness** · 05-23-2016, 10:10 AM

I tried this code but it gave an error at

frmRecipeBox.Controls("Cost" & I).Value = Application.VLookup(myIng, MyRange, Res, 0) * myAmt

Couldn't find the object. There is no control named "Cost". There is a cost textbox for each ingredient (Cost1, Cost 2........Cost15). These controls do not get saved to the table. They are just used to calculate the total cost of the recipe by looking up the cost of each ingredient, adding them together and putting the result in the control named rec65. There is a naming pattern for the controls that are being added to the table row. They are named rec1 to rec 68.
I am attaching a copy of my form and the complete macro so you can get a better idea. What if I changed the naming patterns to something like Ing1....Ing15; Amt1....Amt15, etc.
Thanks

**AlphaFrog** · 05-23-2016, 09:31 AM

Try something like this. Not tested.

Sub CalculateRecipeCost()
    
    Dim MyRange As Range
    Set MyRange = Sheets("Inventory").Range("tblProducts")    'data range
    
'Calculate the cost of ingredients
    Dim myAmt As String, myIng As String, myUnit As String, i As Long, j As Long, col As Long
    Dim totalcost As Single, cost As Variant
    
    With frmRecipeBox
        
        For i = 4 To 64 Step 4    'loop through controls by numbered name
            
            myAmt = .Controls("rec" & i + 1).Value    'quantity
            myIng = .Controls("rec" & i + 0).Value    'ingredient
            myUnit = .Controls("rec" & i + 2).Value   'unit
            col = 0
            j = j + 1    'cost counter
            
            If myAmt = vbnulstring Then
                .Controls("Cost" & j) = 0
                cost = 0
            Else
                Select Case myUnit
                    Case "Kilograms": col = 17
                    Case "Grams": col = 18
                    Case "Pounds": col = 19
                    Case "Ounces Dry": col = 20
                    Case "Liter": col = 21
                    Case "Mils": col = 22
                    Case "Each": col = 23
                    Case "Ounces Liquid": col = 24
                End Select
            End If
            
            If col > 0 Then cost = Application.VLookup(myIng1, MyRange, col, 0) * myAmt
            .Controls("Cost" & j) = cost
            totalcost = totalcost + cost 'Add the ingredient amounts together for total recipe cost
            
        Next i
        
        .rec65.Value = Format(totalcost)
        
    End With
    
End Sub

**omagoodness** · 05-23-2016, 11:46 AM

This code does not work as is, but I don't have time right now to look closer. I will let you know how I make out. Thanks

**Norie** · 05-23-2016, 10:18 AM

The code I posted would not be looking for a control named 'Cost'.

This would be a whole lot easier using a listbox.

**Norie** · 05-23-2016, 10:21 AM

Actually are you sure the error isn't on this line of code which has a typo?

    frmRecipeBox.Controls("Cost").Value = 0

That should be this.

    frmRecipeBox.Controls("Cost" & I).Value = 0

**omagoodness** · 05-23-2016, 11:31 AM

Sorry. You are right. There is a typo there. I will try the code again when I get a chance and let you know how it went. Thanks

**omagoodness** · 05-23-2016, 12:06 PM

Eukeka!!!!!! I found the problem. Once I made the correction to the typo AND fixed a couple of data errors in the underlying data table, all is working. I had imported the real table data after I had my original code working and I didn't notice that there were a few entries in a column that didn't follow the proper data validation. Anyway, I am marking this thread as Solved.
Thank you everyone.

**omagoodness** · 05-23-2016, 11:49 AM

Thanks Norrie. You are right, there was a type. I saw that earlier but I don't know enough about loops to actually recognize it as a typo. I tried the code again with the typo corrected and I am getting calculations but not on all ingredients all the time, which makes very little sense. Actually my original code was doing the same thing so the problem likely lies with my naming pattern or something. I don't have time at the moment to play with it but I will let you know how it goes. Thanks again.

**Keebellah** · 05-23-2016, 11:01 AM

I'd like to help but I'm getting errors like SortByRecipe (Sub or Function not found)
Then Run-time error '9' Subscript out of range, it's probably an inlcomplete file
Sorry

**omagoodness** · 05-23-2016, 11:30 AM

You are right. Incomplete file. SortByRecipe is a macro and can be commented out.

**Keebellah** · 05-23-2016, 11:52 AM

Can you attach the corrected file?

**omagoodness** · 05-23-2016, 12:07 PM

Resolved. Thanks anyway. I used Norie's code (with typo corrected). Works well.

**Keebellah** · 05-23-2016, 02:45 PM

Congrats

Looping Through Userform Controls

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Looping Through Userform Controls

Re: Looping Through Userform Controls

Re: Looping Through Userform Controls

Re: Looping Through Userform Controls

Re: Looping Through Userform Controls

Re: Looping Through Userform Controls

Re: Looping Through Userform Controls

Re: Looping Through Userform Controls

Re: Looping Through Userform Controls

Re: Looping Through Userform Controls

Re: Looping Through Userform Controls

Re: Looping Through Userform Controls

Re: Looping Through Userform Controls

Re: Looping Through Userform Controls

Re: Looping Through Userform Controls

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Looping through controls by frame on userform (frame within a frame)

Looping through label controls

looping controls in to an array

looping through controls

[SOLVED] Looping through controls

Looping through controls on form

[SOLVED] Problem with looping through userform controls

Bookmarks

Bookmarks

Posting Permissions