Hello, I am trying to use a variable from a module into a sheet function. The sheet is able to call the function in the module, but the data entered into the module doesn't stick when I try to pull it back. I really thought I had these setup correctly to share variables. Can someone help?
FYI! A Row in columns N, S, and U all need to have data in them. However, to get the code to work either enter data into column N last or double click into a cell in N with data already in it and hit enter. Like this:
Code In Sheet2 (There is a LOT more code to this, but I cut it down for ease of use):
Code In Module 2 (What I am calling into Sheet2):
FYI! A Row in columns N, S, and U all need to have data in them. However, to get the code to work either enter data into column N last or double click into a cell in N with data already in it and hit enter. Like this:
Code In Sheet2 (There is a LOT more code to this, but I cut it down for ease of use):
VBA Code:
Public r As Long
Public BiggerR As Integer
Public SmallerR As Integer
Public Sub Worksheet_Change(ByVal Target As Range)
Dim lastCol As Variant
Dim lastRow As Variant
Dim cel As Variant
Dim test As Variant
Dim iNum As Integer
Dim Questions As Integer
Dim Insulation As Integer
Dim InsType As Integer
If Not Intersect(Target, Range("N9:N16000")) Is Nothing Then
r = Target.Row
If IsEmpty(Sheet2.Range("S" & Sheet2.r).Value) = False And IsEmpty(Sheet2.Range("N" & Sheet2.r).Value) = False And IsEmpty(Sheet2.Range("U" & Sheet2.r).Value) = False And IsEmpty(Sheet2.Range("AG" & Sheet2.r).Value) = False Then
Checky = Application.InputBox("Do you want to update the component description?" & vbNewLine _
& "1: Yes I want to update the component description" & vbNewLine _
& "2: No, I don't want to update the component description", "Obtain Range Object", Type:=1)
Select Case Checky
Case 1
'MsgBox "this is a placeholder to leave the select"
GoTo MainBadie:
Case 2
'MsgBox "that's cool fam"
End Select
Else
GoTo MainBadie:
End If
Exit Sub
MainBadie:
If IsEmpty(Range("S" & r).Value) = False And IsEmpty(Range("U" & r).Value) = False Then
If Sheet2.Range("S" & Sheet2.r) Like "*Weld*" Then
PrePWHT = Application.InputBox("Does the component need any pre-weld heat treatment or post-weld heat treatment?" & vbNewLine _
& "1: Yes" & vbNewLine _
& "2: No", "Obtain Range Object", Type:=1)
Select Case PrePWHT
Case 1
Sheet2.Cells(Sheet2.r, 33).Value = "Y"
Case 2
Sheet2.Cells(Sheet2.r, 33).Value = "N"
End Select
Welding = Application.InputBox("Type how many welding spots there are for this size of pipe", "Obtain Range Object", Type:=1)
Sheet2.Cells(Sheet2.r, 25).Value = Welding
Sheet2.Cells(Sheet2.r, 26).Value = Welding * Sheet2.Cells(Sheet2.r, 21)
Sheet2.Cells(Sheet2.r, 23).Value = Welding * Sheet2.Cells(Sheet2.r, 21)
Sheet2.Cells(Sheet2.r, 24).Value = "DI"
Else
If Sheet2.Range("S" & Sheet2.r) Like "*Testing*" Then
GoTo TESTING_Call:
End If
'Sheet8.Activate
'On Error GoTo DumbErr:
'Set ItemRange = Range(Sheet8.Cells(2, 16).Value)
With ItemRange
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''This is where I call the function in the Module
Call Reducey
MsgBox Module2.SmallerR
End With
End If
End If
End If
Exit Sub
TESTING_Call:
Exit Sub
DumbErr:
Sheet2.Activate
MsgBox "You either canceled, or did not put a range in"
End SubCode In Module 2 (What I am calling into Sheet2):
VBA Code:
Public BiggerR As Integer
Public SmallerR As Integer
Public Sub Reducey()
Dim BiggerR As Integer
Dim SmallerR As Integer
Reduce = Application.InputBox("Does this component reduce/increase in size?" & vbNewLine _
& "1: Yes " & vbNewLine _
& "2: No", "Obtain Range Object", Type:=1)
Select Case Reduce
Case 1
BiggerR = Application.InputBox("Type Bigger Size in inches", "Obtain Range Object", Type:=1)
SmallerR = Application.InputBox("Type Smaller Size in inches", "Obtain Range Object", Type:=1)
MsgBox BiggerR
MsgBox SmallerR
Case 2
Exit Sub
End Select
End Sub
