OK, the second example and this is my preferred method.
From the previous example, replace all of the code in the module (Including the Public oWorkbook as Workbook statement) with this code:
<pre>
Public Sub FindWorkbook()
'Declare Variable locally
Dim oWorkbook As Workbook
If Workbooks.Count = 2 Then 'If there are only two workbooks open
For Each oWorkbook In Workbooks 'Cycle through the workbooks collection
If Not oWorkbook.Name = ThisWorkbook.Name Then 'If the name of the workbook does not equal thisworkbook, then we have found the other book
Exit For
End If
Next
Else
MsgBox "There is only one work book open.", vbInformation, "Workbook Information"
End If
DisplayWorkbookName oWorkbook 'Call sub routine in the other module and pass oWorkbook as the workbook the subroutine requires
End Sub</pre>
Now in replace the cod ein step 5 with this:
<pre>
Public Sub DisplayWorkbookName(ByRef oWorkbookDifferentVariableNameButStillSameObject As Workbook)
MsgBox oWorkbookDifferentVariableNameButStillSameObject.Name
End Sub</pre>
Now save (as a different name as the first example), close the book and then open it. The code will work just the same. the difference this time is that there are none of those horrible global variables.
The way the second one works is:
You declare the oWorkbook as a local variable, but the subroutine "DisplayWorkbookName" now requires a valid workbook to be passed to the subroutine as well (in this case oWorkbook). In the subroutine declaration for "DisplayWorkbookName" we basically tell the subroutine that it is to ask for a workbook to be provided for it to work with. i.e. the Byref....bit. Now I put in a long and stupid name just for emphasis "oWorkbookDifferentVariableNameButStillSameObject" but basically, we could have called this "oWorkbook" as well, but it would be a different variable to module 1's oWorkbook because both of the variables have been declared at the subroutine level.
Well, I hope this is what you need and I hop that this helps.
Cheers
