Calling Public/ Global Variables Between Modules (Inside Worksheet Change Code)

killpaddy

Board Regular
Joined
Jul 31, 2012
Messages
52
Hello,

All I need, is to be able to call the public variable 'lastrow2' from the module it is defined in to the sheet 1 object. I have the following code:

IN THE 'Sheet1(Drawings List)' OBJECT [I have omitted some irrelevant code]:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)


Dim lastrow As Long
Dim wb As String


wb = Workbooks(1).Name
If ThisWorkbook.Name = "DRAWINGS LIST UPDATE3.xlsm" Then


    lastrow = Worksheets("Drawings List").Range("A" & Rows.Count).End(xlUp).ROW
     
    If Not Target.Address <> "$A$" & lastrow And lastrow2 < lastrow Then
            
        Fill_In_Info lastrow
            
    End If
    
End If
      
End Sub
IN 'Module2' I simply have this:
Code:
Public DWGsinDP As Long
Public DWGsinDP2 As Long
Public lastrow2 As Long
The public variable seems to work in 'Module1' and updates its value, but not in the sheet1 object code.

Any help would be appreciated, thanks
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
That variable should be visible by all modules. Are you sure that it has been assigned a value?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,532
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
That should work as long as you have already run some code to assign a value to lastrow2
 

killpaddy

Board Regular
Joined
Jul 31, 2012
Messages
52
The first code to run is that in the Sheet1 object when I make a change on the sheet. A value for lastrow2 has not yet been assigned and when I run the code and hover over the variable it says 'lastrow2 = 0'.

The Sub 'Fill_In_Info' (located in module1) then runs and assigns lastrow2 a value (I hover over in this module and it says 'lastrow2 = 17', and switching to the sheet1 object it still says 'lastrow2 = 0'). The second time Worksheet_Change runs lastrow2 is still is equal to 0.

The Worksheet_Change Sub does get called several times in the middle of the Fill_In_Info Sub when changes are made, could this be the problem?

I have included the code I thought was irrelevant below, just in case it is relevant:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)


Dim lastrow As Long
Dim wb As String




wb = Workbooks(1).Name
If ThisWorkbook.Name = "DRAWINGS LIST UPDATE3.xlsm" Then


    lastrow = Worksheets("Drawings List").Range("A" & Rows.Count).End(xlUp).ROW
    
    If Not Range("A" & lastrow) = UCase(Range("A" & lastrow).Value) Then
        
        Range("A" & lastrow) = UCase(Range("A" & lastrow).Value)
        
    End If
     
    lastrow2 = lastrow2
    If Not Target.Address <> "$A$" & lastrow And lastrow2 < lastrow Then
            
        Fill_In_Info lastrow
            
    End If
    
End If
      
End Sub
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,532
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Unless you post the code for Fill_In_Info it's going to be hard to debug it.
 

killpaddy

Board Regular
Joined
Jul 31, 2012
Messages
52
OK, here it is. This code is located in Module1

Code:
Sub Fill_In_Info(lastrow)


Dim DateToday
Dim DWGCode As String
Dim colour As Boolean
Dim lastrow2 As Long


If DWGsinDP = 0 Then
    DWGsinDP = 1
    DWGsinDP2 = 0
End If


Range("B" & lastrow) = Range("B" & lastrow - 1).Value + 1


If DWGsinDP > 1 Then
    Range("C" & lastrow) = Range("C" & lastrow - 1).Value
Else
    Range("C" & lastrow) = Range("C" & lastrow - 1).Value + 1
End If


DateToday = Date
Range("K" & lastrow).Value = DateToday


Range("J" & lastrow).Value = Application.UserName


If Range("A" & lastrow).Value = "MZK" Then
    Range("L" & lastrow) = "Severn"
ElseIf Range("A" & lastrow).Value = "DAE" Then
    Range("L" & lastrow) = "Severn"
ElseIf Range("A" & lastrow).Value = "HAA" Then
    Range("L" & lastrow) = "Severn"
ElseIf Range("A" & lastrow).Value = "HAR" Then
    Range("L" & lastrow) = "Severn"
ElseIf Range("A" & lastrow).Value = "MOR" Then
    Range("L" & lastrow) = "Severn"
ElseIf Range("A" & lastrow).Value = "MUR" Then
    Range("L" & lastrow) = "Severn"
ElseIf Range("A" & lastrow).Value = "OKU" Then
    Range("L" & lastrow) = "Severn"
ElseIf Range("A" & lastrow).Value = "PDT" Then
    Range("L" & lastrow) = "Severn"
ElseIf Range("A" & lastrow).Value = "TLH" Then
    Range("L" & lastrow) = "Severn"
ElseIf Range("A" & lastrow).Value = "HWA" Then
    Range("L" & lastrow) = "Severn"
ElseIf Range("A" & lastrow).Value = "EXP" Then
    Range("L" & lastrow) = "Delaware"
ElseIf Range("A" & lastrow).Value = "CUB" Then
    Range("L" & lastrow) = "Delaware"
ElseIf Range("A" & lastrow).Value = "FXT" Then
    Range("L" & lastrow) = "Delaware"
ElseIf Range("A" & lastrow).Value = "CTR" Then
    Range("L" & lastrow) = "Delaware"
ElseIf Range("A" & lastrow).Value = "JAW" Then
    Range("L" & lastrow) = "Delaware"
ElseIf Range("A" & lastrow).Value = "SDY" Then
    Range("L" & lastrow) = "Delaware"
ElseIf Range("A" & lastrow).Value = "RUN" Then
    Range("L" & lastrow) = "Delaware"
ElseIf Range("A" & lastrow).Value = "CHK" Then
    Range("L" & lastrow) = "Delaware"
ElseIf Range("A" & lastrow).Value = "CYL" Then
    Range("L" & lastrow) = "Delaware"
ElseIf Range("A" & lastrow).Value = "VCE" Then
    Range("L" & lastrow) = "Delaware"
ElseIf Range("A" & lastrow).Value = "ZPT" Then
    Range("L" & lastrow) = "Delaware"
End If


lastrow2 = Worksheets("Drawings List").Range("A" & Rows.Count).End(xlUp).ROW


If DWGsinDP2 = 0 Or Not DWGsinDP2 = DWGsinDP Then
    DWGCode = vbNullString
    DWGCode = InputBox(prompt:="Do you have another drawing to add to the DP?" _
     & Chr(13) & "No. of drawings in DP so far: " & _
     DWGsinDP, Title:="ADD DRAWINGS TO DP?", _
     Default:="3 letter dwg code")
     DWGsinDP2 = DWGsinDP


End If


If DWGCode = vbNullString Or DWGCode = "3 letter dwg code" Then
    
Else
    
    DWGsinDP2 = DWGsinDP
    DWGsinDP = DWGsinDP + 1
    Range("A" & lastrow + 1).Value = DWGCode


End If


End Sub
Each time a change is made e.g.

'Range("B" & lastrow) = Range("B" & lastrow - 1).Value + 1'

The Worksheet_Change Sub runs again
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,532
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Remove this line from that sub:
Code:
Dim lastrow2 As Long
 

killpaddy

Board Regular
Joined
Jul 31, 2012
Messages
52
Found it. I had dimensioned lastrow2 locally at the top of Fill_In_Info. Works fine now ive removed that
 

Forum statistics

Threads
1,085,432
Messages
5,383,636
Members
401,842
Latest member
stevensmith1

Some videos you may like

This Week's Hot Topics

Top