Capture Screen Position

[Paige]

Have code (see below) where if user enters something in Cell O68, it puts the
same value in D21 and forces the cursor to remain at Cell O68 after cell
entry (in case they want to enter something else). The problem is that only
specific cells in the sheet are unlocked, so when the user enters a value in
O68, Excel goes to the next unlocked cell (which is A3) then back to O68 (the
target). This causes the screen position to move slightly; i.e., if the top
row before entry is 65, after the code runs, it always moves the screen so
the top row is 47. I need the screen to stay in the same spot. Have tried
all manner of screen updating = false and also changing the code so that
after entry the cursor doesn't move, but it still affects the screen
position. It's not that the screen is moving back and forth (as when screen
updating is true), but only that it is adjusting the scroll position
so-to-speak.

Can someone advise me what I'm doing wrong, or if there is a way to capture
the specific position of the screen before entry, so I can then return the
user to that spot after entry?

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("O68")) Is Nothing Then
Range("D21").Value = Range("O68").Value
Target.Select
End If
End Sub

 

[NickHK]

Paige,
Because you are changing a value, it is causing the Worksheet_Change event
to fire gain. Disable events for this change, then reset.

If Not Application.Intersect(Target, Range("O68")) Is Nothing Then
Application.EnableEvents=false
Range("D21").Value = Range("O68").Value
Application.EnableEvents=true
Target.Select
End If

NickHK

 

[Barb Reinhardt]

I'd try putting

Application.screenupdating = FALSE

at the beginning of your code and

Application.ScreenUpdating = TRUE

at the end of yoru code.

HTH,
Barb Reinhardt

 

[Paige]

Unfortunately, neither of these work, individually or in combination. It
still moves the screen so that Row 47 is the first row showing. So I am left
with wondering if there is a way to capture where the screen is, so I can
return to that point; any ideas on that? Thanks for your help....

 

[Tom Ogilvy]

Tools=>Options=>Edit Tab, uncheck Move selection after enter.

If you want to control it in code, turn on the macro recorder while you do
it manually to get the code. Note that you would have to make this change
before any change was made to the cell - not in the change event. Perhaps in
the selection change event.

You can always bet the visible range with ActiveWindow.VisibleRange

ActiveWindow.VisibleRange(1)

is the top left cell in the visible range. If you have freeze panes or
horizontal/vertical splits in place, it may be more complex.

 

[Paige]

Thanks everyone; will work on this some more with all the suggestions.

 

[GS]

Hi Paige,

Add this to your code:

Dim lLeft As Long, lTop As Long

lLeft = ActiveWindow.ScrollColumn
lTop = ActiveWindow.ScrollRow
ActiveWindow.ScrollIntoView lLeft, lTop, 1, 1
'Use 1 for the width and height argument..
'(It safely defaults to the screen width and height)

Leave ScreenUpdating off. Note that it will automatically reset when your
code terminates (if no other sub/function is running that also has it turned
off), so you don't need to explicitly turn it back on unless it's absolutely
necessary.

hth
Garry