Macro to drill down to another sheet

[#1]

I have a simple scorecard, based on a straight table, which shows 3 KPI's records (name, target, realisation, indication). If you want to investigate the details of one of these KPI's you have to click on another sheet and then you get the requested info. I want to replace the clicking on the sheet by clicking on the kpi line in the straight table. Using a button (with functionality "shortcut" and "previous/next" sheet) is a simple way to solve the problem. But, what if i need analyse data which is not stored in next or previous sheet, but f.e. 3 sheets further??? Read some of the topics concerning this problem and according to most members, a macro should do the job. Can anyone help me with creating such macro??

[sparur]

Hi, emveha86

I made a macro that has activated the page for its SheetID

sub ShowSheetMargin

 set var = ActiveDocument.Variables("vDetailSheet")

 var.SetContent "MARGIN", true

 ActiveDocument.Sheets("MARGIN").Activate

 call SetTabrowColor

End Sub

here string "MARGIN" it's SheetID..

In your case enough only one string: ActiveDocument.Sheets("MARGIN").Activate

[#3]

Hi Sparur,

thnx for your macro! Used the string as mentioned: ActiveDocument.Sheets("MARGIN").Activate. And instead "MARGIN" I used the sheetid; SH02. But the macro doesn t work..... In the debug mode i get a message "Failed to get Disp ID of function". In test mode the macro works, but after closing the macro it fails .... Any Idea what goes wrong?

[#4]

emveha86, I don't understand what your current problem is. The solution provided by sparur should do the trick. Just create a button and link it to the following macro sub routine:

Sub Open_SH02
 ActiveDocument.Sheets("SH02").Activate
End sub

regards Mark

[#5]

Hi Mark,

thnx for your reply, it works!!!

Great!!!

Maarten

[#6]

hi

how to change the size of sheet i.e to work as view / zoom i.e size of sheet 10% ,20% ,......100%

by using macro  handle the zoom of sheet.

use button ,listbox add 10%,20%,,,,100% field when you select 20% then the size of sheet will be 20%

[Anonymous]

Taken from API Reference qvw file:

set mysheet=ActiveDocument.ActiveSheet

set sp=mysheet.GetProperties

sp.ZoomFactor = 0.9

mysheet.SetProperties sp

Jonathan