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.

 

[BarryL]

whats the issue you're seeing with the above? whats the immediate window giving you for blExport?

 

[George J]

When sub Reports is run, the call to SLInc works okay (just noticed the underscore - ignore that)
When the worksheet change event is triggered, I get TRUE in the immediate window for this, but when the change event completes and the SLInc subroutine finishes, I then get FALSE in the immediate window for the last part of the Reports subroutine.

I don't know if I can pass a variable to the change_event or if I need to create another macro call from there to set the blnExport value.

 

[GTO]

Greetings,

I imagine you're posting pseudo code?

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

Where (what module) is the public variable and two subs kept; a Standard Module?

 

[George J]

Yes, pseudo code with typos. Sorry.
All in different Standard Modules.
Public variables in Module1, sub Reports in module3 and subSLInc in module2

 

[Norie]

You can't pass a variable to the Worksheet_Change event but you should be able to do what you appear to be doing, ie using a public variable, blnExport.

Is that not working for you?

If it isn't, how isn't it working?

 

[GTO]

In your real code, place Option Explicit at the top of all modules (including worksheet modules and ThisWorkbook) and see if it compiles without complaining. If that's fine, add a Watch and step thru the code. What's that show?

 

[George J]

Looks like I'll need to dig deeper.
I set up a dummy workbook as in my example above and got the blnExport returning True after both worksheet_change and the end of the reports.
My own workbook is returning true with the worksheet_change, but false at the end of the reports subroutine.
I guess the code isn't doing what I thought it was. Will have another look.

I can compile the code without any problem. I'll add a watch at both the blnExport in the worksheet event and in the module.

 

[Norie]

George

What is the purpose of blnExport?

 

[George J]

Okay, so the problem wasn't quite what I thought it was. I was trying to keep the example simple but it looks like I need to give the long, boring and involved breakdown of what the code is doing.

I have a main workbook (wkbkMaster). The user selects 2 reports, wkbkV & wkbkS to use the data to create multiple reports.
They then press the 'Compile' button on the sheet and the Reports subroutine runs.

The reports subroutine checks the client name selected from a dropdown and creates an array of sheets. This array is then copied which creates a new workbook. I transfer module 1 to the new workbook and run a "GlobalSetup" macro
Application.Run "'" & newwkbk.Name & "'!GlobalSetup"
I now have both workbooks able to determine which is newwkbk and wkbkMaster (the original where the reports code is currently running).

The code then opens wkbkV and wkbkS who's path had been preselected from choosing the client.

This is where it all falls down:
With the last of the reports code (in wkbkMaster), I populate newwkbk with data from wkbkV and wkbkS.
That is where the call SLInc comes in. The code populates the sheets okay, but as the code is running from wkbkMaster, when the required change is made in newwkbk and the worksheet_change event fires, it would not send the value of blnExport back to the wkbkMaster - it would be in the newwkbk.

So I have the newwkbk and it knows that wkbkMaster is the original where the code is running. How do I pass the variable from the newwkbk when the change_event is fired so I can use it in the wkbkMaster?

hahahahaha I need a drink!

p.s. I will be using the value of blnexport to determine whether a final macro is called or not.