Two AddIn Questions

  • Thread starter Thread starter Stuart
  • Start date Start date
S

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.
 
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.
Click to expand...

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.
Click to expand...

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.
 
Hi Stuart,
In my situation, I think your solution of 'tagging' the file will
prove best-suited.

Could you help a little further please?
Click to expand...

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
 
That was very kind.....a big help!

Regards.

Stephen Bullen said:
Hi Stuart,


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
Click to expand...
 
Back
Top