Macro for Field Selction

[montubhardwaj]

Hi Community,

I want to write a macro which will fulfill the below conditions: Let's say, I have QV report with 3 sheets in it and all the sheets have selection criteria say Month, Quarter and Year. If I am selecting any month in sheet 1 or sheet two, that should reflect in sheet 3 as well(which will happen automatically).But when I am not selecting any month in sheet 1 or sheet 2 , sheet 3 should always show March Month. Also, If we alter the selections in other sheets, that should affect sheet 3 as well.

The field Month has values like Jan,Feb to Dec. I am working in QV 8.20 and hence need to write a macro for the same.

Many thanks in advance.

[montubhardwaj]

Hi,

Anyone has any idea about this ?

[#3 · Accepted solution]

Hi Sharma,

you can use the script bellow to achieve this. If you have any questions please ask 🙂

sub SelectMarInSheet3

set doc = ActiveDocument

set MonthSelections = doc.fields("Month").GetSelectedValues 'Get Month field selected values

for i = 0 to MonthSelections.Count - 1

months = months + 1 ' count selected values in Month field

next

activeSheet = ActiveDocument.GetLayout.ActiveSheetId 'Get id of active sheet

set v = ActiveDocument.Variables("MarSelected") 'define custom variable

if months = 0 and activeSheet = "Document\SH03" then ' if there is no selection in month and sheet is Sheet 3

ActiveDocument.Fields("Month").Select "Mar" ' select Mar

v.SetContent "1",true 'set custom value to 1 --> Mar was selected

elseif activeSheet <> "Document\SH03" and v.GetContent.String = "1" then 'if sheet is not Sheet3 and Mar was selected in Sheet3

ActiveDocument.Fields("Month").Clear 'Clear Mar. This is used when Mar was selected in Sheet3 so there is no need to be selected in other sheets

else

v.SetContent "0",true ' If Mar was not selected (there is other selections in Month field) do nothing

end if

end sub

Stefan

[#4]

Forget to mention that this macro should be added on all sheest on OnActivateSheet event

[montubhardwaj]

Hi Stefan,

Thanks a lot for your time and effort. This worked perfectly. The logic was awesome.

Thanks again.