How to make values of spreadsheet show in a modeless forms text bo

[Guest]

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]

#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.)

 

[Guest]

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]

Why not put the userform and code in its own workbook. Then save it as an
addin.

The user can put that file in the XLStart folder or load it via tools|Addins.

And you don't have to worry about destroying someone else's work.

==
I don't see a reason why it wouldn't work in personal.xls, though. (I surely
wouldn't use it, though. But I've said that.)