MACRO - How to select a specific value in Listbox on Sheet Selection

[#1]

This is my first time using macros in Qlikview (and macros altogether in a very long time) I have a simple question regarding a macro when selecting a sheet.

I would like a specific listbox with years to be automatically selected with the latest year.

So on the OnActivateSheet Trigger, i have a macro which clears the selection:

ActiveDocument.Fields("Year").Clear

How would I get the field to be selected with the latest year?

The 'Year' field is in a 'Dates' table which has been retrieved on the load.

Hopefully somebody can help.

Thanks!

-Mike

[biester]

It's really essential in this case to know which version you are using, because with QV 9 there's no need for a macro.

Rgds,
Joachim

[biester]

Oh, I just saw that you are using the term "Trigger", so I assume you HAVE QV 9.

So:

OnActivateSheet Trigger is alright; BUT:

as action don't use "External/Run Macro" !!!! Use "Selection/Select in Field". There, for "field" you specify "Year", for "Search string" you specify "=max(Year)". That's all and it should run without macro programming.

Rgds,
Joachim

[#4]

Thanks for the quick reply!

Actually I am using QV 8.5 so I don't see the "Selection/Select in Field". On the Macro tab, I see Sheet Event Triggers: OnActivateSheet and OnLeaveSheet.

[#5]

Hi,

in this case i first create a variable that hold max year: =max({1} Year)

(lets say that this variable is named vMaxYear). Variables menu is in Settings --> Variable overview

next macro part:

1. Clear Year field (you already achieve this)

2. get content of vYear: set maxYear = ActiveDocument.GetVariable("vMaxYear")

3. Make selection in Year field: ActiveDocument.Fields("Year").Select maxYear.GetContent.String

And put the created sub in OnActivateSheet or OnLeaveSheet

Regards!

Stefan

[Anonymous · Accepted solution]

This is correct, but there is no need for varaible if use Evaluate:
ActiveDocument.Fields("Year").Select ActiveDocument.Evaluate("max(Year)")

[rwunderlich]

Or TopSelect.

ActiveDocument.Fields("Year").TopSelect "Year", 1

-Rob

[#8]

Both of the last 2 suggestions work for me. I could not get Stefan's to work but that is probably my fault.

However these solutions work to select the max year as long as the year is already selected. For example just say I have 3 years: 2007, 2008, 2009.

Before I select the sheet if:

1) 2007,2008,2009 are selected --> 2009 becomes selected

2) none are selected --> 2009 becomes selected

3) 2007,2008 are selected --> 2008 becomes selected ... however ideally I would like 2009 to becomes selected. Does anybody know how to achieve that?

Anyways the solution still works for me as the main thing is one year needs to be selected on this sheet.

Thanks everybody for your help!

-Mike

[Anonymous]

Mike, if you use "Evaluate", simply change expression to
("max(all Year)")
It will ignore selections

[#10]

That works! wow that was so simple.. Thanks!