Keep Consistent cell positions on screen across tabs
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
--
Greetings from New Zealand
Bill K
"Al" wrote in message
...
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.
|