Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
When code runs...worksheet acts a little strange
Hi
I'm using the following code below (with help form this group) to copy the unique values in cell A8:A501 of the activeworksheet to cells A:14:A47 of the Previous worksheet. It's working good...but......after the user enters a value (presses the enter key after entering data into a cell) on the Activeworksheet, the worksheet knida flickers to the Previous worksheet and then back to the Activeworksheet. It is still working..it just looks a bit strange. Is there a way to make this not happen? Private Sub Worksheet_Change(ByVal Target As Range) Dim prevSheet As Worksheet With Me If .Index = 1 Then MsgBox "No sheets to the left" Set prevSheet = Worksheets("WC Adjustments") Else Set prevSheet = Worksheets(.Index - 1) End If .Unprotect Password:="test" If Not Application.Intersect(Target, _ Range("A8:A501")) Is Nothing Then prevSheet.Range("A13:A47").ClearContents prevSheet.Unprotect Password:="test" gCopyUnique Range("A8:A501"), prevSheet.Range("A13") **** see below this code is in a standard module End If .Unprotect Password:="test" 'Range("R16:R51").Select prevSheet.Unprotect Password:="test" prevSheet.Range("A13:A47").Sort , _ Key1:=prevSheet.Range("A13"), _ Order1:=xlDescending, _ Header:=xlGuess, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom .Protect Password:="test", DrawingObjects:=True, _ Contents:=True, Scenarios:=True End With prevSheet.Protect Password:="test", DrawingObjects:=True, _ Contents:=True, Scenarios:=True End Sub Public Sub gCopyUnique(rrngSource As Range, rrngDest As Range) ActiveSheet.Unprotect Password:="test" rrngSource.AdvancedFilter Action:=xlFilterCopy, _ CopyToRange:=rrngDest, Unique:=True ActiveSheet.Protect Password:="test", DrawingObjects:=True, Contents:=True, Scenarios:=True As Always...Thanks for your help! Kimberly |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vba code runs...need spaces ........ | Excel Discussion (Misc queries) | |||
Worksheet acts like its protected but it's not | Excel Discussion (Misc queries) | |||
VBA code that only runs when a worksheet is active | Excel Programming | |||
Code runs every other time | Excel Programming | |||
How to keep from going dizzy when my code runs | Excel Programming |