variables declared public won't pass value to another userform

Pookiemeister

Active Member
Joined
Nov 26, 2015
Messages
306
Office Version
  1. 365
  2. 2010
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.

Code:
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:
Code:
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
Code:
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
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,517
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
Office Version
  1. 365
Platform
  1. Windows
Where have you declared the public variables?
 

Pookiemeister

Active Member
Joined
Nov 26, 2015
Messages
306
Office Version
  1. 365
  2. 2010
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.

Code:
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

Code:
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:
Code:
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

Active Member
Joined
Nov 26, 2015
Messages
306
Office Version
  1. 365
  2. 2010

ADVERTISEMENT

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.
Code:
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,517
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Comes from the code below:
Code:
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

Active Member
Joined
Nov 26, 2015
Messages
306
Office Version
  1. 365
  2. 2010

ADVERTISEMENT

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.
 
Last edited:

Pookiemeister

Active Member
Joined
Nov 26, 2015
Messages
306
Office Version
  1. 365
  2. 2010
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.
 
Last edited:

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,517
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,517
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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. ;)
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,251
Messages
5,527,636
Members
409,778
Latest member
MagalieD

This Week's Hot Topics

Top