Getting add-in to act on file at open 1

[mkasson]

I'm setting up a small Excel VBA add-in so that as soon as I open or create an Excel file (the "target&quot it will change from Excel's standard colors to our company's colors (colors good for color laser and also that can be distinguished when printed on a b/w laser, etc).

I tried starting the color changes in a Workbook_Open macro in the add-in's ThisWorkbook. It does this by calling a macro in Module1 with a series of lines like:

ActiveWorkbook.Colors(XLCP(i)) = CAx(i)
[XLCP and CAx are module level arrays dimmed as Long.]

Two problems:
1) When I open an Excel file I get "Run time error 91: Object variable or with block variable not set". There is no With block, so it must must be a variable reference problem - I'm guessing relating to ActiveWorkbook.

Notably, if I select the "Debug" option and then tell it continue having made no change, it continues with no error - though it does not make the changes to the file I am opening.
I am guessing that ActiveWorkbook is referring to the add-in's workbook and not my "target". I am guessing that the add-in macro gets run before the target file is loaded? And perhaps by the time I am debugging, the target file is loaded?

Tried MsgBoxing the number and names of the open Workbooks in the middle of the macro and my add-in is the one open Workbook.

2) And of course, the colors aren't getting loaded, though I think if we clear up 1, 2 will follow.

Any help would be greatly appreciated. Much thanks.


- MSK

 

[combo]

Hi,

You need to use excel application events to trace 'Open' and 'New' events for every used workbook.
To do this, you need, in the add-in:
1) a class module (say clsSCP) with something like:
[tt]Public WithEvents App As Application

Private Sub xlApp_NewWorkbook(ByVal Wb As Workbook)
call SetColorPalette(Wb)
End Sub

Private Sub xlApp_WorkbookOpen(ByVal Wb As Workbook)
call SetColorPalette(Wb)
End Sub

Private Sub SetColorPalette(Wb as Workbook)
' set colours for Wb
End Sub[/tt]

2) a module with class initialization:
[tt]Dim xlApp As New clsSCP

Sub InitializeAppEvents()
Set xlApp.App = Application
End Sub[/tt]

3) automatic call of (2) in Thisworkbook module of the add-in:
Private Sub Workbook_Open()
Call InitializeAppEvents
End Sub

BTW, you can save a workbook with customized palette as a template, when saved with specific name and location, will be used as default new workbook, see 'templates' topic in help file for details.

combo

 

[mkasson]

Thanks for the quick and explicit reply. How do the procedures xlApp_NewWorkbook and xlApp_WorkbookOpen get called and what workbook is being specified as the parameter? The problem I have seems to be not knowing how to specify the target's workbook when the add-in is executing its Workbook_Open.

When the Workbook_Open procedure is running the Workbooks.Count is 1 and Workbooks(1).Name is my .xla file. Workbooks(2) gives an error (out of range, I believe).

The template thought is a good idea (that hadn't occurred to me), but we're also looking to convert the spreadsheets that we already have in use.

Again, thanks for the help.

- MSK

P.S. I haven't really worked with classes, so I'm sorry if the answer lies in how classes work.

- MSK

 

[combo]

Workbook_Open procedure in an add-in (in add-in's ThisWorkbook module) runs when application starts and opens installed add-in or when you choose to install the add-in.
When you add a class with 'Public WithEvents App As Application' at the top, from the left drop-down of class module you can select App, from the right one application events. Among them you can find NewWorkbook and WorkbookOpen. You also get an handler (Wb) to newly created or opened workbook, as application events are global for excel instance. So if you use (after creating arrays in class):

[tt]Private Sub SetColorPalette(Wb as Workbook)
' ...
Wb.Colors(XLCP(i)) = CAx(i)
' ...
End Sub[/tt]

procedure will change workbook returned by Wb variable, i.e. newly opened or created, and will be executed every time when necessary, triggered by events.

I have not checked if this works for the first workbook created when you open excel, but it is also possible to use other events as WorkbookBeforePrint for instance.
You can also flag workbook custom properties to avoid execute procedure on workbook with already changed palette.

I am suprised that you get counted add-in with Workbooks.count property. It never return add-ins unless the workbook extension was changed manually to .xla instead of saving as an add-in (last item in available file formats).

combo

 

[mkasson]

combo,

I've been using the event handlers all along (Workbook_Open and Workbook_Activate). Does this need to be done using a class? I'm not well versed in their use and I'm not seeing the difference between using class or procedure calls.

In any event, it's still not working.
1) I still don't see how Wb in your example is getting assigned a value.

2) I still get the error 91 (see above). It continues correctly if I click Debug, then continue it. It works fine when I open Excel with no file. It has the problem if I open Excel by double clicking on an Excel file (e.g. on the desktop or in Windows Explorer).
When I continue past the error 91, it shows
ThisWorkbook.Name = addin file name
ActiveWorkbook.Name = target file name (e.g. Book1 for new file)
Count = 1
3) If I open an existing file via File Open, there is no action taken on that file's colors.

Thanks so much for your help.

FYI, the add-in was created by saving it as an add-in (last item on list).

- MSK

 

[combo]

MSK,

first of all sorry for some mess in my code, all event procedures should start with:
Private Sub App_.... instead of Private Sub xlApp_ (NB, they are created automatically, it is consistent with application event names created by VBA).

when you are in ThisWorkbook or worksheet (also chart sheet) module, you can select the object from the left drop-down and event from the right one and write event handler. They are workbook/sheet specific events. They can be used in excel without classes.

You can use Worksheet_Change event on the sheet level. Workbook_SheetChange event in ThisWorkbook module traps Change event on any workbook sheet. Its full syntax (I added second line):

[tt]Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Msgbox Sh.Name
End Sub[/tt]

The Sh argument is returned by event and can be used to interact with Sheet object firing event.

If you open Object Browser, select excel library and Application class, you can find many application events. You can access them, but there is no Application module to select any, it has to be created (same is for charts on worksheet or QueryTables).

To control application level events (i.e. not workbook/worksheet specific) you need class module with "...WithEvents App As Application" declaration (or anything other than App). You can declare custom events (by selecting App and event) in this class in the same way you use workbook events. But to make it work you also need an instance of this class assigned with Application, this is what the rest of the code does.
When excel fires event it will return Wb variable to this class, it is up to you what you will do with it.

Now your questions:
1) as i mentioned, it is excel that gives you Wb as reference to workbook

2) do not refer to ThisWorkbook or ActiveWorkbook in your code, excel gives you (via event procedure) a workbook (Wb) you need. Handle it via event procedure in the class module. (Try to trap errors, don't know if it fires for add-ins, can have problems for protected workbooks).

3) seems that the class is not initialized (can be due to my mistake, see the top).
Set xlApp.App = Application
has to be first executed somehow.

combo