SelectionChange userform
 

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.

SelectionChange userform
 

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


"Nicodemus" wrote in message
...
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.

SelectionChange userform
 

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" wrote:

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


"Nicodemus" wrote in message
...
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.

SelectionChange userform
 

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

"Nicodemus" wrote in message
...
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" wrote:

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


"Nicodemus" wrote in message
...
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.

SelectionChange userform
 

simply marvelous. It works perfectly now !

thank you very much for your help and your time
Nicodemus

"RB Smissaert" wrote:

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

"Nicodemus" wrote in message
...
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" wrote:

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


"Nicodemus" wrote in message
...
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.

SelectionChange userform
 

No trouble; good to hear it works as needed.

RBS

"Nicodemus" wrote in message
...
simply marvelous. It works perfectly now !

thank you very much for your help and your time
Nicodemus

"RB Smissaert" wrote:

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

"Nicodemus" wrote in message
...
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" wrote:

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


"Nicodemus" wrote in message
...
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.