topic Hidden sheets - Macro in QlikView

Hidden sheets - Macro

Fri, 05 Feb 2010 14:11:45 GMT

Hi guys,

I would like to know how can I walk thru hidden sheets using a macro.

Let me explain better:

I have a template QVW that is used for all of my customers. (we are not using server yet)

Depending on what services my customer buys, I would like to hide some sheets.
For this, I've created the following macro:

Sub HideSheets

ActiveDocument.ClearAll true

'Showing all sheets again
for i = 0 to ActiveDocument.NoOfSheets - 1

ActiveDocument.Sheets(ActiveDocument.Sheets(i).GetProperties.Name).Activate

set mysheet = ActiveDocument.Sheets(ActiveDocument.Sheets(i).GetProperties.Name)
set sp=mysheet.GetProperties
sp.Show.Always = true
sp.Show.Expression.v = "1=1"
mysheet.SetProperties sp

next 'i

'Hiding sheets

ActiveDocument.ClearAll true

set m = ActiveDocument.Variables("v_Servico")

ActiveDocument.Fields("Modulo").Select m.GetContent.String
ActiveDocument.Fields("Modulo").Lock
ActiveDocument.Fields("AbaVisivel").Select 0

set mySelections = ActiveDocument.Fields("IdAba").GetPossibleValues

for j = 0 to mySelections.Count - 1

ActiveDocument.Sheets(mySelections.Item(j).text).Activate

set mysheet = ActiveDocument.Sheets(mySelections.Item(j).text)
set sp=mysheet.GetProperties
sp.Show.Always = false
sp.Show.Expression.v = "1=2"
mysheet.SetProperties sp

next

ActiveDocument.Fields("Modulo").UnLock

ActiveDocument.ClearAll true

End Sub

As you can see, the first thing is to show all sheets again. But if there is any hidden sheet, it is not used in the loop "for i = 0 to ActiveDocument.NoOfSheets - 1" because it works with visible sheets only.

Is there a way to loop on all sheets? Even if it's hidden?

Thanks

Hidden sheets - Macro

Fri, 05 Feb 2010 14:48:43 GMT

this maybe of use.


sub ShowOrHide
 if ActiveDocument.Variables("vShowOptions").GetContent.String <> "0" then
 ActiveDocument.Variables("vShowOptions").SetContent "0",true
 else
 ActiveDocument.Variables("vShowOptions").SetContent "1",true
 end if
end sub

Hidden sheets - Macro

Wed, 21 Apr 2010 10:00:17 GMT

I couldn't see how the other reply helped. Did you find a solution? I have exactly the same question for the same reason!

Hidden sheets - Macro

Wed, 21 Apr 2010 14:10:58 GMT

You could use a variable in you show conditions, like this: if(1=1 or showAll = 1, 1, 0)
Then you could set the variable showAll to 1 first in your macro to show all sheets before looping through them.

Not a perfect solution but it would work

/Fredrik

Hidden sheets - Macro

Wed, 21 Apr 2010 15:02:44 GMT

Thanks, I have set up individual variables based on the sheet names and am setting those up in a macro. I hadn't thought of adding a vShowAll variable 0 - that makes it a bit more elegant!

Getting there...

Re: Hidden sheets - Macro

michaelfreitas — Thu, 06 Oct 2011 19:38:34 GMT

I would like to share a shortcut, I learned on a topic here, to display all the sheets, whether they are hidden by a conditional.
ctrl + shift + s

thanks

Re: Hidden sheets - Macro

Sokkorn — Sat, 08 Oct 2011 04:05:46 GMT

Hi All,

I have created report that need to hide sheet for each Section Access. I'm not sure for this one help all of you or not. So let check my attached how to hide sheet base on user open file.

Regards,

Sokkorn

Re: Hidden sheets - Macro

michaelfreitas — Mon, 10 Oct 2011 14:37:02 GMT

Hello Sokkorn Cheav

Actually, what i wanted to be able to manipulate via macro sheets hidden and not hide them.

I found a temporary solution is to display all the sheets, handle all I need, and hide again. By using the shortcut described.
ctrl + shift + s

I am currently using these features through the QlikView developer, not the web. So no problem displaying> manipulate> hide. Only after this is that the application goes to the web.

The macro is just to sweep the application before being released.

I appreciate your willingness to post the help file.

Thank you and need something ...