How do I only open 1 sheet in a workbook

ghrek

Active Member
Joined
Jul 29, 2005
Messages
273
I have a workbook that has 5 sheets but I want it to open on sheet 1 only and hide others until enable macros button pushed. When pushed I want it to display all the sheets.

Any ideas?
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,682
Office Version
  1. 365
Platform
  1. Windows
Try adding this code to the 'ThisWorkBook' code module.
Save and close, then try to open it again.
VBA Code:
Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    ws.Visible = xlSheetVisible
Next
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "Sheet1" Then ws.Visible = xlSheetVeryHidden
Next
End Sub
 

ghrek

Active Member
Joined
Jul 29, 2005
Messages
273
Tried your code and it done it first time and I clicked on enable macros and it worked.

Put some data in and saved and closed the workbook and then reopened but it opened all pages at once.
It seems to hide all the other cells when I click on the close button and save the workbook but as stated when open again it opens all the sheets.

Any Ideas?
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,682
Office Version
  1. 365
Platform
  1. Windows
I thought I had replied to your thread, earlier, but just realised that I posted the reply in the wrong thread :oops:

I've just copied my earlier reply directly, based on the problem you're encountering, does this ring true or is there something else that I haven't noticed?

I was doing a bit more testing on the idea and did notice a cause for concern.

When you try to close the workbook, it will ask you to save.

If you choose not to save then the sheets will not be hidden as required.
If you choose to save then it will also save any previous changes that you may want to discard.
If you force save as part of the procedure then it will also save any changes that you may want to discard.

There will be ways around the problem, I'll wait for feedback from you as to what will be necessary before looking into it further.
 

ghrek

Active Member
Joined
Jul 29, 2005
Messages
273

ADVERTISEMENT

Sort of.

I think that I will need to set it up so when workbook closes I will need it to save automatically and not ask me if I want to save.

Looking into it further when I open the workbook yes I need it to open sheet 1 only and when I click on enable macros I need it to open the rest of the sheets, but now realise that I want to autorun a macro to delete data from certain cells on other sheets.

It dont matter if it deletes the data when you come to close the workbook if thats easier before it saves and closes automatically.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,682
Office Version
  1. 365
Platform
  1. Windows
See if this works for what you need. For testing purposes, I've added a 5 second delay into each procedure so that you can clearly see that the sheets are hidden immediately before closing and after opening. This shows that it is working if the 'enable macros' button does not show up, as was the problem I encountered due to the file being saved in an approved folder. Once you're satisfied that it is working as required these can be deleted.

Your additional code to delete specific cells could be called from either procedure. Personally, I would do it at the point of closing in order to keep the file clean.
VBA Code:
Private Sub Workbook_Open()
Dim ws As Worksheet
Application.Wait Now + TimeSerial(0, 0, 5)
For Each ws In ThisWorkbook.Worksheets
    ws.Visible = xlSheetVisible
Next
    ThisWorkbook.Save
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet
Application.EnableEvents = False
With ThisWorkbook
    For Each ws In .Worksheets
        If ws.Name <> "Sheet1" Then ws.Visible = xlSheetVeryHidden
    Next
    .Save
End With
    Application.EnableEvents = True
    Application.Wait Now + TimeSerial(0, 0, 5)
End Sub
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,912
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Cross posted.
@ghrek You should no better by now. Please supply the link(s)
 

Watch MrExcel Video

Forum statistics

Threads
1,130,201
Messages
5,640,816
Members
417,168
Latest member
StumpoC

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top