SheetChange event in add-in doesn't work: HELP

[Guest]

Hi all,

I have a strange issue programming an Add-In for excel 2002. Here is my problem:

I've created a workbook that create a new toolbar with some commands
In this workbook I have created an Event Listener using the WithEvent clause in a class module
In this class module I handle some of the Excel event eg. beforesave and sheetchange.
When a value in a cell of a worksheet is changed the SheetChange event is fired and I set by code the value of a couple of other cells.

Now if this workbook is saved as a normal workbook everything works fine, while if I save it as add-in (xla) and I try to execute the same code (in detail the code in the sheetchange event) it gives me a 1004 error.

Please any information would be usefull.
Thanks a lot to anyone
Regards
Massimiliano Garri

 

[Frank Kabel]

Hi Max
how do you reference the workbook / the cells within your Add-In. If
you use something like 'ActiveWorkbook' this could be the problem. Try
using
ThisWorkbook.activesheet.range(..)
for referencing the current workbook

 

[Guest]

Hi Frank

thanks for your answer but unfortunately it doesn't work
Thisworkbook reference the add-in (eg. xyz.xla

Here is my code so maybe you can give me another tip
Class Module
Private Sub BBApp_SheetChange(ByVal Sh As Object, ByVal Source As Range
'Sh the sheet where the event is fire
'Source the cell(s) where the event is fire
On Error GoTo ErrorHandle
If Source.Address = "$C$4" Then 'ACTIVE ONLY IF THE CELL MODIFIED IS THE "C4
Sh.Range("H12").Value = Source.AddressLoca
End I
Exit Su
ErrorHandler
MsgBox (Err.Number & Chr(10) & Err.Description)
Resume Nex
End Su

 

[Dave Peterson]

Your code worked ok for me (xl2002)--both as an addin and a normal .xls file.

Is there something special about that worksheet? Is it protected with that cell
locked?



And I'm wondering why you changed the definition of _sheetchange (from Target to
Source).

It worked ok for me using Source, but why change it?

Private Sub BBApp_SheetChange(ByVal Sh As Object, ByVal Target As Range)

is what excel provides if you choose from the dropdown.

 

[Guest]

Hi Dave

Thanks to your suggestion I've been able to get a little further

Now I can see that the difference is not between a normal xls workbook or an add-in (xla)
I made another try and I discovered that when the cell that the event is monitoring (in my example $C$4) is a normal cell everything works fine, but if the cell is handled by a list of possible value (made trough the Data > Validation menu) then i keep getting the same error

Thank you for any further comment you may provide
Massimiliano Garr

P.S. I changed the "Target" to "Source" as it does means more to me (it is the source of the event)

 

[Dave Peterson]

Even though the Target to Source worked, I wouldn't do it. Someday, you'll
wonder what the heck is going on and why you did it. (Or the guy/gal modifying
your addin will be swearing at you for being non-standard.)

I just tried a simple worksheet change that had data|validation (list: a,b,c)
in H12.

The code was allowed to change that cell and ignore the Data|validation rules.

Both .xla and .xls worked ok. (I'm using xl2002 to test.)

 

[Tom Ogilvy]

Did you make C4 or H12 the cell with validation? Sounded like Max is
talking about C4.

--
Regards,
Tom Ogilvy

Even though the Target to Source worked, I wouldn't do it. Someday, you'll
wonder what the heck is going on and why you did it. (Or the guy/gal modifying
your addin will be swearing at you for being non-standard.)

I just tried a simple worksheet change that had data|validation (list: a,b,c)
in H12.

The code was allowed to change that cell and ignore the Data|validation rules.

Both .xla and .xls worked ok. (I'm using xl2002 to test.)

monitoring (in my example $C$4) is a normal cell everything works fine, but
if the cell is handled by a list of possible value (made trough the Data >
Validation menu) then i keep getting the same error.

 

[Dave Peterson]

I actually selected A1:K25 and applied the same data|validation to all of it.

But that could be a problem if he's using xl97.

From Debra Dalgleish's site:
http://www.contextures.com/xlDataVal08.html

In Excel 97, selecting an item from a Data Validation dropdown list
does not trigger a Change event, unless the list items have been typed in
the Data Validation dialog box.

But I don't see that as the solution/explanation for .xls vs. .xla's.

 

[Tom Ogilvy]

or the fact that he says he is getting a 1004 error rather than the change
event not firing.

 

[Guest]

Tom, Dav

thaks a lot for your help

I'm using excel 2002, and the change event is fired when I change the value of the cell handled with a data validation list
So here is the summary
Excel 200

At this point I'm completely lost and I'm trying to create a work-around to solve the problem

Thanks guy your help has been anyway very usefull

Regard
Massimiliano Garr

----- Tom Ogilvy wrote: ----

or the fact that he says he is getting a 1004 error rather than the chang
event not firing

 

[Dave Peterson]

I'm sorry. I don't have a better guess--well, any guess at all.

Ok, one way out guess. When unexplainable things start happening, maybe
cleaning your code would help.

Export each module to a text file and delete the module and reimport them.

Rob Bovey has a workbook that'll do this pretty darn quickly.
You can find it at:
http://www.appspro.com/

(but I have no faith!)