Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
userform and events
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
userform and events
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 "Lena" skrev i meddelelsen ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
userform and events
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
userform and events
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
userform and events
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" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
userform and events
It works now! Thanks! "Tom Hutchins" wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Events in a Userform | Excel Programming | |||
UserForm ListBox Events | Excel Programming | |||
Multipage Userform Events | Excel Programming | |||
UserForm events | Excel Programming | |||
UserForm Events | Excel Programming |