Activating "Todays Date" column upon opening?

[Jeremy H via OfficeKB.com]

Ok I have a workbook that tracks Vacation Dates, Attendence, and Schedules
for all employees in a department.
Row 8 Starting from column B (B8) and beyond (B9, B10, B11, B12...) contains
the date. This runs all the way to the end of the year (12/31/05)

Now what I would like to do is either A:
Make it automatically (search & activate) the column for "today's" date when
I select the worksheet

Or B:
Make a button that will do the same.

Here is a rough guestimate of what the sheet looks like



A B C D E F


8/01 8/02 8/02 8/04 8/04...........
Absent
Days
Days

Absent
Nights
Nights

Absent
Overnights
Overnights

Now on each row in the date columns, I have "Data, Validation, List,
day_shift" or "night_shift" etc so if an employee calls in sick on 8/02 I go
to a "Dayshift" slot, then scroll across to the current date, then select
that agents name from the list.

This is on a worksheet called "Attendence"

Ideally when I click on attendence, the column for today's days, will be the
visible or active column instead of having to scroll across.

Alternatively a button would work that I just press and it scrolls across to
todays date column

I know I can use "=DATE()" to determine the current date anytime the book is
opened, know how do I process that variable into selecting the appropriate
column?

 

[Guest]

You could use the worksheet activate event:

Private Sub Worksheet_Activate()
Dim tday As Range
Dim eCol As Integer
eCol = Cells(8, Columns.Count).End(xlToLeft).Column
With Range(Cells(8, 2), Cells(8, eCol))
Set tday = .Find(what:=Date)
If Not tday Is Nothing Then
tday.Select
End If
End With
End Sub

This is worksheet event code. Right click the sheet tab, select view code
and paste the code in there.

Hope this helps
Rowan

 

[Jeremy H via OfficeKB.com]

Thank you very much!

Works like a charm!

You could use the worksheet activate event:

Private Sub Worksheet_Activate()
Dim tday As Range
Dim eCol As Integer
eCol = Cells(8, Columns.Count).End(xlToLeft).Column
With Range(Cells(8, 2), Cells(8, eCol))
Set tday = .Find(what:=Date)
If Not tday Is Nothing Then
tday.Select
End If
End With
End Sub

This is worksheet event code. Right click the sheet tab, select view code
and paste the code in there.

Hope this helps
Rowan

Ok I have a workbook that tracks Vacation Dates, Attendence, and Schedules
for all employees in a department.

[quoted text clipped - 41 lines]

opened, know how do I process that variable into selecting the appropriate
column?

 

[Guest]

You're welcome.

Thank you very much!

Works like a charm!

You could use the worksheet activate event:

Private Sub Worksheet_Activate()
Dim tday As Range
Dim eCol As Integer
eCol = Cells(8, Columns.Count).End(xlToLeft).Column
With Range(Cells(8, 2), Cells(8, eCol))
Set tday = .Find(what:=Date)
If Not tday Is Nothing Then
tday.Select
End If
End With
End Sub

This is worksheet event code. Right click the sheet tab, select view code
and paste the code in there.

Hope this helps
Rowan

Ok I have a workbook that tracks Vacation Dates, Attendence, and Schedules
for all employees in a department.

[quoted text clipped - 41 lines]

opened, know how do I process that variable into selecting the appropriate
column?