Return to cell after sort
 

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

Return to cell after sort
 

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

- Manges

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

Return to cell after sort
 

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

Return to cell after sort
 

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

Return to cell after sort
 

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

Return to cell after sort
 

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

Return to cell after sort
 

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

Return to cell after sort
 

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

Return to cell after sort
 

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.

Return to cell after sort
 

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

Return to cell after sort
 

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

Return to cell after sort
 

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

Return to cell after sort
 

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

Return to cell after sort
 

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

Return to cell after sort
 

also, I would expect

rngNewCell.Activate

by itself
to achieve what you want.

--
Regards,
Tom Ogilvy

"Chip Pearson" wrote in message
...
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

Return to cell after sort
 

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

Return to cell after sort
 

Hi Chip/Tom

Thanks for your replies.

The GoTo method scrolls me to the new location, so problem solved -
thanks for that, Chip. The Activate method doesn't scroll to the new
location, Tom - it just activates the cell but leaves it out of view.

My code compiled OK. I thought it would because the Access 2000 VB
Help system says:

"Show Method:
"Range object (Syntax 1): Scrolls through the contents of the active
window to move the range into view. The range must consist of a single
cell in the active document.
"Syntax 1
"expression.Show
"expression Required. For Syntax 1, an expression that returns an
object in the Applies To list."
(The 'Applies To List' includes the Range object.)

The above Help extract refers to a *cell in the active document*.

Could this refer to a Word document table cell, rather than an Excel
spreadsheet?

Something odd's going on - with code compiling but not working.

Thanks to you both.
Regards
Geoff.

Return to cell after sort
 

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

Return to cell after sort
 

Sorry Geoff, I ran your code with just the
rngNewCell.Activate

(no rngNewCell.Show)
and it worked fine (as expected). It scrolled the window so the activated
cell was visible.

I ran it without the activate line; just the rngNewCell.show
and it did nothing. The original activated cell in its old location
remained selected.

Range does have a show method and as documented, I would expect it to do
what you intended.

Nonetheless, I am not sure how you are making activate alone to fail, but
for normal circumstances it works just fine.

--
Regards,
Tom Ogilvy




"Geoff" wrote in message
...
Hi Chip/Tom

Thanks for your replies.

The GoTo method scrolls me to the new location, so problem solved -
thanks for that, Chip. The Activate method doesn't scroll to the new
location, Tom - it just activates the cell but leaves it out of view.

My code compiled OK. I thought it would because the Access 2000 VB
Help system says:

"Show Method:
"Range object (Syntax 1): Scrolls through the contents of the active
window to move the range into view. The range must consist of a single
cell in the active document.
"Syntax 1
"expression.Show
"expression Required. For Syntax 1, an expression that returns an
object in the Applies To list."
(The 'Applies To List' includes the Range object.)

The above Help extract refers to a *cell in the active document*.

Could this refer to a Word document table cell, rather than an Excel
spreadsheet?

Something odd's going on - with code compiling but not working.

Thanks to you both.
Regards
Geoff.

Return to cell after sort
 

Thanks, Tom.
That's very helpful to know.

Strange. I'll check tomorrow (it's nearly 2am here now). I don't
think I was doing anything daft - indeed, if you ran my code and it
worked, that would seem to prove it. On my machine, Activate, Select
and Show all didn't scroll to the cell, whereas GoTo did. Perhaps
something else is inhibiting scrolling, but I can't think what. I'll
let you know how I get on. Thanks for confirming what I thought was
the case about the range object's show method.

Regards.
Geoff

Return to cell after sort
 

Here is the code I used:

I had random numbers in A1:J100
Column F had unique numbers
The active Cell was in column A (A10)
the last visible row was 28


After the sort, the value in A10 was in A63. A63 was visible and selected.
(in the middle of the visible vertical rows rather at the top of the screen
as in Application.Goto)


Sub Tester1()
mconCaseNumber = "F1"
mconUsedArea = "A1:J100"

' 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:
Bye:

Exit Sub


End Sub

--
Regards,
Tom Ogilvy

"Geoff" wrote in message
...
Thanks, Tom.
That's very helpful to know.

Strange. I'll check tomorrow (it's nearly 2am here now). I don't
think I was doing anything daft - indeed, if you ran my code and it
worked, that would seem to prove it. On my machine, Activate, Select
and Show all didn't scroll to the cell, whereas GoTo did. Perhaps
something else is inhibiting scrolling, but I can't think what. I'll
let you know how I get on. Thanks for confirming what I thought was
the case about the range object's show method.

Regards.
Geoff

Return to cell after sort
 

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

Return to cell after sort
 

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!

Return to cell after sort
 

Hi Haldun,

Nice to know that Microsoft have already responded to what must have
been a general need on this issue.
I have Office XP Developer, but not yet upgraded!

Thanks for your interest.
Geoff

Return to cell after sort
 

I'm posting this again as it didn't download first time :-

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!

Tom Ogilvy
 

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!

Tom Ogilvy
 

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!