That was very kind.....a big help!

Regards.

"Stephen Bullen" wrote in message
...
Hi Stuart,

In my situation, I think your solution of 'tagging' the file will
prove best-suited.

Could you help a little further please?

To be much more help, we have to get into specific examples instead of
describing the general approach, so let's say we have an addin that adds
its
own menu item to the worksheet menu bar, on which are two menus for 'Tag
Me'
and 'Do Something', where the 'Do Something' menu is only enabled for

So, let's start with a standard module that creates the menu items,
handles
their being clicked and add a routine to enable/disable the 'active' menu
items:

Option Explicit

'An instance of our application event hook class
Dim moAppEvents As CAppEvents

'A collection of the 'active' menu items
Dim moControls As Collection


Sub Auto_Open()

'Set up our menus
SetUpMenus

'Instantiate our application event handler class
Set moAppEvents = New CAppEvents

End Sub

Sub Auto_Close()
DeleteMenus
End Sub

Sub SetUpMenus()

Dim oCtl As CommandBarButton

DeleteMenus

'Add our main menu
With CommandBars("Worksheet Menu Bar").Controls.Add(msoControlPopup, _
, , , True)

.Caption = "My Addin"

'We just add the first menu that's always available
'The rest are 'active' depending on whether the workbook is tagged
With .Controls.Add(msoControlButton, , , , True)
.Caption = "Tag Me"
.OnAction = "TagWorkbook"
.Enabled = True
End With

'Initialise the collection of 'active' menu items
Set moControls = New Collection

'Create an 'active' menu item
Set oCtl = .Controls.Add(msoControlButton, , , , True)
With oCtl
.Caption = "Do Something"
.OnAction = "DoSomething"
.Enabled = False
End With

'And add it to the collection
moControls.Add oCtl

'Create more 'active' menus and add them to the collection here...
End With

End Sub

'Tidy up when closing etc
Sub DeleteMenus()
On Error Resume Next
CommandBars("Worksheet Menu Bar").Controls("My Addin").Delete
End Sub


'Mark the workbook as one we can do something with
Public Sub TagWorkbook()
On Error Resume Next
ActiveWorkbook.CustomDocumentProperties.Add "Tagged by My Addin", _
False, msoPropertyTypeBoolean, True

'We've tagged this workbook, so we should enable the menus
EnableMenus True
End Sub


'Do something!
Public Sub DoSomething()
MsgBox "Did Something!"
End Sub


'Enable/disable the 'active' menu items
Public Sub EnableMenus(bEnabled As Boolean)

Dim oCtl As CommandBarControl

For Each oCtl In moControls
oCtl.Enabled = bEnabled
Next

End Sub


Then all we need to do is add the application event handler class, so add
a
class module called CAppEvents and add the following code to it:


Option Explicit

'The application event handler
Dim WithEvents moXLApp As Application

'Hook the application when we're created
Private Sub Class_Initialize()
Set moXLApp = Application
End Sub


'Hook the event raised when switching between workbooks
Private Sub moXLApp_WorkbookActivate(ByVal Wb As Workbook)

Dim bEnabled As Boolean

'Check if the workbook has been 'tagged',
'using OERM in case the property doesn't exist
On Error Resume Next
bEnabled = Wb.CustomDocumentProperties("Tagged by My Addin").Value

'Enable/disable the 'active' menus
EnableMenus bEnabled

End Sub


That's it. Create a load of new workbooks and 'tag' a few of them. As
you
switch between them, you should see the menu items being enabled/disabled
as
appropriate. Note that in this example, we've done workbook-level tagging
using a custom document property. We could alternatively do sheet-level
tagging using defined names and the _SheetActivate event or even
range-level
tagging using defined names and the _SheetSelectionChange event.


Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.co.uk



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.502 / Virus Database: 300 - Release Date: 18/07/2003