Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
scrolling to a specific cell
sorry - having a bit of a mind-block. I have a worksheet with a column of
dates and would like to auto-scroll to the current date (if available) in column B, every time the sheet is activated (so that the row is visible for entering fresh data)... i know i should be able to do this, but just can't think how... its been a long day! Can anyone give me the answer? tia, tim |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
scrolling to a specific cell
Option Explicit
Private Sub Worksheet_Activate() Cells.Find(Date).Select End Sub Hope this helps, Amit "Tim Marsh" wrote in message ... sorry - having a bit of a mind-block. I have a worksheet with a column of dates and would like to auto-scroll to the current date (if available) in column B, every time the sheet is activated (so that the row is visible for entering fresh data)... i know i should be able to do this, but just can't think how... its been a long day! Can anyone give me the answer? tia, tim |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
scrolling to a specific cell
Sorry missed the scrolling automatically part, so please use this instead :
Option Explicit Private Sub Worksheet_Activate() Cells.Find(Date).Select ActiveWindow.ScrollRow = ActiveCell.Row End Sub Amit "Amit Shanker" wrote in message ... Option Explicit Private Sub Worksheet_Activate() Cells.Find(Date).Select End Sub Hope this helps, Amit "Tim Marsh" wrote in message ... sorry - having a bit of a mind-block. I have a worksheet with a column of dates and would like to auto-scroll to the current date (if available) in column B, every time the sheet is activated (so that the row is visible for entering fresh data)... i know i should be able to do this, but just can't think how... its been a long day! Can anyone give me the answer? tia, tim |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
scrolling to a specific cell
Thank you Amit (& Tom)
this'll do nicely... for the record i did fine yet another way, but it involved a 'do...until' loop and was very inefficient. cheers from foggy, wet England, tim "Amit Shanker" wrote in message ... Sorry missed the scrolling automatically part, so please use this instead : Option Explicit Private Sub Worksheet_Activate() Cells.Find(Date).Select ActiveWindow.ScrollRow = ActiveCell.Row End Sub Amit "Amit Shanker" wrote in message ... Option Explicit Private Sub Worksheet_Activate() Cells.Find(Date).Select End Sub Hope this helps, Amit "Tim Marsh" wrote in message ... sorry - having a bit of a mind-block. I have a worksheet with a column of dates and would like to auto-scroll to the current date (if available) in column B, every time the sheet is activated (so that the row is visible for entering fresh data)... i know i should be able to do this, but just can't think how... its been a long day! Can anyone give me the answer? tia, tim |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
scrolling to a specific cell
Note that in most versions of Excel Cell.Find(Date) doesn't work very
reliably. -- Regards, Tom Ogilvy "Amit Shanker" wrote in message ... Sorry missed the scrolling automatically part, so please use this instead : Option Explicit Private Sub Worksheet_Activate() Cells.Find(Date).Select ActiveWindow.ScrollRow = ActiveCell.Row End Sub Amit "Amit Shanker" wrote in message ... Option Explicit Private Sub Worksheet_Activate() Cells.Find(Date).Select End Sub Hope this helps, Amit "Tim Marsh" wrote in message ... sorry - having a bit of a mind-block. I have a worksheet with a column of dates and would like to auto-scroll to the current date (if available) in column B, every time the sheet is activated (so that the row is visible for entering fresh data)... i know i should be able to do this, but just can't think how... its been a long day! Can anyone give me the answer? tia, tim |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
scrolling to a specific cell
Tom, any idea why ? Would like to understand this better.
Thanks, Amit "Tom Ogilvy" wrote in message ... Note that in most versions of Excel Cell.Find(Date) doesn't work very reliably. -- Regards, Tom Ogilvy "Amit Shanker" wrote in message ... Sorry missed the scrolling automatically part, so please use this instead : Option Explicit Private Sub Worksheet_Activate() Cells.Find(Date).Select ActiveWindow.ScrollRow = ActiveCell.Row End Sub Amit "Amit Shanker" wrote in message ... Option Explicit Private Sub Worksheet_Activate() Cells.Find(Date).Select End Sub Hope this helps, Amit "Tim Marsh" wrote in message ... sorry - having a bit of a mind-block. I have a worksheet with a column of dates and would like to auto-scroll to the current date (if available) in column B, every time the sheet is activated (so that the row is visible for entering fresh data)... i know i should be able to do this, but just can't think how... its been a long day! Can anyone give me the answer? tia, tim |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
scrolling to a specific cell
I can't say why - I don't have the code for the Find method. I just know
that in my experience, match as I show, is much more reliable (never seen it fail if there is a match - but even with that, I have to convert to long for the search). On the other hand, I have plenty of problems with Find not finding dates when they exist. Some have suggest that for the lookin parameter xlValues works while others say xlformulas work. I haven't found a consistent solution (and I imagine this would be affected by how the value in the cell is produced). Tim doesn't set any parameters, so such usage is a crap shoot as to what the last setting the user applied was/is. On the other hand, I work mostly in xl97 and xl2000, so it may not be a problem for you. -- Regards, Tom Ogilvy "Amit Shanker" wrote in message ... Tom, any idea why ? Would like to understand this better. Thanks, Amit "Tom Ogilvy" wrote in message ... Note that in most versions of Excel Cell.Find(Date) doesn't work very reliably. -- Regards, Tom Ogilvy "Amit Shanker" wrote in message ... Sorry missed the scrolling automatically part, so please use this instead : Option Explicit Private Sub Worksheet_Activate() Cells.Find(Date).Select ActiveWindow.ScrollRow = ActiveCell.Row End Sub Amit "Amit Shanker" wrote in message ... Option Explicit Private Sub Worksheet_Activate() Cells.Find(Date).Select End Sub Hope this helps, Amit "Tim Marsh" wrote in message ... sorry - having a bit of a mind-block. I have a worksheet with a column of dates and would like to auto-scroll to the current date (if available) in column B, every time the sheet is activated (so that the row is visible for entering fresh data)... i know i should be able to do this, but just can't think how... its been a long day! Can anyone give me the answer? tia, tim |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
scrolling to a specific cell
Use the sheet activate event:
Dim rng as Range set rng = me.Range(me.Cells(1,1),me.Cells(me.rows.count,1).E nd(xlup)) res = Application.Match(clng(date),rng,0) if not iserror(res) then rng(res).Select End if -- Regards, Tom Ogilvy "Tim Marsh" wrote in message ... sorry - having a bit of a mind-block. I have a worksheet with a column of dates and would like to auto-scroll to the current date (if available) in column B, every time the sheet is activated (so that the row is visible for entering fresh data)... i know i should be able to do this, but just can't think how... its been a long day! Can anyone give me the answer? tia, tim |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
very large WB-what can I do to find a specific tab w/o scrolling? | Excel Discussion (Misc queries) | |||
Excel- smooth scrolling (instead of 'snap' scrolling) | Setting up and Configuration of Excel | |||
Vertical scrolling...jumps rather than smooth scrolling | Excel Discussion (Misc queries) | |||
Live Scrolling/Real-Time /Smooth Scrolling doesn't work for me in Excel, even 2007 beta | Excel Discussion (Misc queries) | |||
Live Scrolling/Real-Time /Smooth Scrolling doesn't work for me in Excel 2003 | Excel Discussion (Misc queries) |