Launch excel showing today's date

Afternoon All,

In our office we have a very simple diary on Excel. We have the day's date
going down column B, and staff names across the top. We simply type in the
cell under our name next to todays date to show what are our plans for that
day.

This Diary goes back to early this year and every time we open the diary
(shared workbook) it starts with cell b4 highlighted (January 4 2005). This
means we have to scroll down to find today's date.

Is it possible to have Excel automatically find today's date in column B and
scroll down to it when we open excel, or can we record a macro that is
triggered by a click?

Cheers,

Richard

Richard, try this, put in thisworkbook code

Private Sub Workbook_Open()

'will go to today's date, in column B, on open

On Error Resume Next

ThisDay = DateValue(Date)

Range("B2").Select

i = 0

Do While ActiveCell.Offset(i, 0).Value <> ThisDay

i = i + 1

Loop

ActiveCell.Offset(i, 0).Select

End Sub
--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003


"Richard V" <Richard V@discussions.microsoft.com> wrote in message
news:A407F6F0-B919-41EB-B80D-0898696840D9@microsoft.com...
> Afternoon All,
>
> In our office we have a very simple diary on Excel. We have the day's date
> going down column B, and staff names across the top. We simply type in the
> cell under our name next to todays date to show what are our plans for
> that
> day.
>
> This Diary goes back to early this year and every time we open the diary
> (shared workbook) it starts with cell b4 highlighted (January 4 2005).
> This
> means we have to scroll down to find today's date.
>
> Is it possible to have Excel automatically find today's date in column B
> and
> scroll down to it when we open excel, or can we record a macro that is
> triggered by a click?
>
> Cheers,
>
> Richard

Hi Paul,

I tried this but Excel didn't seem to do anything different to usual when
opening. However I found this on google that seems to do the job:

Sub Today()
x = 8
Do
x = x + 1
If Cells(x, 2).Value = Date Then
Cells(x, 3).Select
Else
'
End If
Loop Until Cells(x + 1, 1) = ""
End Sub

I have assigned this to a button.

Cheers,

Richard V


"Paul B" wrote:

> Richard, try this, put in thisworkbook code
>
> Private Sub Workbook_Open()
>
> 'will go to today's date, in column B, on open
>
> On Error Resume Next
>
> ThisDay = DateValue(Date)
>
> Range("B2").Select
>
> i = 0
>
> Do While ActiveCell.Offset(i, 0).Value <> ThisDay
>
> i = i + 1
>
> Loop
>
> ActiveCell.Offset(i, 0).Select
>
> End Sub
> --
> Paul B
> Always backup your data before trying something new
> Please post any response to the newsgroups so others can benefit from it
> Feedback on answers is always appreciated!
> Using Excel 2002 & 2003
>
>
> "Richard V" <Richard V@discussions.microsoft.com> wrote in message
> news:A407F6F0-B919-41EB-B80D-0898696840D9@microsoft.com...
> > Afternoon All,
> >
> > In our office we have a very simple diary on Excel. We have the day's date
> > going down column B, and staff names across the top. We simply type in the
> > cell under our name next to todays date to show what are our plans for
> > that
> > day.
> >
> > This Diary goes back to early this year and every time we open the diary
> > (shared workbook) it starts with cell b4 highlighted (January 4 2005).
> > This
> > means we have to scroll down to find today's date.
> >
> > Is it possible to have Excel automatically find today's date in column B
> > and
> > scroll down to it when we open excel, or can we record a macro that is
> > triggered by a click?
> >
> > Cheers,
> >
> > Richard
>
>
>

Richard, glad you got it to do what you want, did you put the code I posted
in thisworkbook module? like this


To put in this macro, from your workbook right-click the workbook's icon and
pick View Code. This icon is to the left of the "File" menu this will open
the VBA editor, in the left hand window double click on thisworkbook, under
your workbook name, and paste the code in the window that opens on the right
hand side, press Alt and Q to close this window and go back to your
workbook, now this will run every time you open the workbook. If you are
using excel 2000 or newer you may have to change the macro security
settings to get the macro to run. To change the security settings go to
tools, macro, security, security level and set it to medium
--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003


"Richard V" <RichardV@discussions.microsoft.com> wrote in message
news:2F05C6B8-BC1D-4783-A009-3849BFF978D2@microsoft.com...
> Hi Paul,
>
> I tried this but Excel didn't seem to do anything different to usual when
> opening. However I found this on google that seems to do the job:
>
> Sub Today()
> x = 8
> Do
> x = x + 1
> If Cells(x, 2).Value = Date Then
> Cells(x, 3).Select
> Else
> '
> End If
> Loop Until Cells(x + 1, 1) = ""
> End Sub
>
> I have assigned this to a button.
>
> Cheers,
>
> Richard V
>
>
> "Paul B" wrote:
>
>> Richard, try this, put in thisworkbook code
>>
>> Private Sub Workbook_Open()
>>
>> 'will go to today's date, in column B, on open
>>
>> On Error Resume Next
>>
>> ThisDay = DateValue(Date)
>>
>> Range("B2").Select
>>
>> i = 0
>>
>> Do While ActiveCell.Offset(i, 0).Value <> ThisDay
>>
>> i = i + 1
>>
>> Loop
>>
>> ActiveCell.Offset(i, 0).Select
>>
>> End Sub
>> --
>> Paul B
>> Always backup your data before trying something new
>> Please post any response to the newsgroups so others can benefit from it
>> Feedback on answers is always appreciated!
>> Using Excel 2002 & 2003
>>
>>
>> "Richard V" <Richard V@discussions.microsoft.com> wrote in message
>> news:A407F6F0-B919-41EB-B80D-0898696840D9@microsoft.com...
>> > Afternoon All,
>> >
>> > In our office we have a very simple diary on Excel. We have the day's
>> > date
>> > going down column B, and staff names across the top. We simply type in
>> > the
>> > cell under our name next to todays date to show what are our plans for
>> > that
>> > day.
>> >
>> > This Diary goes back to early this year and every time we open the
>> > diary
>> > (shared workbook) it starts with cell b4 highlighted (January 4 2005).
>> > This
>> > means we have to scroll down to find today's date.
>> >
>> > Is it possible to have Excel automatically find today's date in column
>> > B
>> > and
>> > scroll down to it when we open excel, or can we record a macro that is
>> > triggered by a click?
>> >
>> > Cheers,
>> >
>> > Richard
>>
>>
>>