UserForm updated

[Dan Tabla]

Can anyone please help me with following code?

I cant make this code work on all opened Excel Workbooks.
I only succeded on the workbook that contains the UserForm1, which contains
text box txtLayout.

I put this code under "ThisWorkbook" and it runs nicely but only localy on
the sheets included on this workbook.


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)

UserForm1.txtLayout = ActiveCell.Text

End Sub

 

[Dave Peterson]

First, the sheet events are limited to events that happen on that individual
sheet.

Workbook events are limited to events that happen on that individual workbook.

So what you'll need is an application event--things that happen any place in the
application.

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

Chip uses a different technique (a dedicated class module) and the following
uses the ThisWorkbook class module.

This seemed to work for me (in simple testing). I created a simple
userform--just a textbox. I can close it via the X at the top right corner of
the userform.

First, create a new general module in your workbook with the userform.

Put this code in it:

Option Explicit
Public UF1IsLoaded As Boolean
Public UF1 As UserForm1
Sub ShowTheForm()
If UF1IsLoaded Then
'do nothing
Else
Set UF1 = UserForm1
UF1.Show vbModeless
UF1IsLoaded = True
End If
End Sub

Then this is used in the Userform module:

Option Explicit
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
UF1IsLoaded = False
Unload Me
End Sub

And finally, this is used in the ThisWorkbook module:

Option Explicit
Public WithEvents xlApp As Excel.Application
Private Sub Workbook_Open()
Set xlApp = Application
Call ShowTheForm
End Sub
Private Sub Workbook_Close()
Set xlApp = Nothing
If UF1IsLoaded Then
Unload UF1
UF1IsLoaded = False
End If
End Sub
Private Sub xlApp_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
If UF1IsLoaded Then
UF1.TextBox1.Value = Target.Cells(1).Text
End If
End Sub

Save this workbook (a test workbook????).

Then run the Workbook_Open event (manually or by closing and reopening the
workbook).

And test it to see if it works.

 

[Dan Tabla]

Thank you so much Dave!!! The code works great ...It will really save me a
lot of efforts!!! God bless you and compile successfully!