Thank you Dave, but unfortunately all the functionalities of the App im
creating need to be available to ALL and ANY workbook since the
activeworkbook can be any workbook, so i can't write workbook specific code.
Will the fundamentals of this code still function correctly if i adapt it to
work in personal.xls?

"Dave Peterson" wrote:

#1. Nope.

#1 and #2. You could have an application event that looks for a selection
change.

I used a different file than personal.xls...

I put this in a general module:

Option Explicit
Public FormIsRunning As Boolean
Sub startit()
UserForm1.Show vbModeless
End Sub

I put this 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)
If FormIsRunning Then
UserForm1.Label1.Caption = Target.Address(external:=True)
End If
End Sub

I put this behind the userform:

Option Explicit
Private Sub UserForm_Initialize()
FormIsRunning = True
End Sub
Private Sub UserForm_Terminate()
FormIsRunning = False
End Sub


========
If you're going to share this with others, don't use Personal.xls. You may end
up messing up the other user's stuff. (And you share it with a workbook that's
named nicely, too.)



Chirs wrote:

I have a Form that i display in Modeless state to allow the user make
selections and changes to spreadsheets without closing the form. I would
like to have the activecell value of the spreadsheet show up in a textbox on
the form. i have TWO questions: 1) I am using the personal.xls file for
all the forms and macros to make it available to all workbooks, will the
Workbook_SheetSelectionChange of personal.xls fire for the activeworkbook AND
2) Is there a way to indiacte that the modeless form is loaded and showing
to captuer the event only if it is showing at the time?

--

Dave Peterson