Hide and show Excel worksheets in PowerPivot Gallery

[kingtoni]

I'm new to working with PowerPivot Gallery and I didn't know that you can't use VBA macros or Shapes in your workbook. I have published my Excel workbook in PowerPivot Gallery. My workboos contains shapes and VBA macros.

1) I use shapes to add text and numbers to them. Shapes work as links to other worksheets in my workbook. This one with the shapes is so called HOME-worksheet for my report. How have you guys replaced shapes in your workbooks? I was thinking to add text to cell but that is not pretty.

2) I only want to show one worksheet at the time and hide others. I have 12 worksheets on my workbook. I have used macro for this in my workbook but you can't use macros when you have uploaded your workbook to PowerPivot Gallery. Is there a way to hide worksheets and only show the active one? I can use links instead of the macro but then I have to show all the worksheets.

3) I'd like to add HOME-button and BACK-button to all my worksheets. HOME-button is basic hyperlink to HOME-sheet but how to handle with BACK-button. I know how to do this with VBA but... you just want to go back to the worksheet where you came from.


Any comments are wellcome!


I use:
SharePoint 2013
Excel 2013

 

[Matt Allington]

You can load gif files and tiff files for shapes.
You could hide the sheet tabs to stop people moving randomly in a workbook, and hyperlink to the gif and tiff shapes. Hyperlinks work fine.

 

[kingtoni]

You can load gif files and tiff files for shapes.

Matt, thx!

I changed all my shapes to gifs and that's working. Now I just need to add my text to those pictures and that's a problem. I cannot place text to those gifs or added gif behind the text in cell. I think that I'll make a background picture with all the shapes and add those texts in cells in front of the shapes. Then I'll add buttons for the navigation options.

You could hide the sheet tabs to stop people moving randomly in a workbook, and hyperlink to the gif and tiff shapes. Hyperlinks work fine.

I just want to show one worksheet at the time, others are hidden. Does the hyperlink make my worksheet unhidden?

 

[Matt Allington]

Well if you hide the tabs in your workbook, then technically the other sheets are not hidden. But if the user can't access them - it is the same outcome right? So hide the tabs in your workbook before uploading and then use Hyperlinks to jump between them.

Now personally I don't like the Power Pivot Gallery and I prefer to use Excel Web Parts. I am planning on sharing my PASS BA Con 2015 presentation on this topic on my YouTube Channel but I haven't got to it yet. It is loaded an you can watch it here if you are interested: https://www.youtube.com/watch?v=2yjWBaixy2E

 

[kingtoni]

Well if you hide the tabs in your workbook, then technically the other sheets are not hidden. But if the user can't access them - it is the same outcome right? So hide the tabs in your workbook before uploading and then use Hyperlinks to jump between them.

Didn't know that this was even possible. Thank you for the tip! I'm going to use this one... Is it possible to protect this workbook that users can't make them visible again?

Now personally I don't like the Power Pivot Gallery and I prefer to use Excel Web Parts. I am planning on sharing my PASS BA Con 2015 presentation on this topic on my YouTube Channel but I haven't got to it yet. It is loaded an you can watch it here if you are interested: https://www.youtube.com/watch?v=2yjWBaixy2E

To be honest... this is my first PowerPivot Gallery project. I have used Office 365 before and used Power BI site to publish my reports. Excel Web Parts is new to me and I'm going to watch you video. Thank you!

 

[Matt Allington]

i have an opinionated view. I think that if you provide a seamless experience where users can browse and get what they want, they won't even think about trying to unhide tabs etc. so don't sweat it. But if security is an issue, then the webpart approach can solve that.

 

[kingtoni]

Matt, that web part looks amazing! I have no idea why we are using pp gallery Can you add shapes to your web part?

 

[kingtoni]

Well... your video explained a lot. Thank you Matt!

 

[Matt Allington]

Your welcome. Good luck with it.