SelectionChange userform

[Guest]

Hi all,

I've created a XLA that opens a form when I click a button in my toolbar.
This form displays a textbox containing the address of the activecell of the
activesheet.

I wonder how to get this textbox updated when I select another cell/range on
any other worksheets.

I guess I could use the Worksheet_SelectionChange event, but it will be
linked to the sheet inside my XLA, and thus won't be triggered if I select a
cell in an independant worksheet.

Any ideas ?

Thanks for any support,
Nicodemus

PS: this post might be triple as I'm not sure if the first one was published.
My apologize if it's the case.

 

[RB Smissaert]

Put this code in the ThisWorkbook module of the add-in and you will see.

Option Explicit
Private WithEvents xlApp As Excel.Application

Private Sub Workbook_Open()
Set xlApp = Excel.Application
End Sub

Private Sub xlApp_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
MsgBox Target.Address
End Sub


RBS

 

[Guest]

Hello RB,

it works fine indeed.
The problem is it keeps on working !
Even when I close my form. Is there a way to cancel or disable this function
when I quit the add-in ?

Thanks for your help
Nicodemus

 

[RB Smissaert]

Yes, you could put the code in the userform:

Option Explicit
Private WithEvents xlApp As Excel.Application

Private Sub UserForm_Initialize()
Set xlApp = Excel.Application
End Sub

Private Sub xlApp_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
MsgBox Target.Address
End Sub

Will have to be a modeless userform, otherwise you can't move in the sheet
while the form is loaded. Once the userform is unloaded it won't work
anymore.
The other option is to keep in the ThisWorkbook module, but make a toolbar
button that can turn this on and off via a public variable or by other
means.

RBS

 

[Guest]

simply marvelous. It works perfectly now !

thank you very much for your help and your time
Nicodemus

 

[RB Smissaert]

No trouble; good to hear it works as needed.

RBS