Using Excel 2000:

I'm using VBA to sort a spreadsheet in different ways.
All works OK.
I want to remember the active cell before the sort.
Then return to that cell (wherever it's moved to) after the sort.
How do I do it please?

Thanks in advance.
Geoff

before starting the sort use
a = ActiveCell.address
after finishing sort use
range(a).select

- Manges

--
Message posted from http://www.ExcelForum.com

Hi Mangesh,

Thanks for your reply.

I added "debug.print a" before and after the sort.
Range(a).Select returns me to the same cell, but not the same record.

I've learned from your reply that the address property returns a
string (eg $G$11).

So thanks again.
Regards
Geoff

Hi,


before sorted
dim ActCell as Range
set ActCell=ActiveCell

after sorted
ActCell.Activate
set ActCell=nothing


--
Haldun Alay


"Geoff" , iletide þunu yazdý ...
Using Excel 2000:

I'm using VBA to sort a spreadsheet in different ways.
All works OK.
I want to remember the active cell before the sort.
Then return to that cell (wherever it's moved to) after the sort.
How do I do it please?

Thanks in advance.
Geoff

Hi Haldun,

Thanks for your reply.

With your solution, I'm returned to the same cell, but not the same
record.
After the sort, I want to be on the row for the person I was on before
the sort.

Thanks for taking time out to reply.
Regards
Geoff

Hi,

A different solution to your problem.

if you are not using cell comments on your table, this works.




Dim Sht As Worksheet
Set Sht = ActiveCell.Parent
ActiveCell.AddComment
CmtCount = Sht.Comments.Count
ActiveCell.Comment.Text Text:="I was here"

....
....your sorting codes
....


Sht.Comments(CmtCount).Parent.Select
Sht.Comments(CmtCount).Delete




Kind Regards.
--
Haldun Alay

"Geoff" , iletide þunu yazdý ...
Hi Haldun,

Thanks for your reply.

With your solution, I'm returned to the same cell, but not the same
record.
After the sort, I want to be on the row for the person I was on before
the sort.

Thanks for taking time out to reply.
Regards
Geoff

Hi Haldun

Thanks for this tip.

I thought I might tag the cell using a comment, but didn't know how to
do it.

For the current spreadsheet, each row has a unique value in one
column, so I've been able to use that to effect a solution (see reply
to Tom).

It's a pity there's not a bookmark property for a cell (as there might
be for a record in a recordset) to enable fast return to the same
location.

Thanks again.
Regards
Geoff

Hi Geoff,

If you are using Office XP or higher, you can use Watches class to locate your current cell before sort.

An example follows

Sub AddWatchAndSort()
Application.Watches.Delete
Application.Watches.Add Source:=ActiveCell
Call SortMyTable ' Your Sorting codes
Application.Watches.Item(0).Source.Activate
End Sub

Kind regards.
--
Haldun Alay
"Geoff" , iletide þunu yazdý ...
Hi Haldun

Thanks for this tip.

I thought I might tag the cell using a comment, but didn't know how to
do it.

For the current spreadsheet, each row has a unique value in one
column, so I've been able to use that to effect a solution (see reply
to Tom).

It's a pity there's not a bookmark property for a cell (as there might
be for a record in a recordset) to enable fast return to the same
location.

Thanks again.
Regards
Geoff

Use a variable to store the ActiveCell contents, before the sort, then Find
it after the sort.

Sub Sort_Find()
Dim actvcell

Range("A16").Select 'change to your cell
actvcell = ActiveCell.Value 'store the cell value
' put your sort routine next
Selection.Sort Key1:=Range("A2"), Order1:=xlDescending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
'Find the original cell value
Cells.Find(What:=actvcell, After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase _
:=False).Activate
End Sub

Mike F
"Geoff" wrote in message
...
Using Excel 2000:

