VBA from WB gets tangled with any other WB that is open

[picklefactory]

Good morning all
Before I start posting tons of code, I thought I'd keep the question basic initially, in case there is a simple solution/issue I should be looking for. (Most of my questions tend to be, like me, rather simple)
OK, I have a WB that works fine when it is the only Excel file currently open on a pc. However, if there is any other, totally unrelated WB currently also open on the same machine it seems to get tangled and doesn't fully function or close cleanly. As it is a WB that is used across multiple machines, I have it closing down once the task is completed and I am also using an OnTime event to close it down if it is idle for an excessive period.
Is there a common issue or tactic I should be using to keep the code in this WB exclusive to this WB?
Cheers folks

 

[NdNoviceHlp]

Hi picklefactory. I suspect that you are using active workbook/active sheet in your coding. Be specific with your code especially if you may have more than one wb open or if the wb is on one drive ....

 ThisWorkbook.Sheets("Sheet1").Range("A" & 1).Value

HTH. Dave

 

[picklefactory]

Thanks Dave
That's highly likely. I'll go through my code and amend to be specific and see how that goes before I post back...... might be a day or two for that with workload.
Thanks for the hint

 

[picklefactory]

OK, I've finally got away long enough to look at this with Dave's advice and I think it's solid advice. My next issue is not being experienced enough to know how to correct my code to avoid that particular pitfall. I have numerous lines of code in various modules where my lack of knowledge/bad practice is evident. I can now see many instances of ActiveSheet, ActiveWorkbook, ThisWorkbook etc
Such as:

ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=folderPath & pdfFileName

Application.DisplayAlerts = False
ThisWorkbook.Save
Application.DisplayAlerts = True
Application.Quit

ActiveSheet.Range("C25").Value = Worksheets("Employee List").Range("J10").Value

    ActiveWorkbook.Close Savechanges:=True

What is the best way to make my code more precise and selective to a specific workbook or sheet or range so any other open workbooks cannot be dragged in accidentally?
Cheers

 

[NdNoviceHlp]

Hi again picklefactory. You will need to determine a main wb and secondary wbs. The main wb should be referred to as thisworkbook in code and should contain the code to open/close secondary wbs which you should set to some name when you open them. Replace activewb with either thisworkbook or the secondary wb name. To refer to sheets, use the line of code I originally provided altering the wb part as needed. You only need one application, so no need to alter the code, however you could just close the wb and leave the application running if it suits. Here's some code examples. HTH. Dave

Dim ThisWb As Workbook, Wb2 As Workbook, ws As Worksheet, wb2ws As Worksheet
Set ThisWb = ThisWorkbook
Set ws = ThisWb.Sheets("Sheet1")

'create new wb
Set Wb2 = Workbooks.Add
Set wb2ws = Wb2.Worksheets("Sheet1")

ThisWb.ExportAsFixedFormat Type:=xlTypePDF, Filename:=folderPath & pdfFileName
Wb2.ExportAsFixedFormat Type:=xlTypePDF, Filename:=folderPath & pdfFileName

wb2ws.Range("C25").Value = ThisWb.Sheets("Employee List").Range("J10").Value
ws.Range("C25").Value = ThisWb.Sheets("Employee List").Range("J10").Value

 

[picklefactory]

Thank you Dave
I only want the code to relate to a single wb, and just avoid affecting or being affected by any other unrelated wb that just happens to be open on the same machine. Am I correct in that your code would still be fine for that and I just wouldn't require the secondary wb. Can I be more definitve in the

Set ThisWb = ThisWorkbook

and instead of using ThisWorkbook, specify a workbook name or path and name?
Thanks again

 

[NdNoviceHlp]

If your wb doesn't open/close other wbs, there's probably no need to set a name, just use ThisWorkbook. However, if the other open wbs use activewb/activesheet etc. and interact with your "main" wb then there still may be problems. Dave

 

[picklefactory]

If your wb doesn't open/close other wbs, there's probably no need to set a name, just use ThisWorkbook. However, if the other open wbs use activewb/activesheet etc. and interact with your "main" wb then there still may be problems. Dave

So I really need to go through all of my wbs with code (There aren't many in shared use) and correct my wording. So just to be clear for a dumbass amateur, ThisWorkbook is pretty safe but I should avoid ActiveWorkbook etc?

 

[NdNoviceHlp]

"ThisWorkbook is pretty safe but I should avoid ActiveWorkbook " Always and then you won't have these issues. Dave

 

[picklefactory]

"ThisWorkbook is pretty safe but I should avoid ActiveWorkbook " Always and then you won't have these issues. Dave

Thanks again. I'll go through the offending articles, update as best I can and test. I'll mark this as solved for now........ I'll probably be back in 10 minutes with a different issue