Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Two AddIn Questions
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Two AddIn Questions
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" wrote in message ... 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 --- 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Two AddIn Questions
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Two AddIn Questions
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Answers to questions posing more questions in a workbook | Excel Worksheet Functions | |||
View Questions and Answer to questions I created | Excel Discussion (Misc queries) | |||
MS VB addin err | Excel Worksheet Functions | |||
My addin in other computers | Excel Worksheet Functions | |||
Addin Help | Excel Discussion (Misc queries) |