Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Keep Consistent cell positions on screen across tabs
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Keep Consistent cell positions on screen across tabs
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 '--------------------------------------------------------- -- Regards, Sébastien <http://www.ondemandanalysis.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Keep Consistent cell positions on screen across tabs
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" 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Keep Consistent cell positions on screen across tabs
Thanks for responding Peter.
"Peter T" wrote: 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" 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Keep Consistent cell positions on screen across tabs
Thanks Sebastian; this was exactly what i was looking for.
"sebastienm" wrote: 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 '--------------------------------------------------------- -- Regards, Sébastien <http://www.ondemandanalysis.com |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Keep Consistent cell positions on screen across tabs
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. ( :)) -- Greetings from New Zealand Bill K "Bill Kuunders" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Testing positions in a cell | Excel Discussion (Misc queries) | |||
How can I view all of my tabs in one screen? Can I layer them? | Excel Discussion (Misc queries) | |||
I want the tabs to run down the side of the screen | Excel Discussion (Misc queries) | |||
sheet tabs are gone (screen resolution) | Excel Programming | |||
How do I sum using relative cell positions? | Excel Worksheet Functions |