can't get find date macro to work

[still learning]

Hi

I’m can’t get this macro to work.
I make a new worksheet
I put in today's date with format m/dd/yyyy in A7
The rest of the sheet is blank
I put this macro in sheet1 of the VBA window

Private Sub Worksheet_activate()
Cells.Find(Date).Select
Set C = [A:A].Find(Format(Date, "m/dd/yyyy"))
If Not C Is Nothing Then C.Select
ActiveWindow.ScrollRow = ActiveCell.Row
ActiveWindow.SmallScroll Down:=-1
End Sub

I save and close the sheet
(yes I made it an macro enabled workbook)
When I open it, the cursor goes to A1
That’s where it was when I saved and closed it
I tried changing Worksheet_activate to Worksheet_open
Nothing is in This workbook
I’m using File open and clicking on the sheet


mike

 

[Joe4]

I don't think you need the FORMAT function in your find statement. It does not care about the format, only that it is a valid date.

Does this do what you want?

Private Sub Worksheet_Activate()
    Dim C As Range
    Set C = [A:A].Find(Date)
    If Not C Is Nothing Then C.Select
End Sub

 

[still learning]

Hi Joe4
it's been awhile
alright to sat happy father's day?
about the macro
nope didn't work
the sheet opens where the cursor was when it was saved and closed

mike

 

[Joe4]

Hi Joe4
it's been awhile
alright to sat happy father's day?
about the macro
nope didn't work
the sheet opens where the cursor was when it was saved and closed

mike

Check the following two things:

1. Are you absolutely certain that you have an entry in column A with the current date, entered as a valid date (and NOT entered as text)?
And there is no time component to this entry?

If it is indeed entered as text, then you would need to use the FORMAT function, like you had originally.

Private Sub Worksheet_Activate()
    Dim C As Range
    Set C = [A:A].Find(Format(Date, "m/d/yyyy"))
    If Not C Is Nothing Then C.Select
End Sub

2. Do you have any other VBA code in this particular worksheet module which could be interfering with this?

3. Are you sure that you have Events enabled?

 

[still learning]

I used end down and end up to check that there is nothing else in A:A
I typed in today's date
I used this following macro before I opened the sheet

Sub enableevents()
Application.enableevents = True
End Sub

I tried it again with and without the format function
I used =today()
and did a paste special to get rid of the formula
and tried it with and without the format function
Then just before i sent this...
I made a macro of the Dim C as Range..etc and put it in a modular
after I opened the sheet ( and the private sub didn't work)
I opened the view macro and clicked on the new macro and run..
it worked.
I took out the format function and it also worked
I tried it a couple of times after saving and closing and then opening it
it worked with the macro in the modular but not the private sub


mike

 

[Joe4]

What is the name of the module you have placed this code in?
In order to get it to work automatically, you MUST put it in the specific SHEET module.
If you put it in any other module (like one you manually created), it will not work.

 

[still learning]

the Private Sub Worksheet_Activate() is in sheet1
The macro I made of the Dim C as Range..etc, I put in a module1
both are in the workbook that the test sheet is in

 

[Joe4]

the Private Sub Worksheet_Activate() is in sheet1
The macro I made of the Dim C as Range..etc, I put in a module1
both are in the workbook that the test sheet is in

I am not quite sure what you mean, as you only posted one block of code originally.

Can you post the VBA code you have in each place?

 

[still learning]

the original code i'm having trouble is

Private Sub Worksheet_Activate()
Dim C As Range
Set C = [A:A].Find(Date)
If Not C Is Nothing Then C.Select
End Sub

Which i put in sheet1 of the workbook (sheet1 is where I typed in today's date in A7
After a couple of your reply s, I made a macro using a part of the Private Sub Worksheet_Activate()
and called it Test and put it in a module

Sub Test()
Dim C As Range
Set C = [A:A].Find(Date)
If Not C Is Nothing Then C.Select
End Sub

I then ran this test macro after i opened the sheet and the troubled macro did not work and this test macro did work


mike

 

[Joe4]

OK, note that "Worksheet_Activate" only runs when you CHANGE sheets, and that sheet is activated.
If you save your workbook on that sheet, and re-open it, since it is already on that sheet, it will NOT run.
However, if you select another sheet in that workbook and come back to it, then it will run.

If you only have one sheet in your workbook, or want it to run when the workbook is first opened, you should place the body of the code in the "Workbook_Open" event.
Note that code MUST go in the "ThisWorkbook" module, not in any sheet modules.

If you want to code to run whenever the workbook is opened OR when that particular sheet is selected/activated, then put the code in BOTH places.