| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ----------------------------------------------------------- |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 ----------------------------------------------------------- |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 ----------------------------------------------------------- |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 ----------------------------------------------------------- |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 ----------------------------------------------------------- |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 ----------------------------------------------------------- |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Restricting Scroll Prevents Row and Column Choice | Excel Discussion (Misc queries) | |||
| Restricting copied text to one Column | Excel Programming | |||
| Restricting Rows | Excel Discussion (Misc queries) | |||
| Restricting Macros | Excel Programming | |||
| Restricting Access to VB Code (xl98) | Excel Programming | |||
Linear Mode
