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

[killpaddy]

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

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:

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]

That variable should be visible by all modules. Are you sure that it has been assigned a value?

 

[RoryA]

That should work as long as you have already run some code to assign a value to lastrow2

 

[killpaddy]

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:

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]

Unless you post the code for Fill_In_Info it's going to be hard to debug it.

 

[killpaddy]

OK, here it is. This code is located in Module1

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]

Remove this line from that sub:

Dim lastrow2 As Long

 

[killpaddy]

Found it. I had dimensioned lastrow2 locally at the top of Fill_In_Info. Works fine now ive removed that

 

[killpaddy]

Yes, thanks Rory