Pick latest value (Week Ending Date) when switching to a sheet

[#1]

Hello,

I would like to write a macro to pick the latest 'weekEndDate' value when the use switches to a sheet.

So it would be like:

ActiveDocument.Fields("weekEndDate").Select (Max weekEndDate)

weekEndDat values:

'August 28, 2010', 'August 21, 2010', etc.

Can anybody help?

Thanks

Mike

[#2]

Hi,

Create a variable with the max date value in it.

vMaxDate = max(weekEndDate)

Use this variable in the macro for selecting the value.

set vMaxDate=ActiveDocument.Variables("vMaxDate")
ActiveDocument.Fields("weekEndDate").Select vMaxDate

Run this macro while opening the sheet and the selection will be done.

[#3]

Thanks for the response Haneesh but it looks like it doesnt work in my case. It stops on the last line for some reason.

I set a variable: vMaxDate = max(weekEndDate)

And run the following in a macro while opening the sheet:

set vMaxDate = ActiveDocument.Variables ("vMaxDate")

ActiveDocument.Fields("weekEndDate").Select vMaxDate <--- Goes into the debugger and highlights this line.

[disqr_rm]

Try putting a trigger at "on activate" of sheets. On Sheet Properties --> Triggers tab press "On activate". For Action choose "Select in Field". Put the field name in the Field and =max(weekEndDate) in the "Search String" field. If your date is not stored in the "date" format internally (number) then use MaxString instead of Max. Play with it a little, and it should work.

[#5]

Thanks Rakesh. However I do not have a Triggers tab. I am using QV developer 8.5

[#6 · Accepted solution]

Try this.

sub pickDate
 set vMaxDate1 = ActiveDocument.Variables ("vMaxDate")
 msgbox(vMaxDate1.GetContent.string)
 ActiveDocument.Fields("weekEndDate").Select vMaxDate1.GetContent.string
end sub

[#7]

Awesome. That Worked! Thanks Devang!

[disqr_rm]

In that case:

ActiveDocument.GetField("weekEndDate").Select ActiveDocument.Evaluate("max(weekEndDate)")

This one line should do the job in your macro. Just pick max or maxstring according to your data type.