Per and Tom - thank you very much for your answers.

I want my userform with the click event work for any active workbook when
the macro is run, not just for a specific workbook.
Should I use Application_SheetSelectionChange then?

thanks in advance

"Tom Hutchins" wrote:

The Worksheet_SelectionChange event code has to be in the code module for
each worksheet where you want this to work. To open the code module for a
worksheet, right-click on the tab for that sheet, then select View Code. If
you want it to work for all sheets in the workbook, use a
Workbook_SheetSelectionChange event instead; this would go in the
ThisWorkbook module.

You also need to make your userform nonmodal for this to work. In the
Properties for your userform, set ShowModal to False.

Hope this helps,

Hutch

"Lena" wrote:

Hello. I have a user form with a text box that shows currently selected cell.
If a user clicks on another cell I want the text box to be updated with that
cell address. I tried using Private Sub Worksheet_SelectionChange(ByVal
Target As Range). But I don't know where to put it because nothing happens
when the cell is clicked. Do I need to create a class? Here's how my subs
look:

Option Explicit
Dim clAdr As String
Private Sub UserForm_Initialize()
clAdr = ActiveCell.Address(rowabsolute:=False, columnabsolute:=False)
UserForm1.TextBox1.Text = clAdr
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
If Target.Address < UserForm1.TextBox1.Text Then
UserForm1.TextBox1.Text = Target.Address
End If
Application.EnableEvents = True
End Sub