Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Talk to Experts Tuesday, Live Q&A, September 22: Moving from QlikView to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

Macro to Clear all selections except dates?

All, i have been searching the archives for any easy sub routine that would clear all active fields EXCPET Year, Quarter and Date...

Any ideas?

Thanks in advance

1 Solution

Accepted Solutions
Highlighted
Not applicable

You could lock those fields, clear all and then unlock them?

ActiveDocument.Fields("Year").Lock
ActiveDocument.Fields("Quarter").Lock
ActiveDocument.Fields("Date").Lock

ActiveDocument.ClearAll

ActiveDocument.Fields("Year").Unlock
ActiveDocument.Fields("Quarter").Unlock
ActiveDocument.Fields("Date").Unlock

View solution in original post

8 Replies
Highlighted
Not applicable

You could lock those fields, clear all and then unlock them?

ActiveDocument.Fields("Year").Lock
ActiveDocument.Fields("Quarter").Lock
ActiveDocument.Fields("Date").Lock

ActiveDocument.ClearAll

ActiveDocument.Fields("Year").Unlock
ActiveDocument.Fields("Quarter").Unlock
ActiveDocument.Fields("Date").Unlock

View solution in original post

Highlighted
Creator III
Creator III

True, but I was trying this out as well

Sub Clear

ActiveDocument.Fields("Year").ClearAllButThis

ActiveDocument.Fields("Month").ClearAllButThis

End Sub



However, it doesn't work since it doesn't like multiple fields..... Anyone know how to select ActiveDocument.Fields("Year")("Month".ClearAllButThis









Highlighted
Not applicable

I don't think that is possible. ClearAllButThis seems to only work for one field.

I think if you want to avoid Lock, it gets more complicated. This should work (I tried it using one field):

set yearSelect = ActiveDocument.Fields("Year").GetSelectedValues
set qtrSelect = ActiveDocument.Fields("Quarter").GetSelectedValues
set dateSelect = ActiveDocument.Fields("Date").GetSelectedValues

ActiveDocument.ClearAll

set fv = ActiveDocument.Fields("Year").GetNoValues

for i = 0 to yearSelect.Count - 1
fv.Add

fv(i).Text = yearSelect.Item(i).Text
fv(i).IsNumeric = false
next

ActiveDocument.Fields("Year").SelectValues fv


set fv = ActiveDocument.Fields("Quarter").GetNoValues

for i = 0 to qtrSelect.Count - 1
fv.Add

fv(i).Text = qtrSelect.Item(i).Text
fv(i).IsNumeric = false
next

ActiveDocument.Fields("Quarter").SelectValues fv


set fv = ActiveDocument.Fields("Date").GetNoValues

for i = 0 to dateSelect.Count - 1
fv.Add

fv(i).Text = dateSelect.Item(i).Text
fv(i).IsNumeric = false
next

ActiveDocument.Fields("Date").SelectValues fv

Highlighted
MVP & Luminary
MVP & Luminary

I don't think it's necessary to build a new array "fv". The objects returned by GetSelectedValues are already in the correct format for Selectvalues.

-Rob

set yearSelect = ActiveDocument.Fields("Year").GetSelectedValues
set qtrSelect = ActiveDocument.Fields("Quarter").GetSelectedValues
set dateSelect = ActiveDocument.Fields("Date").GetSelectedValues
ActiveDocument.ClearAll
ActiveDocument.Fields("Year").SelectValues yearSelect
ActiveDocument.Fields("Quarter").SelectValues qtrSelect
ActiveDocument.Fields("Date").SelectValues dateSelect



Highlighted
Creator III
Creator III


Rob Wunderlich wrote:
I don't think it's necessary to build a new array "fv". The objects returned by GetSelectedValues are already in the correct format for Selectvalues.
-Rob
<blockquote>set yearSelect = ActiveDocument.Fields("Year").GetSelectedValues
set qtrSelect = ActiveDocument.Fields("Quarter").GetSelectedValues
set dateSelect = ActiveDocument.Fields("Date").GetSelectedValues

ActiveDocument.ClearAll
ActiveDocument.Fields("Year").SelectValues yearSelect
ActiveDocument.Fields("Quarter").SelectValues qtrSelect
ActiveDocument.Fields("Date").SelectValues dateSelect<pre>



Rob My issue is I cant get another Sub Routine to call up those "Selections" once I get back to a sheet I want to apply them too. Thus I created a macro that makes a bookmark for said data and than that Macro is applied on open sheets where I needed it

Highlighted
MVP & Luminary
MVP & Luminary

I was assuming, based on the previous post, that the sequence of save/clear/restore all happened in one Sub.

However, if the save and restore occur in different subs, you can declare the *Select objects as global variables (outside a Sub) and then they can be referenced by either Sub.

If your bookmark solution is working, that sounds like a good solution also.

-Rob

Highlighted
Champion III
Champion III

James,
It would be so much easier to help you if you described your problem from the very beginningBig Smile
Yes, bookmark is not a bad solution. I just want to mention that I had to resolve a simuilar problem once, and couldn't use bookmark. So, I created a set of hidden "copy fields" for Year, Month, and Date as a logical island. One macro copied the selections from the "real" fields to these hidden fields, and another macro copied selections from hidden to "real".

Highlighted
Contributor III
Contributor III

this can be annoying, i wrote a code that will only clear the fields that are selected, you can check this qvw, this also allows you to just list those fields in an island instead of repeating code.