Keep Consistent cell positions on screen across tabs

[Al]

I'm looking for code which will hold my cell references constant when I tab
through my worksheets. My workbook contains 34 different worksheets, so if
my activecell is N34 in the upper left hand corner of my screen, I'd like
the activecell to continue to be N34 in the upper left hand corner of my
screen when I tab through the worksheets.

Thanks in advance.

 

[sebastienm]

Hi,
Witrhin the ThisWorkbook code module, you can track the active selected cell
and adjust the selection when a sheet activates.; see code below (assumes
sheets are worksheets, not chartsheets)

'-------- In ThisWOrkbook code module --------------------------
Private mActiveCell As Range

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Not mActiveCell Is Nothing Then
Application.ScreenUpdating = True
Application.Goto Sh.Range(mActiveCell.Address), True
End If
End Sub


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
'assumes sh is worksheet and not chart
Set mActiveCell = Target
End Sub
'---------------------------------------------------------

 

[Peter T]

Try this in the ThisWorkbook module

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
On Error GoTo done
Application.EnableEvents = False
Application.Goto Range("N34"), True

done:
Application.EnableEvents = True

End Sub

Regards,
Peter T

 

[Al]

Thanks for responding Peter.

Try this in the ThisWorkbook module

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
On Error GoTo done
Application.EnableEvents = False
Application.Goto Range("N34"), True

done:
Application.EnableEvents = True

End Sub

Regards,
Peter T

 

[Al]

Thanks Sebastian; this was exactly what i was looking for.

 

[Bill Kuunders]

This macro will move a cell you double click on sheet1
into the top left corner of the screen for all sheets.

You will have to adjust the numbers 16, 7, 41 and 20 to suit your
screen size and row height / column width
In other words check how many rows and columns you can see when
A1 is the active cell.

You need to enter the name of this macro into the code for sheet1.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
cellselectsheet1
End Sub

and enter this macro into a general module
Sub cellselectsheet1()
Dim rw As Long
Dim cl As Long
Sheet1.Activate
rw = ActiveCell.Row
cl = ActiveCell.Column

For Each Sheet In Sheets
On Error Resume Next
Sheet.Select
Range("A1").Activate
Range("A1").Offset(rw - 1, cl - 1).Activate

ActiveCell.Activate

If cl > 16 Then
ActiveWindow.SmallScroll ToRight:=7
Else
ActiveWindow.SmallScroll ToRight:=cl - 1
End If
If rw > 41 Then
ActiveWindow.SmallScroll Down:=20
Else
ActiveWindow.SmallScroll Down:=rw - 1
End If

Next
Sheet1.Select

End Sub

 

[Bill Kuunders]

I should have known............

Right after I send you that complicated stuff about scrolling
down and to the right I picked up the proper code from exceltip.com

The macro you're after is now............

Sub cellselectsheet1()
Dim rw As Long
Dim cl As Long
Sheet1.Activate
rw = ActiveCell.Row
cl = ActiveCell.Column

For Each Sheet In Sheets
On Error Resume Next
Sheet.Select
Range("A1").Activate
Range("A1").Offset(rw - 1, cl - 1).Activate
Application.Goto ActiveCell, True

Next
Sheet1.Select

End Sub

and it wouldn't supprise me at all if someone else can make it a lot shorter
still.

( :>))