Viewing Hidden Sheets with a macro

**Shazz** · 03-13-2012, 10:09 AM

Can anyone help,

I have a workbook that I have a a sheet that shows all my data called "Data Form", I have another sheet named "Dashboard" which I would like to have various button on that whn clicked will show the Sheet the button refers too.

In short, I want to have the Sheet "Data Form" permanently hidden unless a button "View Data Table" on the dashboard is clicked and then the "Data Form" appears, once you return to the dashboard "Data Form" remains hidden again.

Can anyone help me with this please??

Shazz
xx

**Richard Buttrey** · 03-13-2012, 10:17 AM

Hi,

In the click event of the 'View Data Table' button add the code

Sheets("Data Form").Visible = True

or better still in case someone should ever change the sheet tab name, use the 'Data Form' sheet code name that you see in the Visual Basic Environment. e.g.

Sheet1.Visible = True

In the Sheet Activate event of the 'Dashboard' sheet enter the code

Sheets("Data Form").Visible = False ' or Sheet1.Visible = False ...change Sheet1 to the actual code name

**Shazz** · 03-13-2012, 10:22 AM

Sorry to appear thick, but I can not find this, when i right click on the button it just give the option to assign a macro.

Could you explain exactly where this is??

Shazz
xx

**Richard Buttrey** · 03-13-2012, 10:31 AM

I implied from your use of the term' button' that you were referring to an Activex control button.

In that case add a macro at the module level. i.e.

Sub UnhideDataForm

   Sheets("Data Form").Visible = True

End Sub

**Shazz** · 03-13-2012, 10:37 AM

Maybe I am doing somethind wrong but I can not get it to work, can you explain step by step and tell me the best method to use.

Shazz
xx

**Richard Buttrey** · 03-13-2012, 10:49 AM

Hi,
1. Right click on any sheet tab (or ALt-F11) to be taken to the Visual Basic (macro) Environment (VBE)
2. Find your particular workbook name in the Project Explorer pane on the left.
3. Select any of the sheet objects or the 'This Workbook' object and from the VB menu pick Insert Module
4. Select the 'Module1' that appears in the Project Explorer pane and copy and paste the Sub UnhideDataForm() procedure I gave you in the previous post
5. Then back on the Excel sheet right click the button and attach the procedure mentioned in 4 above to the button.
6. Back to the VBE - Double click on the 'Dashboard' sheet object in the Project Explorer pane
7. In the left hand drop down on the right select the Worksheet Option
8. In the right hand drop down select the 'Activate' event
9. In the Activate event add the line of code I gave you, i.e. Sheets("Data Form").Visible = False

**Shazz** · 03-13-2012, 12:52 PM

I have managed to re hide it again once closed but can you advise how I can get the sheet to open up to view and not just open the tab at the bottom and then have to click it to view.

Thanks everso much.

Shazz

**Richard Buttrey** · 03-13-2012, 01:03 PM

....late edit. I think I understand what you're getting at.

Add

Sheets("Data Form").Activate

Hi,

I don't understand what you mean. If you can see a sheet tab then whatever is on the sheet is viewable. Are you sure you've scrolled to the top of the sheet and are not somewhere below the data you expect to see?

Which sheet are you referring to?

To understand further you'll need to upload your workbook.

Rgds

**Shazz** · 03-13-2012, 12:43 PM

Ok, I have got it to unhide but it just shows the tab on the bottom I would like it to actually open the sheet up to view, I also need it to hide again once finished?

Shazz

**Shazz** · 03-13-2012, 02:22 PM

I have added this but it still does not work, I have uploaded a Test database, could you have a look and see where I am going wrong.

I want to run the database in Full Screen Mode, that way you do not see the tabs at the bottom, when you click View Data Table I need it to show that worksheet as the active sheet, but it does not.

Shazz