userform and events

[Lena]

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

 

[Per Jessen]

Hi

Worksheet_SelectionChange is an event code so it has to be in the code sheet
for desired sheet.

To make it work you also have change the ShowModal property of the userform
to False.

Hopes this helps
....
Per

 

[Tom Hutchins]

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]

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]

Sure. You'll have to create a class module to do that. For example, I created
a new class module, then named it EventClassModule. It contains this code:

Public WithEvents App As Application

Private Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal Target As
Range)
If Target.Address <> UserForm1.TextBox1.Text Then
UserForm1.TextBox1.Text = Target.Address
End If
End Sub

Then, I inserted a regular VBA module and put this code in it:

Dim X As New EventClassModule

Sub ShowForm()
Set X.App = Application
UserForm1.Show
End Sub

All this is in the same workbook that contains the userform.

Hutch

 

[Lena]

It works now! Thanks!

Sure. You'll have to create a class module to do that. For example, I created
a new class module, then named it EventClassModule. It contains this code:

Public WithEvents App As Application

Private Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal Target As
Range)
If Target.Address <> UserForm1.TextBox1.Text Then
UserForm1.TextBox1.Text = Target.Address
End If
End Sub

Then, I inserted a regular VBA module and put this code in it:

Dim X As New EventClassModule

Sub ShowForm()
Set X.App = Application
UserForm1.Show
End Sub

All this is in the same workbook that contains the userform.

Hutch