I'm using VBA to sort a spreadsheet in different ways.
All works OK.
I want to remember the active cell before the sort.
Then return to that cell (wherever it's moved to) after the sort.
How do I do it please?

Thanks in advance.
Geoff

My routine assumed you wanted to locate the original cell contents after the
sort has moved it. Mangesh & Haldun's routine will put you back to the
original cell location before the sort, no matter what the contents of that
cell is now. Choose whichever you needed.
Mike F
"Mike Fogleman" wrote in message
news:vHD0d.73216$3l3.18664@attbi_s03...
Use a variable to store the ActiveCell contents, before the sort, then
Find
it after the sort.

Sub Sort_Find()
Dim actvcell

Range("A16").Select 'change to your cell
actvcell = ActiveCell.Value 'store the cell value
' put your sort routine next
Selection.Sort Key1:=Range("A2"), Order1:=xlDescending,
Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
'Find the original cell value
Cells.Find(What:=actvcell, After:=ActiveCell, LookIn:=xlValues, LookAt
_
:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase _
:=False).Activate
End Sub

Mike F
"Geoff" wrote in message
...
Using Excel 2000:

I'm using VBA to sort a spreadsheet in different ways.
All works OK.
I want to remember the active cell before the sort.
Then return to that cell (wherever it's moved to) after the sort.
How do I do it please?

Thanks in advance.
Geoff

Hi Mike,

Thanks for your reply.

I have several command buttons on a customised toolbar to execute the
different sorts.

Some of the columns I'm sorting don't contain unique values. But I've
adapted your method and Tom's method and am almost there! (See reply
to Tom's post for outstanding issue.)

I've learned the Cells.Find method from your reply, so thanks for
that.

Regards.
Geoff

The best approach is not to affect the activecell at all

Sub AASort()
With Worksheets("sheet1")
..Range("B9:Z30").Sort Key1:=.Range("C9"), _
Order1:=xlAscending, Header:=xlNo
End With
End Sub

won't affect the activecell event if it is in the range to be sorted or even
if it is on another worksheet in the workbook.

--
Regards,
Tom Ogilvy

"Geoff" wrote in message
...
Using Excel 2000:

I'm using VBA to sort a spreadsheet in different ways.
All works OK.
I want to remember the active cell before the sort.
Then return to that cell (wherever it's moved to) after the sort.
How do I do it please?

Thanks in advance.
Geoff

Disregard, I didn't see the "wherever it's moved to" in your question.

An adjustment for that might be:

Sub AASort()
vVal = ActiveCell.Value
With Worksheets("sheet1")
On Error Resume Next
set rng = Intersect(activeCell.EntireColumn, _
.Range("B9:Z30"))
On Error goto 0
.Range("B9:Z30").Sort Key1:=.Range("C9"), _
Order1:=xlAscending, Header:=xlNo
End With
if not rng is nothing then
rng.Find(vVal).Activate
End if
End Sub

This would assume that the value of the activecell is unique in that column.

--
Regards,
Tom Ogilvy


--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
The best approach is not to affect the activecell at all

Sub AASort()
With Worksheets("sheet1")
.Range("B9:Z30").Sort Key1:=.Range("C9"), _
Order1:=xlAscending, Header:=xlNo
End With
End Sub

won't affect the activecell event if it is in the range to be sorted or
even
if it is on another worksheet in the workbook.

--
Regards,
Tom Ogilvy

"Geoff" wrote in message
...
Using Excel 2000:

I'm using VBA to sort a spreadsheet in different ways.
All works OK.
I want to remember the active cell before the sort.
Then return to that cell (wherever it's moved to) after the sort.
How do I do it please?

Thanks in advance.
Geoff

With declarations:

Sub AASort()
Dim rng As Range, vVal As Variant
vVal = ActiveCell.Value
With Worksheets("sheet1")
On Error Resume Next
Set rng = Intersect(ActiveCell.EntireColumn, _
.Range("B9:Z30"))
On Error GoTo 0
.Range("B9:Z30").Sort Key1:=.Range("C9"), _
Order1:=xlAscending, Header:=xlNo
End With
If Not rng Is Nothing Then
rng.Find(vVal).Activate
End If
End Sub

--
Regards,
Tom Ogilvy

Hi Tom,

Thanks for your replies.
I'm almost home and dry.

I've used the Intersect method on a column that contains unique
values. I can unambiguously find the new row after the sort. I can
create a range object for the new location of the cell that was active
before the sort.

One problem remains. Do you (or anyone else) have any ideas please?
The show method of the new location cell's range object doesn't scroll
me to the cell, ie this code line doesn't work as I expect :

rngNewCell.Show

Thanks for your help.
Geoff

I don't suppose you'll need it, but this is how I ended up doing it:

' Ensure CaseNumber range name exists:
RetVal = RangeExists(mconCaseNumber)
If Not RetVal Then
RangeNameMissing_CannotSort mconCaseNumber
GoTo Bye
End If

' Ensure UsedArea range exists:
RetVal = RangeExists(mconUsedArea)
If Not RetVal Then
RangeNameMissing_CannotSort mconUsedArea
GoTo Bye
End If

' Turn off screen:
AllowScreenUpdates False

' Get active cell's range, column and row numbers:
Set rngOldCell = ActiveCell
lngOldRow = rngOldCell.Row
lngOldCol = rngOldCell.Column

' Get Case Number column as range:
Set rngCaseNoCol = Intersect(Range(mconCaseNumber).EntireColumn,
Range(mconUsedArea))

' Test for intersection:
If rngCaseNoCol Is Nothing Then GoTo InvalidCaseNumberIntersection

' Get Case Number column number:
lngCaseNoCol = rngCaseNoCol.Column

' Get Case Number for active row:
strCaseNo = Cells(lngOldRow, lngCaseNoCol).Value

' Execute sort on Case Number column:
Range(mconUsedArea).Sort Key1:=Range(mconCaseNumber),
Order1:=xlAscending, _
Header:=xlYes, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

' Find old case number in Case Number column now it's sorted:
Set rngNewRow = rngCaseNoCol.Find(strCaseNo)

' Shouldn't happen, but test that case number is found:
If rngNewRow Is Nothing Then GoTo CaseNumberNotFound

' Get new row number of cell that was active at start:
lngNewRow = rngNewRow.Row

' Get new range of cell that was active at start:
Set rngNewCell = Cells(lngNewRow, lngOldCol)

' Move to new cell location:
rngNewCell.Activate
rngNewCell.Show ' PROBLEM WITH THIS LINE !

' Put sort order into footer:
ChangeFooter "Sorted by Case Number"

GoSub CleanUp

' Message user:
strMessage = "Now ordered by CASE NUMBER."
intOptions = vbInformation + vbOKOnly
strHeading = "Sort Complete"
MsgBox strMessage, intOptions, strHeading

Bye:

Exit Sub

Geoff,

rngNewCell.Show

There is no Show method for a range object, so you should get an
"object doesn't support this method" error message. Instead, you
can use Application.Goto. E.g.,

Application.Goto rngNewCell, True


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Geoff" wrote in message
...
Hi Tom,

Thanks for your replies.
I'm almost home and dry.

I've used the Intersect method on a column that contains unique
values. I can unambiguously find the new row after the sort.
I can
create a range object for the new location of the cell that was
active
before the sort.

One problem remains. Do you (or anyone else) have any ideas
please?
The show method of the new location cell's range object doesn't
scroll
me to the cell, ie this code line doesn't work as I expect :

rngNewCell.Show

Thanks for your help.
Geoff

I don't suppose you'll need it, but this is how I ended up
doing it:

' Ensure CaseNumber range name exists:
RetVal = RangeExists(mconCaseNumber)
If Not RetVal Then
RangeNameMissing_CannotSort mconCaseNumber
GoTo Bye
End If

' Ensure UsedArea range exists:
RetVal = RangeExists(mconUsedArea)
If Not RetVal Then
RangeNameMissing_CannotSort mconUsedArea
GoTo Bye
End If

' Turn off screen:
AllowScreenUpdates False

' Get active cell's range, column and row numbers:
Set rngOldCell = ActiveCell
lngOldRow = rngOldCell.Row
lngOldCol = rngOldCell.Column

' Get Case Number column as range:
Set rngCaseNoCol =
Intersect(Range(mconCaseNumber).EntireColumn,
Range(mconUsedArea))

' Test for intersection:
If rngCaseNoCol Is Nothing Then GoTo
InvalidCaseNumberIntersection

' Get Case Number column number:
lngCaseNoCol = rngCaseNoCol.Column

' Get Case Number for active row:
strCaseNo = Cells(lngOldRow, lngCaseNoCol).Value

' Execute sort on Case Number column:
Range(mconUsedArea).Sort Key1:=Range(mconCaseNumber),
Order1:=xlAscending, _
Header:=xlYes, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom

' Find old case number in Case Number column now it's
sorted:
Set rngNewRow = rngCaseNoCol.Find(strCaseNo)

' Shouldn't happen, but test that case number is found:
If rngNewRow Is Nothing Then GoTo CaseNumberNotFound

' Get new row number of cell that was active at start:
lngNewRow = rngNewRow.Row

' Get new range of cell that was active at start:
Set rngNewCell = Cells(lngNewRow, lngOldCol)

' Move to new cell location:
rngNewCell.Activate
rngNewCell.Show ' PROBLEM WITH THIS LINE !

' Put sort order into footer:
ChangeFooter "Sorted by Case Number"

GoSub CleanUp

' Message user:
strMessage = "Now ordered by CASE NUMBER."
intOptions = vbInformation + vbOKOnly
strHeading = "Sort Complete"
MsgBox strMessage, intOptions, strHeading

Bye:

Exit Sub

It's 1am here now, when I downloaded all these helpful replies.
Thanks fellas.
I'll give them a work through tomorrow and let you know how I get on.

Geoff

PS - You've hit the nail on the head. It was the "wherever it's moved
too" that was fogging me.

Hi Tom,

Well, I got to the bottom of it eventually.

As you know, I was wrong to say that the Activate, Select and Show
methods don't scroll the screen - as your test demonstrated. My
guess, after getting your post, proved right - that something else was
inhibiting scrolling.

There was a slight difference in our coding.

Your code began just after my code turned off screen updating with:
AllowScreenUpdates False
which called:
Private Sub AllowScreenUpdates(blnYes As Boolean)
Application.ScreenUpdating = blnYes
End Sub

It appears that, if:
Application.ScreenUpdating = False
when the Activate, Select, or Show methods are called, then the active
cell doesn't end up in the middle of the screen (but somewhere off
screen). However, the GoTo method does scroll the screen while screen
updating is off.

Having the active cell in the middle of the screen (after Activate,
Select, or Show) is better for the user. But the penalty is the
screen flashes a little more while the screen scrolls.

Having the active cell at the top (after GoTo) is not so good for the
user. But there's less flash.

I decided to use Activate.

Thanks for your help, Tom. It's been the best I've received from
anyone here or any other group. You should be an MVP (if you're not
already).

Geoff

PS - I replicated your test spreadsheet. Neat!

thanks for the update. Glad you find some of the information useful (if
sometimes hard to read).

--
Regards,
Tom Ogilvy

"Geoff" wrote in message
...
Hi Tom,

Well, I got to the bottom of it eventually.

As you know, I was wrong to say that the Activate, Select and Show
methods don't scroll the screen - as your test demonstrated. My
guess, after getting your post, proved right - that something else was
inhibiting scrolling.

There was a slight difference in our coding.

Your code began just after my code turned off screen updating with:
AllowScreenUpdates False
which called:
Private Sub AllowScreenUpdates(blnYes As Boolean)
Application.ScreenUpdating = blnYes
End Sub

It appears that, if:
Application.ScreenUpdating = False
when the Activate, Select, or Show methods are called, then the active
cell doesn't end up in the middle of the screen (but somewhere off
screen). However, the GoTo method does scroll the screen while screen
updating is off.

Having the active cell in the middle of the screen (after Activate,
Select, or Show) is better for the user. But the penalty is the
screen flashes a little more while the screen scrolls.

Having the active cell at the top (after GoTo) is not so good for the
user. But there's less flash.

I decided to use Activate.

Thanks for your help, Tom. It's been the best I've received from
anyone here or any other group. You should be an MVP (if you're not
already).

Geoff

PS - I replicated your test spreadsheet. Neat!