I am having some trouble figuring out the best way to do something. I have 2 workbooks. Wb 1 has a macro to open Wb 2 and a macro to return to Wb 2 if one has already been open. Once in Wb 2, the user can save the file as a different name. They can also return to Wb 1.
My problem occurs when the user hits the return button in Wb 1 when there is no open Wb 2. My initial solution to this was to count the workbooks with:
This works fine until I already have another completely unrelated workbook open. Then the code counts that workbook as one and simply reveals it when I hide Wb 1. I hide Wb 1 to avoid referencing workbooks by name as the name may very.
First, I am open to any possible solution.
Second, my first thoughts were to either test other open workbooks for a sheetname (b/c that will be in all the workbooks I want open). This tells me that hiding Wb 1 will reveal the appropriate workbook.
I have changed the name of the workbook in the properties window from thisworkbook to Forms. This is unchanging regardless of the Filename. Is there someway to reference this from another workbook?
Or, is there someway to utilize part of a path to test. For example, can I test to see if other open workbooks are stored in the "patient files" folder and if so, then hide wb 1 to reveal wb 2?
Hope that made sense.
Any thoughts?
My problem occurs when the user hits the return button in Wb 1 when there is no open Wb 2. My initial solution to this was to count the workbooks with:
Code:
If Workbooks.Count = 1 Then
Workbooks.Open FileName:="C:\program files\reports to go\Forms.xls", UpdateLinks:=xlUpdateLinksAlways
Else
Application.ScreenUpdating = False
Range("d6").Select
ReportsToGo.Save
Windows("reports to go.xls").Visible = False
End If
End If
End Sub
First, I am open to any possible solution.
Second, my first thoughts were to either test other open workbooks for a sheetname (b/c that will be in all the workbooks I want open). This tells me that hiding Wb 1 will reveal the appropriate workbook.
I have changed the name of the workbook in the properties window from thisworkbook to Forms. This is unchanging regardless of the Filename. Is there someway to reference this from another workbook?
Or, is there someway to utilize part of a path to test. For example, can I test to see if other open workbooks are stored in the "patient files" folder and if so, then hide wb 1 to reveal wb 2?
Hope that made sense.
Any thoughts?