Passing variable due to worksheet_change

[George J]

Never been too good at passing variables, but can usually keep it simple enough to get by. Not this time however.

I have a macro that works okay and part of this calls another macro. The second macro passes 2 public variables and this seems to work okay. As part of the second macro however I need to get another variable passed back to the first macro if certain conditions are met on Worksheet_Change. And that is how you tie yourself into a knot.

Public blnExport as boolean

sub Reports()
'do code, nice and simple
call SLInc(wkbkV, wkbkS)
debug.print blnExport
end sub

Sub SL_Inc(wkbkV As Workbook, wkbkS As Workbook)
'do my code
If CoNm = 5 and Origin > 3 then shtSLI.Range("D65536").end(xlup).value = CoNm.address
'so the worksheet_change event may fire at this stage
end sub

Public Sub Worksheet_Change(ByVal Target As Range)
'do code
blnExport = true
debug.print blnExpoert
end sub

So the report subroutine runs and calls the SLInc subroutine while passing 2 variables. If the SLInc subroutine results in a specific change to the workbook, blnExport = True. I am trying to pass that value back to the Reports subroutine. I have blnExport set as a Public variable in a module.

Does this make sense?
If anyone can provide inspiration it would be appreciated.

 

[George J]

Many thanks to all who looked at this.

Finally managed to get it working.
When I set the public variables in the new workbook, I added a reference to wkbkMaster's project.
After this was set, I was able to add a line after blnExport = True so that Project1.blnExport = True which updated the variable in the original workbook.

Sub GlobalSetup()
Dim CallingBook As String
    CallingBook = Evaluate("CallingBook")
    Set wkbkMaster = Application.Workbooks(CallingBook)
    Set newwkbk = Application.ThisWorkbook
    newwkbk.VBProject.References.AddFromFile wkbkMaster.FullName
End Sub

This was definitely not what I thought the original problem was. I'd probably still be picking through it if you guys hadn't confirmed that the example should work.
I appreciate the assistance.

 

[GTO]

Hi George,

Well, I am glad you got it working. As I was able to follow, it seems like you are using one instance of Excel, and as I just got done typing it, here's another possibility: Using a Class and checking the worksheet_change at Application level.

In a new Class named: clsApplication

Option Explicit
  
Public WithEvents ThisApplication As Excel.Application
  
Private WB As Workbook
  
' Public to allow setting the value from outside the Class
Public Property Set WBofInterest(w As Workbook)
  Set WB = w
End Property
  Public Property Get WBofInterest() As Workbook
    Set WBofInterest = WB
  End Property
  
Private Sub ThisApplication_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  
  'If the sheet's parent, i.e. the workbook, is the same object as our workbook of interest, set the flag
  If Sh.Parent Is WBofInterest Then blnExport = True
  
End Sub

In ThisWorkbook(to initialize the Class)

Option Explicit
  
Private Sub Workbook_Open()
  
  Call InitializeApplicationEvents
  
End Sub

In a Standard Module named: basMain

Option Explicit
  
Private MyExcelApp As clsApplication
  
Public blnExport As Boolean
  
Public Sub InitializeApplicationEvents()
  
  'Create a new instance of the Class and Set ThisApplication in the Class
  Set MyExcelApp = New clsApplication
  Set MyExcelApp.ThisApplication = Excel.Application
  
End Sub
  
Public Sub exampleReports()
Dim wbS As Workbook
Dim wbV As Workbook
Dim wbNew As Workbook
  
  Set wbS = Application.Workbooks.Open(Filename:=ThisWorkbook.Path & "\wbS.xls", UpdateLinks:=False, ReadOnly:=True, AddToMru:=False)
  Set wbV = Application.Workbooks.Open(Filename:=ThisWorkbook.Path & "\wbV.xls", UpdateLinks:=False, ReadOnly:=True, AddToMru:=False)
  
  'Call, passing our 2 open wb's and one that is currently Nothing
  Call exampleSLInc(wbS, wbV, wbNew)
  
  If blnExport Then
    MsgBox "Save wbNew or something?", vbQuestion, vbNullString
  Else
    wbNew.Close False
  End If
  
  wbS.Saved = True
  wbS.Close False
  wbV.Saved = True
  wbV.Close False
  
End Sub
  
Private Sub exampleSLInc(ByVal wb1 As Workbook, ByVal wb2 As Workbook, ByVal wbNew As Workbook)
Dim ws1   As Worksheet
Dim ws2   As Worksheet
Dim rng1  As Range
Dim rng2  As Range
Dim Cell  As Range
Dim n     As Long
  
  'Add one-sheet wb
  Set wbNew = Workbooks.Add(xlWBATWorksheet)
  Set MyExcelApp.WBofInterest = wbNew
  
  'Set source ranges
  Set ws1 = wb1.Worksheets(1)
  Set ws2 = wb2.Worksheets(1)
  Set rng1 = ws1.Range("A2:A6")
  Set rng2 = ws2.Range("A2:A6")
  
  'just to watch whilst stepping thru
  wb2.Activate
  
  'Changes that should not change our flag
  For Each Cell In rng2.Cells
    Cell.Value = Cell.Value + 10
  Next
  
  'Chages that will change our flag (only once actually)
  For Each Cell In wbNew.Worksheets(1).Range("A2:A6").Cells
    n = n + 1
    Cell.Value = rng1.Cells(n).Value + rng2.Cells(n).Value
  Next
  
End Sub

With example data in wbS.xls and wbV.xls like:

Excel Workbook
	A
1	wbS
2	10
3	100
4	1000
5	10000
6	100000
Sheet1

Excel 2010

Excel Workbook
	A
1	wbV
2	1
3	2
4	3
5	4
6	5
Sheet1

Excel 2010

Not sure if that's any help at this point, but in case...

Mark

 

[George J]

Thanks for that Mark.

Some interesting code there I will need to play around with later. I appreciate the time and effort spent on this.

 

[GTO]

You are most welcome and Happy Coding