Help with 1 code line

  • Thread starter Thread starter JMay
  • Start date Start date
J

JMay

Below code in Sheet2 allows user to return to previous clicked cell in
Sheet1:
My Sheet1 code (not shown) Highlights ActiveRow (on Sheet2) setting interior
= 3.
4th line of code below is my attempt to remove colorindex when retuning to
sheet1,
however it "ain't-working". Any help appreciated.
TIA,


Private Sub Worksheet_BeforeDoubleClick( _
ByVal Target As Excel.Range, Cancel As Boolean)
If Not Intersect(Target, Range("B:B").EntireColumn) Is Nothing Then
Cancel = True
Application.Goto Reference:=Application.PreviousSelections(1)
ActiveSheet.Range("B4").CurrentRegion.Rows.Interior = xlColorIndexNone
<<BOMB!!
End If
End Sub
 
I've added a few checks and disabled events while the thing is running,
(probably not needed, but you do want to err on the safe side. and you
may have other event handlers on the sheet or book)

Since you run in an object module i've used the Me keyword, with
evaluates to the sheet in which the code is running.

Also I've changed to sequence of color and goto
First reset the color, THEN jump to prevselect


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range,
Cancel As Boolean)

If Not Intersect(Target, Me.Columns(2)) Is Nothing Then
Cancel = True
With Application
.EnableEvents = False
Me.Range("B4").CurrentRegion.Interior.ColorIndex = xlColorIndexNone
'or .Entirerow.Interior.ColorIndex = xlColorIndexNone
If Not .PreviousSelections(1) Is Nothing Then
.Goto Reference:=.PreviousSelections(1)
End If
.EnableEvents = True
End With
End If

End Sub

hth...

keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >
 
Hi JMay,

Without otherwise looking at your event procedure, your problem code line
should read:

ActiveSheet.Range("B4").CurrentRegion.Rows.Interior.Color = xlColorIndexNone
 
Thanks keepitcool,
used your alternative
adding Activecell before .Entirerow.Interior.ColorIndex =
xlColorIndexNone
WORKS GREAT!!

If a user "moves around" using arrow keys (Say 4 or 5 strokes)
I think the PreviousSelection(Count) is lost. hummm

JMay
 
The previousselections collections is only populated by using a name from
the namebox or using GoTo

From Help:

Each time you go to a range or cell by using the Name box or the Go To
command (Edit menu), or each time a macro calls the Goto method, the
previous range is added to this array as element number 1, and the other
items in the array are moved down.


Just selecting a cell with a mouse does not add the range to this
collection.
 
Thanks Tom...
much appreciated.

Tom Ogilvy said:
The previousselections collections is only populated by using a name from
the namebox or using GoTo

From Help:

Each time you go to a range or cell by using the Name box or the Go To
command (Edit menu), or each time a macro calls the Goto method, the
previous range is added to this array as element number 1, and the other
items in the array are moved down.


Just selecting a cell with a mouse does not add the range to this
collection.
Click to expand...
 
Back
Top