I am stuck in a scenario where I have multiple charts in a sheet, all dispayed conditionally on button clicks.
In one case, there are 20 charts , and on other there are only two.So, when I have only two charts displyed, then also, the size of the sheet remains equal to the one where 20 are displyed , as in, the scroll bar appears as per the max size of sheet occupied by the objects
I belive this can be handled using some active sheet properties in a macro.
Maybe you can avoid using macros and instead create aditional charts with the size you would like to have. Create a new variable for displaying these specific sheets with the new size, control it with button clicks like you are doing with the charts you already have but instead of trying to control the size with a macro, just make them invicible and display copies of these with the other size.
I would begin by asking what the actual business requirement is for this behaviour?
Maybe separate tabs or some other design would be more appropriate?
If it does need to be designed this way, nested Container objects may work. Use Containers in Grid mode to display the 2 or 20 charts. Put each of these containers in a parent container in Single Object mode to switch between different views.
A sheet itself does not have height and width properties rather it spans to fit all the objects in the sheet itself.
So, if you want to hide scrollbars in the sheet then one way can be as suggested by Michael Fenton to use container object, which was developed to show multiple objects in 1 sheet.
From version 11 you have grid view also possible which solves the problem of not being able to see multiple objects in one view.
To hide the scrollbars in sheet,you need to make width and height of all the objects within the limits of the sheet.
If you can use container then that will be easy for you because you need to change the width of only one object or else you will need to change width of all the objects in the sheets.
This can be done using macro somewhat similar to this
set obj = ActiveDocument.GetSheetObject( "CH01" )
pos = obj.GetRect
pos.Top = 1
pos.Left = 1
pos.height = 100
this you can call when you want to switch to lesses objects view, then one more macro can be written which can be called when you want to switch to more objects view, which will make all those objects back to their original layout (width, height, top etc)
But rather then doing all this, a simpler and better way will be to change the way you are showing objects.
Instead of showing 20 objects in 1 view, you can further divide them into subgroup and that way you can show 5 objects in one view.