Center on Cell

[bposaner]

Hi

I've had a look around and can find anything really helpful.
I want to automatically centre and zoom in/out the worksheet on certain chosen cells.

For example:
A user enters/select cell, say "J76", and i want the page to centre on cell J76 and then zoom in to 150%, wait a few seconds and zoom back to 100% Then they may enter "B34" and then the page will recentre on B34, ETC.

Hopefully I've explained myself
Thanks for any help.
BP

 

[cornflakegirl]

Okay, this works, but you can't do anything in the 5 second wait, so there's probably a better way.

Mostly stolen from this thread: http://www.mrexcel.com/forum/showthread.php?t=68193

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 
Dim Rng As Range
Set Rng = ActiveCell
 
ActiveWindow.Zoom = 150
Application.Goto Target, True 'puts active cell in top left corner
Rng.Activate
With ActiveWindow
     On Error Resume Next
     .ScrollRow = Application.Max(1, .ScrollRow - .VisibleRange.Rows.Count \ 2 + Target.Rows.Count \ 2)
     .ScrollColumn = Application.Max(1, .ScrollColumn - .VisibleRange.Columns.Count \ 2 + Target.Columns.Count \ 2)
End With
 
Application.Wait Now + TimeValue("00:00:05")
 
ActiveWindow.Zoom = 100
Application.Goto Target, True
Rng.Activate
With ActiveWindow
     On Error Resume Next
     .ScrollRow = Application.Max(1, .ScrollRow - .VisibleRange.Rows.Count \ 2 + Target.Rows.Count \ 2)
     .ScrollColumn = Application.Max(1, .ScrollColumn - .VisibleRange.Columns.Count \ 2 + Target.Columns.Count \ 2)
End With
 
End Sub