Identifying workbooks 
Author Message
 Identifying workbooks

I have a workbook which adds a menu to the excel menu bar when the
workbook is opened and removes it when it is closed.
The users now say they want to open many instances of the workbook as
different files to copy data between them.
My problem then is how to ensure only one menu is created and used and
how to ensure it is deleted when the last workbook is closed.
I can check if the menu exists on opening and only create if it does not
but how can I check that closing this workbook closes the last instance
of the workbook type that requires the menu. Other workbooks may still
be open.
I had thought I could add some identifying data to the workbook that
requires the menu and when closing check if more than one exists but I
could not work out how to do this.
Any help would be gratefully received.

--
Alan Beal
Printrak International Ltd. UK (0) 1202 862023
A stupid question is better than a stupid mistake.



Wed, 18 Jun 1902 08:00:00 GMT  
 Identifying workbooks
This event proceedure will look for a certain value in sheet 1 of every open
workbook.  If it finds it, the proceedure is exited before getting to the
line DeleteMenu.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim WB As Workbook
For Each WB In Workbooks
If WB.Sheets(1).range("A1") = "Expense Sheet" Then
    Exit Sub
Next
DeleteMenu
End Sub

But, I think if the workbook that created the menu is deleted, the menu will
still point to macros in the closed workbook.  You will have to test.


Quote:
> I have a workbook which adds a menu to the excel menu bar when the
> workbook is opened and removes it when it is closed.
> The users now say they want to open many instances of the workbook as
> different files to copy data between them.
> My problem then is how to ensure only one menu is created and used and
> how to ensure it is deleted when the last workbook is closed.
> I can check if the menu exists on opening and only create if it does not
> but how can I check that closing this workbook closes the last instance
> of the workbook type that requires the menu. Other workbooks may still
> be open.
> I had thought I could add some identifying data to the workbook that
> requires the menu and when closing check if more than one exists but I
> could not work out how to do this.
> Any help would be gratefully received.

> --
> Alan Beal
> Printrak International Ltd. UK (0) 1202 862023
> A stupid question is better than a stupid mistake.




Wed, 18 Jun 1902 08:00:00 GMT  
 Identifying workbooks
Hi ALan
Your question is interesting since I am encountering the same problem.
May be one solution would be to chek the # of windows called
"TheNameOfYourWorkBook" and then until last is closed keep the menu visible?
May be start with somthing like :
For each W in Application.Windows
    If W.Name = "TheNameOfYourWorkBook" then
----- I don't know what next?
HTH
Pascal
France


Quote:
> I have a workbook which adds a menu to the excel menu bar when the
> workbook is opened and removes it when it is closed.
> The users now say they want to open many instances of the workbook as
> different files to copy data between them.
> My problem then is how to ensure only one menu is created and used and
> how to ensure it is deleted when the last workbook is closed.
> I can check if the menu exists on opening and only create if it does not
> but how can I check that closing this workbook closes the last instance
> of the workbook type that requires the menu. Other workbooks may still
> be open.
> I had thought I could add some identifying data to the workbook that
> requires the menu and when closing check if more than one exists but I
> could not work out how to do this.
> Any help would be gratefully received.

> --
> Alan Beal
> Printrak International Ltd. UK (0) 1202 862023
> A stupid question is better than a stupid mistake.




Wed, 18 Jun 1902 08:00:00 GMT  
 Identifying workbooks

I had not thought of the wrinkle of what the menu would point at.
Could I make the code an addin that was installed when the first workbook was
opened and deleted when the last was closed? That would still leave me with the
problem of identifying how many of the workbooks were open when I closed one.
The problem is that the files are not necessarily named the same they are just
instantiations of the same type which require my application to work on them
Next question then would be how do I make an addin and how do I install and
deinstall it programatically?
Here is a scenario, can anyone tell me if it would work or what to watch out
for.
When the workbook is opened it checks to see if the addin has been installed and
if yes it calls the load menu macro. If it has not then it installs the addin
and then loads the menu.
Each load of the menu increments a count.
As each workbook closes it unloads the menu.
Each menu unload decrements the count and when it reaches zero deinstalls the
addin.
If this scenario would work then more help would be needed to identify addins as
being installed and how do I keep a count between the load menu and unload menu
routines, would a variable declared outside any routine remain between routines.

Quote:

> This event proceedure will look for a certain value in sheet 1 of every open
> workbook.  If it finds it, the proceedure is exited before getting to the
> line DeleteMenu.

> Private Sub Workbook_BeforeClose(Cancel As Boolean)
> Dim WB As Workbook
> For Each WB In Workbooks
> If WB.Sheets(1).range("A1") = "Expense Sheet" Then
>     Exit Sub
> Next
> DeleteMenu
> End Sub

> But, I think if the workbook that created the menu is deleted, the menu will
> still point to macros in the closed workbook.  You will have to test.



> > I have a workbook which adds a menu to the excel menu bar when the
> > workbook is opened and removes it when it is closed.
> > The users now say they want to open many instances of the workbook as
> > different files to copy data between them.
> > My problem then is how to ensure only one menu is created and used and
> > how to ensure it is deleted when the last workbook is closed.
> > I can check if the menu exists on opening and only create if it does not
> > but how can I check that closing this workbook closes the last instance
> > of the workbook type that requires the menu. Other workbooks may still
> > be open.
> > I had thought I could add some identifying data to the workbook that
> > requires the menu and when closing check if more than one exists but I
> > could not work out how to do this.
> > Any help would be gratefully received.

> > --
> > Alan Beal
> > Printrak International Ltd. UK (0) 1202 862023
> > A stupid question is better than a stupid mistake.


--
Alan Beal
Printrak International Ltd. UK (0) 1202 862023
A stupid question is better than a stupid mistake.



Wed, 18 Jun 1902 08:00:00 GMT  
 Identifying workbooks
Although I am still looking for a more elegant solution I have solved the problem
temporarily by adding the macros to the personal.xls files for the few users who
will be using them.
Now when excel starts it automatically opens the personal.xls file and executes the
open macro which loads the menus.
When excel closes it unloads the menus.
I know this means they will be loaded whenever excel opens but the users are happy
while I research the more elegant solution.
Thanks for all your help peoples, please keep suggestions coming for the more
elegant solution which does not require personal.xls but works automatically.

--
Alan Beal
Printrak International Ltd. UK (0) 1202 862023
A stupid question is better than a stupid mistake.



Wed, 18 Jun 1902 08:00:00 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. Identify workbook in Code

2. Identifying Workbook Contents

3. Identify data using ADO to read workbook

4. Identifying template used to create a workbook

5. Identifying links to an external workbook in a cell

6. copy sheets to new workbook, name new workbook by cell value then mail workbook

7. Macro to open a workbook, work in that workbook, than close the workbook (saved)

8. Return Workbooks Template from workbook

9. workbook opening another workbook

10. Merge multiple workbooks into one single workbook


 
Powered by phpBB® Forum Software © phpBB Group