Working with the Excel MDI
I have a project that I manage using multiple Excel Workbooks. To ease grouping the control of these workbooks, I have on a master Workbook, a sheet that provides buttons to open any of the workbooks in the group.
To work on the project I launch the master spreadsheet and open only the workbooks I currently need. I wouldn't want to open all of them at the same time, every time I dived into the project.
The buttons are linked to macros which provide the appropriate very simple VBA code to open the relevant Workbook.
e.g.
Sub WSIV() ' Link your master button to this subroutine using Assign Macro.
Workbooks.Open FileName:= _
"C:\Documents and Settings\me\My Documents\work\suppliers\invoices.xlsm"
End Sub
This has worked fine for many years, but the Multi Document Interface creates a tedious problem, in that as the number of workbooks that are open in the session increases, switching between them involves a hunt for the required window, usually totally obscured, by layers of other sheets.
If only I could see the required sheet and click on it, it would immediately bring it into focus.
Apart from sliding the upper windows out of the way to find the required sheet in the hope that the workbook is already opened and the next one revealed is the one being hunted. The alternative is to locate the sheet on the task bar, and if it is there click it, which is tedious in itself. It may only cost one click but also costs two distinct cursor positions, as well as the delay in building the array of miniature sheet views and selecting one.
I therefore searched for a better way and I believe I have found one. I worked out some code for the master sheet buttons that now open the sheet if not already open, and bring the sheet into focus if it is already open.
The result is that by keeping the master sheet in view or at least in the same position, and up against the edge of the window, on can quickly locate the button for the desired sheet, since it is always in the same position, double click it and the required sheet springs into view.
Getting the code working involved overcoming a couple of pitfalls. First of all there is no built in function to determine whether a Workbook is open. Also there is an apparent inconsistency in the naming of the workbook file - the workbook name doesn't include the full path, just the filename and extension.
Here is the code:
Sub WSIV() ' Link your master button to this subroutine using Assign Macro.
If IsOpen("invoices.xlsm") Then
' Bring the already open Workbook into focus.
Workbooks("invoices.xlsm").Activate
Else ' Open Workbook.. It will have focus.
Workbooks.Open FileName:= _
"C:\Documents and Settings\me\My Documents\work\suppliers\invoices.xlsm"
End If
End Sub
' Repeat as above for each button and filename
'...
'...
'...
' Common function to test if a given Workbook is already open.
Function IsOpen(FileName As String) As Boolean
Dim wb As Workbook
' Search all open Workbooks for the required one.
For Each wb In Application.Workbooks
If VBA.UCase(wb.Name) = VBA.UCase(FileName) Then
' Requested workbook is already open
IsOpen = True
Exit Function
End If
Next wb
' None found, requested workbook is not open
IsOpen = False
End Function
