variables declared public won't pass value to another userform

[Pookiemeister]

I can't figure out what I am doing wrong. I thought if you declared a variable as public, it could be used anywhere within the workbook. Not so in this case because none of these public variables declared will pass their value. So what I am doing wrong? Any help will be greatly appreciated. Thank You.

Option Explicit

Public txtDz As Long, txtCs As Long
Public txtUOM As String

Sub Test()

Dim ws_count As Integer, i As Integer, FinalRow As Integer, x As Integer


ws_count = ActiveWorkbook.Worksheets.Count
    For i = 4 To ws_count
        Worksheets(i).Activate
        FinalRow = Cells(Rows.Count, 2).End(xlUp).Row
        For x = 1 To FinalRow
            Cells(x, 2).Select
            If Cells(x, 2).Value & " " & "(" & Cells(x, 3).Value & ")" = Chattemfrm.cmbPrdCde.Value Then
               txtDz = Cells(x, 4)
               txtCs = Cells(x, 5)
               txtUOM = Cells(x, 6)
               Chattemfrm.txtbxStckNum.Value = Cells(x, 7)
            End If
        Next x
    Next i
    If txtDz = 0 Or txtCs = 0 Or txtUOM = "" Or Chattemfrm.txtbxStckNum.Value = "" Then
        Call ErrorTrap
    End If
    
End Sub

ErrorTrap sub:

Sub ErrorTrap()        
    Worksheets(Chattemfrm.cmbSDPFLine.Value).Activate
    MsgBox Chattemfrm.cmbPrdCde.Value & " not found or is missing values in Product list. Please add missing product to the product list or fill in missing data in order to continue.", vbOKOnly + vbCritical + vbDefaultButton1, "Missing Information"
    frmAddProduct.Show
    Exit Sub
    
End Sub

frmAddProduct values won't pass to this form

Private Sub UserForm_Initialize()    
    
    If txtDz = 0 Then
        txtbxDzPrCs.Enabled = True
    Else
        txtbxDzPrCs.Enabled = False
        txtbxDzPrCs.Value = txtDz
    End If
    
    
    If txtCs = 0 Then
        txtbxCsPerPal.Enabled = True
    Else
        txtbxCsPerPal.Enabled = False
        txtbxCsPerPal.Value = txtCs
    End If
    
    
    If txtCs = 0 Then
        txtbxCsPerPal.Enabled = True
    Else
        txtbxCsPerPal.Enabled = False
        txtbxCsPerPal.Value = txtCs
    End If
    
End Sub

 

[RoryA]

Pretty hard to debug that with what you've provided. What values do the txtDz and txtCs variables have before you load the form, and have you either declared those variables in the form too, or used the same names for controls on that form? (txtCs is a pretty weird variable name for a Long data type.)

 

[Norie]

Where have you declared the public variables?

 

[Pookiemeister]

You're right. Never thought about that. I need to fix that. Thank You
Here is the updated code. I changed txtDz to lDz and txtCs to lCs and txtUOM to sUOM. Hopefully it clarifies variables.

Option ExplicitPublic lDz As Long, lCs As Long
Public sUOM As String
Sub Test()


Dim ws_count As Integer, i As Integer, FinalRow As Integer, x As Integer


ws_count = ActiveWorkbook.Worksheets.Count
    For i = 4 To ws_count
        Worksheets(i).Activate
        FinalRow = Cells(Rows.Count, 2).End(xlUp).Row
        For x = 1 To FinalRow
            Cells(x, 2).Select
            If Cells(x, 2).Value & " " & "(" & Cells(x, 3).Value & ")" = Chattemfrm.cmbPrdCde.Value Then
               lDz = Cells(x, 4)
               lCs = Cells(x, 5)
               sUOM = Cells(x, 6)
               Chattemfrm.txtbxStckNum.Value = Cells(x, 7)
            End If
        Next x
    Next i
    If lDz = 0 Or lCs = 0 Or sUOM = "" Or Chattemfrm.txtbxStckNum.Value = "" Then
        Call ErrorTrap
    End If
    Chattemfrm.Show
End Sub

Private Sub UserForm_Initialize()    
    
    If lDz = 0 Then
        txtbxDzPrCs.Enabled = True
    Else
        txtbxDzPrCs.Enabled = False
        txtbxDzPrCs.Value = lDz
    End If
    
    
    If lCs = 0 Then
        txtbxCsPerPal.Enabled = True
    Else
        txtbxCsPerPal.Enabled = False
        txtbxCsPerPal.Value = lCs
    End If
    
    
    If Chattemfrm.txtbxStckNum.Value = "" Then
        frmAddProduct.txtbxStckNum.Enabled = True
    Else
        frmAddProduct.txtbxStckNum.Enabled = False
        frmAddProduct.txtbxStckNum = Chattemfrm.txtbxStckNum.Value
    End If
    
End Sub

What values do the txtDz and txtCs variables have before you load the form

Comes from the code below:

lDz = Cells(x, 4)           lCs = Cells(x, 5)

and have you either declared those variables in the form too, or used the same names for controls on that form?

No, I haven't declared those variables in the form nor have I used the same names for controls. Do I need to declare those variables in the form?

Thank You for your quick response.

 

[Pookiemeister]

I declared my Public Variables in a module above the Sub Test(). Is that the wrong place to put it? If so, where would be a better place for it? A userform? Thank you.

Option Explicit
[B]Public lDz As Long, lCs As Long[/B]
[B]Public sUOM As String[/B]
Sub Test()


Dim ws_count As Integer, i As Integer, FinalRow As Integer, x As Integer


ws_count = ActiveWorkbook.Worksheets.Count
    For i = 4 To ws_count
        Worksheets(i).Activate
        FinalRow = Cells(Rows.Count, 2).End(xlUp).Row
        For x = 1 To FinalRow
            Cells(x, 2).Select
            If Cells(x, 2).Value & " " & "(" & Cells(x, 3).Value & ")" = Chattemfrm.cmbPrdCde.Value Then
               lDz = Cells(x, 4)
               lCs = Cells(x, 5)
               sUOM = Cells(x, 6)
               Chattemfrm.txtbxStckNum.Value = Cells(x, 7)
            End If
        Next x
    Next i
    If lDz = 0 Or lCs = 0 Or sUOM = "" Or Chattemfrm.txtbxStckNum.Value = "" Then
        Call ErrorTrap
    End If
    Chattemfrm.Show
End Sub

 

[RoryA]

Comes from the code below:

lDz = Cells(x, 4)           lCs = Cells(x, 5)

I can see how they are assigned - I wanted to know what values they actually hold (not what you think they should hold)

Do I need to declare those variables in the form?

No, you should not.

As Norie asked, where did you declare the variables - in a normal module, not a worksheet/thisworkbook?

 

[Pookiemeister]

The values that are actually held by the variables lDz and lCs should be numerical and I declared the variables in a normal module. Thank you.

 

[Pookiemeister]

I have a question. How does a VBA novice/newbie such as myself become as great as you and other experienced members on this forum? Are there any websites or literature that might be recommended to improve my VBA knowledge base? Thank you for your help on this project as well as previous ones.

 

[RoryA]

The values that are actually held by the variables lDz and lCs should be numerical

But what are they in actual fact? Have you verified them?

 

[RoryA]

There are numerous books you can read on the subject (assuming you learn well from books) and there are online courses you can take.

Personally I learned through books and forums like this (i.e. trial and error). Trying to solve other people's problems, and viewing how others do it, takes you out of your comfort zone and often into areas that you wouldn't normally use. Also, there's no substitute for experience.