Restricting sheet_selectionchange code to just one column

  • Thread starter Thread starter shabutt
  • Start date Start date
S

shabutt

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
-----------------------------------------------------------
 
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
 
Do you really need to use Application.ScreenUpdating = False in this Change
event

If this post helps click Yes
 
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
 
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
 
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.
 
Back
Top