2 Replies Latest reply: Aug 13, 2009 4:48 PM by Karen Smith RSS

    Macro to select default values

    Karen Smith











      I am using this macro to clear my selection values upon open and select current year as default selection value. This works for most object witht exception of any object that has a set analysis coding. It is necessary to clear all then make year selection in order for Set analysis to work properly. Can some one tell me how I can make the default selection value work for objects with set analysis?


      Sub SetDefaulFYYear
      ActiveDocument.Fields("FYYear").Select "2009"
      ActiveDocument.Fields("CalYear").Select "2009"
      End Sub

      Tried two set analysis statements


      Current Year :Sum({<FY


      Year={$(=GetFieldSelections(FYYear))}>} BOOKVALUE)




      Last Year: Sum


      ({$<FYYear={$(=GetFieldSelections(FYYear)-1)}>} BOOKVALUE)





      Current Year: sum



      Last Year: sum({<FYYear={$(=Only(FYYear)-1)}>}PLANVALUE)


      Both set analysis code gives the same results. values are displayed only after sheet has been cleared and year value selected.




        • Macro to select default values
          Oleg Troyansky


          I can only guess that you might have other selection fields related to dates, and those fields, when selected, distort your expected result from the Set Analysis Expression.

          For example - if you allow user selection in fields Year, Month and Day, and someone selected a specific month, your Set Analysis expressions that only take care of the Year, will show results only for the selected Month.

          If that's the case, you can ignore user selections in Date-related fields by specifying them in your set analysis:


          ({<FYYear={$(=Only(FYYear))}, Month= , Day= >}PLANVALUE)