I'm still kind of new to this site. So I'm not sure what the forum rules are because the code below goes with topic we were discussing in regards to passing values. If I am wrong, please let me know and I'll re-post in another thread. Thank you.
Here's why I asked how to pass values. In the code below the variable sPrdCde needs to be passed to other forms and a module from this form. This is also why I mentioned about passing a value with the command button.
The code below appears first.
Private Sub cmdbtnDone_Click()
'Declare variables
Dim r As Range, r1 As Range
'Sets variables to a range
Set r = Range(RefEdit1)
Set r1 = r(1)
wbName = ActiveWorkbook.Name
Load Chattemfrm
Chattemfrm.cmbSDPFLine.Value = ActiveWorkbook.Name
Chattemfrm.txtbxPrdctNm.Value = r1.Offset(0, -5).Value
sPrdCde = r1.Offset(0, -6).Value
Chattemfrm.txtBxLtNum.Value = r1.Offset(0, -3).Value
Chattemfrm.txtBxShopNumber.Value = r1.Offset(0, 1).Value
Chattemfrm.txtbxVndrLtNu.Value = r1.Offset(0, -2).Value
Chattemfrm.txtbxdz = Me.txtbxRangeTotal.Value
Chattemfrm.cmbPrdCde.Value = sPrdCde & " " & "(" & Chattemfrm.txtbxPrdctNm & ")"
Call ReName
Unload Me
Workbooks(wbName).Activate
ActiveWorkbook.Close
Call Test
'Chattemfrm.Show
End Sub
The code below appears second and is from a module also this is where I declared sPrdCde.
Also, I'm not sure why the declared Public variables in the code below won't pass to frmAddProduct and for what ever reason I had to declare the sPrdCde again
Module:
Option ExplicitPublic lDz As Long, lCs As Long
Public sUOM As String, lStckNum As String
Sub Test()
Dim ws_count As Integer, i As Integer, FinalRow As Integer, x As Integer
Dim sPrdCde As String
lDz = 0
lCs = 0
sUOM = " "
lStckNum = ""
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 = sPrdCde 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()
Dim sPrdCde As String
txtbxPrdctCde.Value = sPrdCde
txtbxDescription.Value = Chattemfrm.txtbxPrdctNm.Value
If lDz = 0 Then
txtbxDzPrCs.Enabled = True
Else
txtbxDzPrCs.Value = lDz
txtbxDzPrCs.Enabled = False
End If
If lCs = 0 Then
txtbxCsPerPal.Enabled = True
Else
txtbxCsPerPal.Value = lCs
txtbxCsPerPal.Enabled = False
End If
If lStckNum = "" Then
frmAddProduct.txtbxStckNum.Enabled = True
Else
frmAddProduct.txtbxStckNum = Chattemfrm.txtbxStckNum.Value
frmAddProduct.txtbxStckNum.Enabled = False
End If
If sUOM = " " Then
frmAddProduct.optDz.Enabled = True
frmAddProduct.optEa.Enabled = True
ElseIf sUOM = "DZ" Then
Me.optDz.Value = True
Me.optDz.Enabled = False
Me.optEa.Enabled = False
Else
Me.optEa.Value = True
Me.optDz.Enabled = False
Me.optEa.Enabled = False
End If
End Sub
Please let me know if you have any questions. Thank you all for your gracious help on what I am to assume will be a "pulling out my hair" project.