Excel VBA: Modification to make routine faster for hidiing open sheet

Experts.

I was wondering if an expert new of a way to modify my code below.  All it does is look for open sheets and closes them and leave the one I want open.  

The issue is that it doesn't need to look thru all the sheets to see if they are open.  Because there should only be one open at a time.  (thats the design)

Any ideas?
Sub OnlySheetVisible(mysheet As String)
Dim WS As Worksheet

Application.ScreenUpdating = False

    Sheets(mysheet).Visible = xlSheetVisible
    Sheets(mysheet).Activate
    For Each WS In Worksheets
        If WS.Name <> Sheets(mysheet).Name Then
           If WS.Visible <> xlSheetVeryHidden Then WS.Visible = xlSheetVeryHidden
        End If
           Next WS
Application.ScreenUpdating = False


End Sub

Open in new window

Maliki HassaniAsked:
Who is Participating?
 
Chris BottomleyConnect With a Mentor Commented:
It does take a long time to open which I have assumed is graphics or the like

Chris
0
 
Rory ArchibaldCommented:
If you know that always to be true, then you can exit the loop early:
Sub OnlySheetVisible(mysheet As String)
   Dim WS As Worksheet

   Application.ScreenUpdating = False

   Sheets(mysheet).Visible = xlSheetVisible
   Sheets(mysheet).Activate
   For Each WS In Worksheets
      If WS.Name <> Sheets(mysheet).Name Then
         If WS.Visible <> xlSheetVeryHidden Then
            WS.Visible = xlSheetVeryHidden
            Exit For
         End If
      End If
   Next WS
   Application.ScreenUpdating = False


End Sub

Open in new window

0
 
Maliki HassaniAuthor Commented:
Great, let me give that a try
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Maliki HassaniAuthor Commented:
no change..

Do you think the xlSheetVeryHidden is causing this to slow down?  What are other options being that it is already hidden and locked?
0
 
Rory ArchibaldCommented:
I don't see why it would be particularly slow, but if you know there will only be one sheet visible at any time, you can use:
Sub OnlySheetVisible(mysheet As String)
Dim WS As Worksheet

set ws = activesheet
    Sheets(mysheet).Visible = xlSheetVisible
    Sheets(mysheet).Activate
ws.visible = xlsheetveryhidden

End Sub

Open in new window

0
 
Maliki HassaniAuthor Commented:
I noticed that when I first open up the spreadsheet and click on the sheets, it is slow.  but when I go back and click on the ones that I just open they are no delays.  Is there a way around this?  Or just the way it has to load into memory first?
0
 
Rory ArchibaldCommented:
I don't know what would be causing that in your workbook.
0
 
Chris BottomleyCommented:
Can't say I see what would be the issue either though I think you want application.screenupdating to be true not false before exiting.

Can you supply the workbook at all?

Chris
0
 
Maliki HassaniAuthor Commented:
Sure, password is "usf"

You can run the "unhideall" macro which will unlockall sheets.  Click on the tab names up at the top will close all the sheets where the "Sub OnlySheetVisible" is fired.  module 1
KIR-Dashboard-New.xlsm
0
 
Maliki HassaniAuthor Commented:
I am trying find a way to overall speed up my time it takes to update the charts and clicking to get to a different tab.
0
 
Maliki HassaniAuthor Commented:
Been stuck on this for days..
0
 
Chris BottomleyCommented:
password isn't!

Chris
0
 
Maliki HassaniAuthor Commented:
Are you saying that you can't get into the modules?  It should be lowercase  "usf"
0
 
Chris BottomleyCommented:
yes and I copied the text in case ... no luck
0
 
Maliki HassaniAuthor Commented:
okay, try this
KIR-Dashboard-New.xlsm
0
 
Chris BottomleyCommented:
Nope still not!


Chris
0
 
Maliki HassaniAuthor Commented:
the only other password that I had this before was "password"
0
 
Chris BottomleyCommented:
Switched from Mac to PC and it opened ok!

Runs in an instant on my pc as it should so what do you have in the way of add ins.  Try deselecting them and see if one resolves the speed issue.  If when they are disabled , (keep a note of what is selected!) it is ok re-enable them in turn to see the culprit.

Chris
0
 
Maliki HassaniAuthor Commented:
Cool,  I will give that a try.  As for add ins, only a chart add in. Do you notice where it take a few seconds when it opens up the tabs? All you see is the lisboxes and then the background images shows up.  Is that normal?
0
All Courses

From novice to tech pro — start learning today.