Two AddIn Questions

[Stuart]

a) Can a file opened by user under Excel be checked such that:
a loaded Addin will not allow its' menu code to run on that file
(ie only run if the file opened under the addin menu)

b) 3 nr Addins loading, and I would prefer to keep each separate
(rather than share routines).
If I create a Class module containing Sheet_Change Event
code and declare a Public variable in that Class, for one of the
Addins, will it be visible across the Addins, and if so, how do
I limit use purely to files called by that particular Addin's menu.

Excel 2000 Win2k developing the addins
Some users with XL97

Regards and thanks.

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

[Stephen Bullen]

Hi Stuart,

> a) Can a file opened by user under Excel be checked such that:
> a loaded Addin will not allow its' menu code to run on that file
> (ie only run if the file opened under the addin menu)

The usual way to do this is the following:
1. When opening the file under addin control, the addin 'tags' the file
in some way, such as adding a custom document property or creating a
(hidden) defined name or some such.

2. The addin then has a class module to hook Application-level events
and checks for SheetActivate events. Whenever a sheet is activated, the
addin checks to see if the file is 'tagged' and enables or disables its
menus appropriately.

3. As an alternative to 2, the check for 'tagging' could be done in the
code for each of the addin's routines, so it wouldn't need to bother with
the enabling/disabling of menus.

> b) 3 nr Addins loading, and I would prefer to keep each separate
> (rather than share routines).
> If I create a Class module containing Sheet_Change Event
> code and declare a Public variable in that Class, for one of the
> Addins, will it be visible across the Addins, and if so, how do
> I limit use purely to files called by that particular Addin's menu.

Unless specifically designed and coded for, the variables will only be
available to that addin. If in point 1 above, each addin uses a
different 'tag', you'll be able to identify which addin opened which
file.

Regards

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

[Stuart]

Many thanks.

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

So:

> The usual way to do this is the following:
> 1. When opening the file under addin control, the addin 'tags' the file
> in some way, such as adding a custom document property or creating a
> (hidden) defined name or some such.

Would you be kind enough to give a simple example, or point me
somewhere, to get started, please?

> 2. The addin then has a class module to hook Application-level events
> and checks for SheetActivate events. Whenever a sheet is activated, the
> addin checks to see if the file is 'tagged' and enables or disables its
> menus appropriately.

I 'think' I can deal with the Class module code, but how to disable
(grey out?) the menu options, please? In my case, each addin has a
single entry in Excel's menubar, with a number of related dropdowns.

Could you help a little further please?

Regards and thanks.

"Stephen Bullen" <ste...@bmsltd.co.uk> wrote in message
news:VA.0000010...@bmsltd.co.uk...

[Stephen Bullen]

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.

[Stuart]

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

Regards.

"Stephen Bullen" <ste...@bmsltd.co.uk> wrote in message
news:VA.0000010...@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