Scrolling and date matching

[Johnnyboy5]

I have a holiday planner leave sheet in Excel for 2009 /2010 with the
top row being the dates of the days of the “weekday” entered. I also
have one cell with =Today() in it, I need a macro to Auto_open and
scroll the sheet along to the same date as Today.

Or a Macro where I can enter the week beginning date, e.g 17/8/09
and the sheet to auto scroll across to that date.


Ta guys and gals

 

[OssieMac]

Hi Johnny,

The following code will place the cell with =Today() in the top left of the
window.

To install the code. (Not sure if you know how so just in case)
Alt/F11 to open the VBA editor.
On the left of the screen the Project explorer should be displayed with the
Sheet modules and one named ThisWorkbook. (If you can't see the Project
Explorer the Ctrl/r will display it.

Double Click ThisWorkbook.

Copy and paste the code into the large white area.

Edit the worksheet name towards the top of the code where the comment is.

Click the X in the red background top right to close the VBA editor.

Save the workbook. (If xl2007 ensure you save as Excel Enabled workbook.)

Private Sub Workbook_Open()
Dim rngRow1 As Range
Dim rngToFind As Range
Dim dateToday As Date

'Edit Sheet1 to match your worksheet
Sheets("Sheet1").Select

With ActiveSheet
Set rngRow1 = .Rows(1)
End With

dateToday = Date

With rngRow1
Set rngToFind = .Find(What:=dateToday, _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
End With

If Not rngToFind Is Nothing Then
rngToFind.Select
ActiveWindow.ScrollRow = rngToFind.Row
ActiveWindow.ScrollColumn = rngToFind.Column
Else
MsgBox "Date " & dateToday & " not found"
End If

End Sub

 

[Atishoo]

Hi
I dont know which cell you have todays date in in thi sub i have set it as
B2 and assumed that your dates are all accross row 1.

With Worksheets("sheet1").Range("1:1")
Set c = .Find(Range("B2").Value, LookIn:=xlValues)
If Not c Is Nothing Then
Application.Goto reference:=Range(c.Address), Scroll:=True
End If
End With
good luck John

 

[OssieMac]

I should have said that I assumed from your request that Today() is in row 1
and that is where the code is looking for it. If not in row 1 then edit the
following line and change the .Row(1) to match the row where Today() is.

Set rngRow1 = .Rows(1)

 

[Johnnyboy5]

I should have said that I assumed from your request that Today() is in row 1
and that is where the code is looking for it. If not in row 1 then edit the
following line and change the .Row(1) to match the row where Today() is.

Set rngRow1 = .Rows(1)

Thanks you guys, will give it a try and get back to this post with my
results

Johnnyboy

 

[Johnnyboy5]

Thanks you guys,  will give it a try and get back to this post with my
results

Johnnyboy

Thanks Ossie, works a treat, also sent a reply to author in this
post... I think

 

[Rick Rothstein]

Your Range("B2") and Range(c.Address) references will refer to the ActiveSheet which is not guaranteed to be (your example) "sheet1". Perhaps this would be a better way to rearrange your code so that all references can be dotted in order to refer back to the same worksheet...

Dim c As Range
......
......
With Worksheets("sheet1")
Set c = .Range("1:1").Find(.Range("B2").Value, LookIn:=xlValues)
If Not c Is Nothing Then
Application.Goto reference:=.Range(c.Address), Scroll:=True
End If
End With

 

[Atishoo]

Indeed it would! fortunately ossie posted a far superior code (am just
playing with that at the moment) am looking to adapt it to make a rolling
page that scrolls across a 24 hour period like a linear clock using similar
sub with time rather than dates!

 

[Atishoo]

hi johnnyboy
you might want to click ossies post as being useful! its a great little
piece of code hes posted there.

 

[Johnnyboy5]

hi johnnyboy
you might want to click ossies post as being useful! its a great little
piece of code hes posted there.

Yer your right, have just done 5 star !

JB

 

[Johnnyboy5]

Yer your right,  have just done   5 star !

JB

Hi

just one more question, how do I set it it to find the date to be 7
days early than the current date?

many thanks Ossie.

 

[Johnnyboy5]

Hi

just one more question,  how do I set it it to find the date to be 7
days early than the current date?

many thanks  Ossie.

Hey no worries sorted it Date () - 7