Passing data from one userform to a formula on another userform

[Pookiemeister]

In the code below, I declared lDz and lCs as public variables so I can use them in a formula located on another user form. Unfortunately the values will not pass to the formula and I get a Run-time error '11': Division by zero. I even created invisible text boxes and tried to assign the values to it. That didn't work either and I didn't want to do that anyway. Is there a simple method or explanation that can be given that explains the best way to pass values between forms that are not used in text boxes but in formulas that will always work. Thank you.

Option Explicit 
Public sUOM As String
Public lDz As Long
Public lCs As Long


Private Sub cmdbtnAddItem_Click()
    
    Dim LastRow As Long
    Dim cnt As Integer
    Dim ctl As Control
    
    Worksheets(Chattfrm.cmbSDPFLine.Value).Select
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row + 1
    
    If rngItem Is Nothing Then
       Set rngItem = Cells(LastRow, "B")
    End If
    If rngItem.Offset(, -1).Value = "" Then
        Cells(LastRow, 1).Value = Len(Me.txtbxDescription.Value)
    Else
        rngItem.Offset(, -1).Value = Len(Me.txtbxDescription.Value)
    End If
    
    If rngItem.Offset(, 0).Value = "" Then
        Cells(LastRow, 2).Value = Me.txtbxPrdctCde.Value
    Else
        rngItem.Offset(, 0).Value = Me.txtbxPrdctCde.Value
    End If
        
    If rngItem.Offset(, 1).Value = "" Then
        Cells(LastRow, 3).Value = Application.Proper(Me.txtbxDescription.Value)
    Else
        rngItem.Offset(, 1).Value = Application.Proper(Me.txtbxDescription.Value)
    End If
            
    If rngItem.Offset(, 2).Value = Me.txtbxDzPrCs.Value Then
        Cells(LastRow, 4).Value = Me.txtbxDzPrCs.Value
    Else
        rngItem.Offset(, 2).Value = Me.txtbxDzPrCs.Value
    End If
    
    If frmAddProduct.txtbxDzPrCs.Value = "" Then
        MsgBox "Please enter dozens per case.", vbCritical + vbDefaultButton1 + vbOKOnly, "Missing: Dozens per case"
        Me.txtbxDzPrCs.SetFocus
        Exit Sub
    Else
        rngItem.Offset(, 2).Value = Me.txtbxDzPrCs.Value
    End If
    
    If frmAddProduct.txtbxCsPerPal.Value = "" Then
        MsgBox "Please enter cases per pallet.", vbCritical + vbDefaultButton1 + vbOKOnly, "Missing: Cases per pallet"
        Me.txtbxCsPerPal.SetFocus
        Exit Sub
    Else
        rngItem.Offset(, 3).Value = Me.txtbxCsPerPal.Value
    End If
    
    For Each ctl In Me.Controls
        If TypeName(ctl) = "OptionButton" Then
            If ctl.Value = True Then
                cnt = cnt + 1
                Debug.Print "You have selected " & ctl.Caption
                rngItem.Offset(, 4).Value = ctl.Caption
                sUOM = ctl.Caption
            End If
        End If
    Next ctl
    If cnt = 0 Then
        MsgBox "Please select a unit of measure (UOM).", vbCritical + vbDefaultButton1 + vbOKOnly, "Missing: Unit of Measure"
        frmAddProduct.optEa.SetFocus
        Exit Sub
    End If
    
    Unload Me
    Chattfrm.Show
    lDz = Me.txtbxDzPrCs.Value
    lCs = Me.txtbxCsPerPal.Value
End Sub

Formula variables are to be used in.

textValUp = ((txtbxdz.Value) / lDz / lCs) + 0.5 - 1E-16

 

[shg]

Move the declarations to a standard code module.

 

[Pookiemeister]

First let me say that I am still trying to learn and understand how use VBA, so please excuse some of my simple questions. So are you saying I need to create a module and place the code in it like this:

Sub variables()    lDz = frmAddProduct.txtbxDzPrCs.Value
    lCs = frmAddProduct.txtbxCsPerPal.Value
End Sub

If I am wrong, can you please give an example. Thank You

 

[shg]

No.

Insert a standard code module and put this in it:

Option Explicit 
Public sUOM As String
Public lDz As Long
Public lCs As Long

Then remove the declarations from any Forms modules.

 

[Pookiemeister]

After I deleted the declarations you suggested, I only had to adjust a few things and it worked great. Thank you very much.

 

[shg]

You're welcome.