Jumping to a certain cell

[Guest]

Dear Excell(ent) users,

I have a file with multiple sheets. In those sheets I work with dates
(dd-mm-yy). What I am looking for is a macro which opens the file in those
sheets at the current date.

For instance 15-03-2007 is in Cell BX3, so when it would be 15-03-2007 when
I open that sheet, the cursor should be in BX3.

Can you help me, please?

Thank you !

 

[David McRitchie]

You would want to use an Event macro.

Event Macros, Worksheet Events and Workbook Events
http://www.mvps.org/dmcritchie/excel/event.htm
Worksheet_Activate Event (#ws_activate)
http://www.mvps.org/dmcritchie/excel/event.htm#ws_activate

which is set up for the current date upon sheet activation
you can limit it to a specific column.

 

[Guest]

Put this in Worksheet code for the sheet in question:

Private Sub Worksheet_Activate()
Dim s As String
s = Date
For Each r In ActiveSheet.UsedRange
If Not IsEmpty(r) Then
If s = r.Value Then
r.Select
Exit Sub
End If
End If
Next
End Sub

 

[Gord Dibben]

To have the activate operate on all sheets place the code into Thisworkbook
module and rename to

Private Sub Workbook_SheetActivate(ByVal Sh As Object)


Gord Dibben MS Excel MVP