|
Restricting sheet_selectionchange code to just one column
The below event code shows content of a cell which has just lost focus into a
textbox placed in a worksheet. This code works for all cells in the worksheet but I want the code to restrict to a single column. Another question is regarding the performance of code execution. How can I speed it up? Your help is urgently need. TIA 'A keen beginner in VBA' ----------------------------------------------------------- Option Explicit Dim rngLast As Range Private Sub Worksheet_SelectionChange(ByVal Target As Range) Application.ScreenUpdating = False On Error Resume Next If Not rngLast Is Nothing Then Me.Shapes("TextBox").TextFrame.Characters.Text = rngLast(1, 6).Value End If Set rngLast = Target Shapes("TextBox").SetShapesDefaultProperties On Error GoTo 0 Application.ScreenUpdating = True End Sub ----------------------------------------------------------- |
Restricting sheet_selectionchange code to just one column
If you want to restrict this to Column B add a condition..
If target.Column = 2 Then 'Place your code here End If If this post helps click Yes --------------- Jacob Skaria "shabutt" wrote: The below event code shows content of a cell which has just lost focus into a textbox placed in a worksheet. This code works for all cells in the worksheet but I want the code to restrict to a single column. Another question is regarding the performance of code execution. How can I speed it up? Your help is urgently need. TIA 'A keen beginner in VBA' ----------------------------------------------------------- Option Explicit Dim rngLast As Range Private Sub Worksheet_SelectionChange(ByVal Target As Range) Application.ScreenUpdating = False On Error Resume Next If Not rngLast Is Nothing Then Me.Shapes("TextBox").TextFrame.Characters.Text = rngLast(1, 6).Value End If Set rngLast = Target Shapes("TextBox").SetShapesDefaultProperties On Error GoTo 0 Application.ScreenUpdating = True End Sub ----------------------------------------------------------- |
Restricting sheet_selectionchange code to just one column
Do you really need to use Application.ScreenUpdating = False in this Change
event If this post helps click Yes --------------- Jacob Skaria "shabutt" wrote: The below event code shows content of a cell which has just lost focus into a textbox placed in a worksheet. This code works for all cells in the worksheet but I want the code to restrict to a single column. Another question is regarding the performance of code execution. How can I speed it up? Your help is urgently need. TIA 'A keen beginner in VBA' ----------------------------------------------------------- Option Explicit Dim rngLast As Range Private Sub Worksheet_SelectionChange(ByVal Target As Range) Application.ScreenUpdating = False On Error Resume Next If Not rngLast Is Nothing Then Me.Shapes("TextBox").TextFrame.Characters.Text = rngLast(1, 6).Value End If Set rngLast = Target Shapes("TextBox").SetShapesDefaultProperties On Error GoTo 0 Application.ScreenUpdating = True End Sub ----------------------------------------------------------- |
Restricting sheet_selectionchange code to just one column
Thank you Jacob Skaria for your help. Here is the modified code as suggested
by you but for two columns because I needed two columns. ------------------------------------- Option Explicit Dim rngLast As Range 'last used cell which has lost focus Private Sub Worksheet_SelectionChange(ByVal Target As Range) Application.EnableEvents = False Application.ScreenUpdating = False If Target.Column 9 And Target.Column < 12 Then On Error Resume Next If Not rngLast Is Nothing Then 'check for existence Me.Shapes("TextBox").TextFrame.Characters.Text = rngLast(1, 6).Value End If End If Set rngLast = Target Shapes("TextBox").SetShapesDefaultProperties On Error GoTo 0 Application.ScreenUpdating = True Application.EnableEvents = True End Sub ------------------------------------- I don't understand your this comment "Do you really need to use Application.ScreenUpdating = False in this Change event". Please explain a little bit. TIA "Jacob Skaria" wrote: Do you really need to use Application.ScreenUpdating = False in this Change event If this post helps click Yes --------------- Jacob Skaria "shabutt" wrote: The below event code shows content of a cell which has just lost focus into a textbox placed in a worksheet. This code works for all cells in the worksheet but I want the code to restrict to a single column. Another question is regarding the performance of code execution. How can I speed it up? Your help is urgently need. TIA 'A keen beginner in VBA' ----------------------------------------------------------- Option Explicit Dim rngLast As Range Private Sub Worksheet_SelectionChange(ByVal Target As Range) Application.ScreenUpdating = False On Error Resume Next If Not rngLast Is Nothing Then Me.Shapes("TextBox").TextFrame.Characters.Text = rngLast(1, 6).Value End If Set rngLast = Target Shapes("TextBox").SetShapesDefaultProperties On Error GoTo 0 Application.ScreenUpdating = True End Sub ----------------------------------------------------------- |
Restricting sheet_selectionchange code to just one column
Sorry if I didnt put that correctly. Check the performance of code execution
....after removing Application.EnableEvents = False Application.ScreenUpdating = False Can you elaborate your comment "but for two columns because I needed two columns" If this post helps click Yes --------------- Jacob Skaria "shabutt" wrote: Thank you Jacob Skaria for your help. Here is the modified code as suggested by you but for two columns because I needed two columns. ------------------------------------- Option Explicit Dim rngLast As Range 'last used cell which has lost focus Private Sub Worksheet_SelectionChange(ByVal Target As Range) Application.EnableEvents = False Application.ScreenUpdating = False If Target.Column 9 And Target.Column < 12 Then On Error Resume Next If Not rngLast Is Nothing Then 'check for existence Me.Shapes("TextBox").TextFrame.Characters.Text = rngLast(1, 6).Value End If End If Set rngLast = Target Shapes("TextBox").SetShapesDefaultProperties On Error GoTo 0 Application.ScreenUpdating = True Application.EnableEvents = True End Sub ------------------------------------- I don't understand your this comment "Do you really need to use Application.ScreenUpdating = False in this Change event". Please explain a little bit. TIA "Jacob Skaria" wrote: Do you really need to use Application.ScreenUpdating = False in this Change event If this post helps click Yes --------------- Jacob Skaria "shabutt" wrote: The below event code shows content of a cell which has just lost focus into a textbox placed in a worksheet. This code works for all cells in the worksheet but I want the code to restrict to a single column. Another question is regarding the performance of code execution. How can I speed it up? Your help is urgently need. TIA 'A keen beginner in VBA' ----------------------------------------------------------- Option Explicit Dim rngLast As Range Private Sub Worksheet_SelectionChange(ByVal Target As Range) Application.ScreenUpdating = False On Error Resume Next If Not rngLast Is Nothing Then Me.Shapes("TextBox").TextFrame.Characters.Text = rngLast(1, 6).Value End If Set rngLast = Target Shapes("TextBox").SetShapesDefaultProperties On Error GoTo 0 Application.ScreenUpdating = True End Sub ----------------------------------------------------------- |
Restricting sheet_selectionchange code to just one column
Thanks Jacob Skaria. After deleting.....
Application.EnableEvents = False Application.ScreenUpdating = False Application.EnableEvents = True Application.ScreenUpdating = True from my code, scrolling & data entry has become really fast. But I don't understand why earlier without the "Application.ScreenUpdating = False" in my code, the flicker wouldn't go and now there is no flicker. I needed two columns so that rngLast (1, 6) focus remains the same whether I am in column 10 or 11 because after entering data in cells, I sometimes use right arrow key. And since I have this code also.... If Target.Column = 10 Then Selection.AutoFilter field:=10, Criteria1:="=" Thanks again for your generous help. "Jacob Skaria" wrote: Sorry if I didnt put that correctly. Check the performance of code execution ...after removing Application.EnableEvents = False Application.ScreenUpdating = False Can you elaborate your comment "but for two columns because I needed two columns" If this post helps click Yes --------------- Jacob Skaria "shabutt" wrote: Thank you Jacob Skaria for your help. Here is the modified code as suggested by you but for two columns because I needed two columns. ------------------------------------- Option Explicit Dim rngLast As Range 'last used cell which has lost focus Private Sub Worksheet_SelectionChange(ByVal Target As Range) Application.EnableEvents = False Application.ScreenUpdating = False If Target.Column 9 And Target.Column < 12 Then On Error Resume Next If Not rngLast Is Nothing Then 'check for existence Me.Shapes("TextBox").TextFrame.Characters.Text = rngLast(1, 6).Value End If End If Set rngLast = Target Shapes("TextBox").SetShapesDefaultProperties On Error GoTo 0 Application.ScreenUpdating = True Application.EnableEvents = True End Sub ------------------------------------- I don't understand your this comment "Do you really need to use Application.ScreenUpdating = False in this Change event". Please explain a little bit. TIA "Jacob Skaria" wrote: Do you really need to use Application.ScreenUpdating = False in this Change event If this post helps click Yes --------------- Jacob Skaria "shabutt" wrote: The below event code shows content of a cell which has just lost focus into a textbox placed in a worksheet. This code works for all cells in the worksheet but I want the code to restrict to a single column. Another question is regarding the performance of code execution. How can I speed it up? Your help is urgently need. TIA 'A keen beginner in VBA' ----------------------------------------------------------- Option Explicit Dim rngLast As Range Private Sub Worksheet_SelectionChange(ByVal Target As Range) Application.ScreenUpdating = False On Error Resume Next If Not rngLast Is Nothing Then Me.Shapes("TextBox").TextFrame.Characters.Text = rngLast(1, 6).Value End If Set rngLast = Target Shapes("TextBox").SetShapesDefaultProperties On Error GoTo 0 Application.ScreenUpdating = True End Sub ----------------------------------------------------------- |
| All times are GMT +1. The time now is 01:13 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com