Event Procedures in an Add-In

[DJB]

Hi all

I am using the Workbook_SheetSelectionChange event to determine when a range is selected and to show the total, average and number of items selected. This works great in a workbook, but I have tried to set this up in an add-in so it's availale all the time, but it doesn't work because the event is tied to the add-in file and not the currently active workbook.

Does anybody know of a way around this?

Thanks

DJB

[Dave Peterson]

You could use an application event to monitor a selection change in any
workbook.

You could create a new workbook, save it as an addin (.xla) in your xlstart
folder. (Then it'll open each time excel starts.) Or you could merge it into
your personal.xl* workbook (if you have one).

This kind of code goes behind the ThisWorkbook module:

Option Explicit
Public WithEvents xlApp As Excel.Application
Private Sub Workbook_Open()
Set xlApp = Application
End Sub
Private Sub Workbook_Close()
Set xlApp = Nothing
End Sub
Private Sub xlApp_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
MsgBox Target.Cells.Count & " are selected"
End Sub

(I have no idea what your original code did, though.)

You can read a lot more about application events at Chip Pearson's site:
http://www.cpearson.com/excel/AppEvent.htm




DJB wrote:
>
> Hi all
>
> I am using the Workbook_SheetSelectionChange event to determine when a
> range is selected and to show the total, average and number of items
> selected. This works great in a workbook, but I have tried to set this
> up in an add-in so it's availale all the time, but it doesn't work
> because the event is tied to the add-in file and not the currently
> active workbook.
>
> Does anybody know of a way around this?
>
> Thanks
>
> DJB
>
> --
> DJB
> ------------------------------------------------------------------------
> DJB's Profile: http://www.excelforum.com/member.php...o&userid=21221
> View this thread: http://www.excelforum.com/showthread...hreadid=468384

--

Dave Peterson

[Dave Peterson]

I have something similar that I keep in my personal.xla workbook.

But it's not based on the change of selection. I just assign a short cut key to
the macro and run it on demand.

I had some counts of formulas and other stuff that really slowed down when I did
selected all the cells on the sheet.



DJB wrote:
>
> Hi all
>
> I am using the Workbook_SheetSelectionChange event to determine when a
> range is selected and to show the total, average and number of items
> selected. This works great in a workbook, but I have tried to set this
> up in an add-in so it's availale all the time, but it doesn't work
> because the event is tied to the add-in file and not the currently
> active workbook.
>
> Does anybody know of a way around this?
>
> Thanks
>
> DJB
>
> --
> DJB
> ------------------------------------------------------------------------
> DJB's Profile: http://www.excelforum.com/member.php...o&userid=21221
> View this thread: http://www.excelforum.com/showthread...hreadid=468384

--

Dave Peterson

[DJB]

Dave

Ahhhhhh, I think I need the Public With Events... line. I'll have a go with that. Thanks very much for the help.

DJB