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: 
bobbydave
Creator III
Creator III

Macros activating sheet but hiding other sheet

One application has two buttons, that opens different tabs of another application.

I want to have the application open showing the Claims sheet but hide the other sheet (Premium) and vice versa for ButB

Any ideas?

Sub ButA

set App = ActiveDocument.GetApplication

set newdoc = App.OpenDoc ("c:\Users\......\Desktop\Department.qvw","","")

newdoc.Sheets("Claims").Activate

End Sub

Sub ButB

set App = ActiveDocument.GetApplication

set newdoc = App.OpenDoc ("c:\Users\........\Desktop\Department.qvw","","")

newdoc.Sheets("Premium").Activate

End Sub

1 Solution

Accepted Solutions
robert_mika
Master III
Master III

I woudl belive that this shoudl work but is not

Sub ButA

set App = ActiveDocument.GetApplication

set newdoc = App.OpenDoc ("c:\Users\mikro01\Desktop\Department.qvw","","")

newdoc.Sheets("Claims").Activate

set mysheet = newdoc.Sheets("Claims")

set sp = mysheet.GetProperties

sp.Show.Always = False

sp.Show.Expression.v = "num(today())>100000"

mysheet.SetProperties sp

newdoc.Sheets("Premium").Activate

End Sub

Sub ButB

set App = ActiveDocument.GetApplication

set newdoc = App.OpenDoc ("c:\Users\mikro01\Desktop\Department.qvw","","")

newdoc.Sheets("Premium").Activate

set mysheet = newdoc.Sheets("Premium")

set sp = mysheet.GetProperties

sp.Show.Always = False

sp.Show.Expression.v = "num(today())>100000"

mysheet.SetProperties sp

newdoc.Sheets("Claims").Activate

End Sub

Once the Claims sheet is hidden It can not be unhidden.

I reckon we need some kind of IF statement or check the status of the sheets but  so far I have not been able to find a way.

View solution in original post

6 Replies
marcus_sommer

Have a look on these example from te APIGuide.qvw. I think you could adjust it to your case:

set mysheet=ActiveDocument.ActiveSheet

set sp=mysheet.GetProperties

sp.Show.Always = false

sp.Show.Expression.v = "sum(Sales)>4000"

mysheet.SetProperties sp

- Marcus

robert_mika
Master III
Master III

I have been trying to work out on something but I can not get properties of the second file.

You can open it,Add something but can not use GetProperties of the documents.

Any thought on that?

marcus_sommer

In general you could access the object-properties from the other document if you refered to it per "newdoc". In this case is the Show.Always statement a bit confusing then it led to the assumption that simply a value of true or false would be enough - but you need to use a condition within the expression part. The following worked fine for me (the expression itself is only a dummy):

newdoc.Sheets("Intro").Activate

set mysheet = newdoc.Sheets("Intro")

set sp = mysheet.GetProperties

sp.Show.Always = false

sp.Show.Expression.v = "num(today())>100000"

mysheet.SetProperties sp

newdoc.Sheets("PrintObjects").Activate

- Marcus

bobbydave
Creator III
Creator III
Author

Is there a way even in the external appliation to just simply say

If 'Premiums' tab is open, hide the 'Claims' tab

If 'Claims' tab is open, hide the 'Premiums' tab.

I could be over complicating things by having the main application buttons do the work, when the external application could just hide the sheets (could a conditional input resolve the issue??)

robert_mika
Master III
Master III

I woudl belive that this shoudl work but is not

Sub ButA

set App = ActiveDocument.GetApplication

set newdoc = App.OpenDoc ("c:\Users\mikro01\Desktop\Department.qvw","","")

newdoc.Sheets("Claims").Activate

set mysheet = newdoc.Sheets("Claims")

set sp = mysheet.GetProperties

sp.Show.Always = False

sp.Show.Expression.v = "num(today())>100000"

mysheet.SetProperties sp

newdoc.Sheets("Premium").Activate

End Sub

Sub ButB

set App = ActiveDocument.GetApplication

set newdoc = App.OpenDoc ("c:\Users\mikro01\Desktop\Department.qvw","","")

newdoc.Sheets("Premium").Activate

set mysheet = newdoc.Sheets("Premium")

set sp = mysheet.GetProperties

sp.Show.Always = False

sp.Show.Expression.v = "num(today())>100000"

mysheet.SetProperties sp

newdoc.Sheets("Claims").Activate

End Sub

Once the Claims sheet is hidden It can not be unhidden.

I reckon we need some kind of IF statement or check the status of the sheets but  so far I have not been able to find a way.

marcus_sommer

There isn't a direct way to check if a sheet is visible or not - but you could check if the visibility-condition is true or false. Those conditions must not necessary be set externally - they could query a variable-value, a certain selection or the osuser() and maybe something more and/or combined several from them.

But by:

bobbydave schrieb:

Is there a way even in the external appliation to just simply say

If 'Premiums' tab is open, hide the 'Claims' tab

If 'Claims' tab is open, hide the 'Premiums' tab.

I could be over complicating things by having the main application buttons do the work, when the external application could just hide the sheets (could a conditional input resolve the issue??)

I miss some proper logic. Whereas your approach with two buttons with my suggest code-snippets would work. I assume you want apply similar logics to more then one application and some are to use (now or in the future) with the AJAX client so that another possibilities like Multiple selects in URL AJAX should be considered.

- Marcus