Updating Userform Data

[omagoodness]

I have a userform (frmRecipes) that is designed to be print a recipe that is selected from a combobox (cboRecipe). The combobox populates the userform an underlying table.
Sometimes the user would like to change the recipe yield before printing. For example, perhaps they want to double the recipe or cut it in half. I have another combobox (cboYieldChange) where they can select the desired change. The code below has 2 problems:
1. After the initial change, it will not start the module again when a new value is selected.
2. The message box alert triggered if the user makes a yield change with no recipe selected, triggers twice with the line to reset cboYieldChange back to "".
3. There are 15 possible ingredients and I am trying to use a loop to make the changes to each ingredient rather than 15 different if:then statements., however there is something wrong with that snippet of code and I don't know what. it is. When I use the if;then statements, it works fine.
I also want to be able to return to the original amounts.
NOTE: The changes are for printing purposes only and will not be saved to the underlying table.
Any suggestions are welcome. I've been struggling with this for 2 days now and have not been able to find anything on the web to help, (at least not that I understand).

Private Sub cboYieldChange_Change()
'Multiplies the ingredient amounts and total cost by the amount selected in the drop down list
'Set the variables
Dim i As Long, Yield As Long, Cost As Long, Change As Long
Dim A As Integer, B As Integer, C As Integer, D As Long
A = 0.25
B = 0.5
C = 1.5
D = 2

Yield = Me.txtYield 'The recipe yield stored in the table
Cost = Me.RecCost ' the cost of the recipe stored in the table

    If Me.cboYieldChange.ListIndex = 0 Then 'Quarter
        Change = A
    Else
    If Me.cboYieldChange.ListIndex = 1 Then 'Half
        Change = B
    Else
    If Me.cboYieldChange.ListIndex = 2 Then 'One + Half
        Change = C
    Else
    If Me.cboYieldChange.ListIndex = 3 Then 'Double
        Change = D
    End If
    End If
    End If
    End If

Me.RecCost.Value = (Cost * Change)
Me.txtYield.Value = Me.txtYield * Change
Me.txtRCost.Value = Format(Me.RecCost, "#,##0.00")
Me.txtYieldAmt.Value = Me.txtYield & " " & Me.txtUnit

    For i = 1 To 15
    If Me("Amt" & i).Value > 0 Then
   Me("Amt" & i).Value = Me("Amt" & i).Value * Change
   End If
  Next i

'************************************************************
'There are 15 possible ingredients.  These 2 examples work OK

'If Me.Amt1.Value > 0 Then
'Me.Amt1.Value = Me.Amt1.Value * Change
'End If
'If Me.Amt2.Value > 0 Then
'Me.Amt2.Value = Me.Amt2.Value * Change
'End If

'***********************************************************
End Sub

This is a screenshot of my form

 

[dmt32]

Hi,
try this change to your For Next loop & see if helps you

For i = 1 To 15
        With Me.Controls("Amt" & i)
            If Val(.Value) > 0 Then .Value = Val(.Value) * Change
        End With
    Next i

If still have issues, helpful to forum if can place copy of your workbook with sample data in dropbox.

Dave

 

[omagoodness]

Hi,
try this change to your For Next loop & see if helps you

For i = 1 To 15
        With Me.Controls("Amt" & i)
            If Val(.Value) > 0 Then .Value = Val(.Value) * Change
        End With
    Next i

If still have issues, helpful to forum if can place copy of your workbook with sample data in dropbox.

Dave

Thank you Dave. The loop works great. Still working on the other 2 issues.

 

[dmt32]

Thank you Dave. The loop works great. Still working on the other 2 issues.

Its a good start - placing copy of your workbook with sample data in a dropbox would be helpful - plenty here to assist you.

Dave

 

[DanteAmor]

The following is for you to consider to simplify your macro a bit.

Private Sub cboYieldChange_Change()
  'Multiplies the ingredient amounts and total cost by the amount selected in the drop down list
  'Set the variables
  Dim i As Long, Yield As Long, Cost As Long, Change As Long
  
  Yield = Me.txtYield 'The recipe yield stored in the table
  Cost = Me.RecCost ' the cost of the recipe stored in the table
  
  Select Case Me.cboYieldChange.ListIndex
    Case 0: Change = 0.25
    Case 1: Change = 0.5
    Case 2: Change = 1.5
    Case 3: Change = 2
  End Select
  
  Me.RecCost.Value = (Cost * Change)
  Me.txtYield.Value = Me.txtYield * Change
  Me.txtRCost.Value = Format(Me.RecCost, "#,##0.00")
  Me.txtYieldAmt.Value = Me.txtYield & " " & Me.txtUnit
  
  For i = 1 To 15
    With Me.Controls("Amt" & i)
      If Val(.Value) > 0 Then .Value = Val(.Value) * Change
    End With
  Next i
End Sub