Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
james
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
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
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

james
Creator III
Creator III
Author

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









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

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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



james
Creator III
Creator III
Author


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

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Anonymous
Not applicable

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".

yakir_manor
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.