Solved

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

Posted on 2011-03-11
19
332 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:Maliki Hassani
  • 10
  • 6
  • 3
19 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35109699
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
 

Author Comment

by:Maliki Hassani
ID: 35109715
Great, let me give that a try
0
 

Author Comment

by:Maliki Hassani
ID: 35109740
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35109799
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
 

Author Comment

by:Maliki Hassani
ID: 35109802
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35109841
I don't know what would be causing that in your workbook.
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 35110265
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
 

Author Comment

by:Maliki Hassani
ID: 35110464
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
 

Author Comment

by:Maliki Hassani
ID: 35110482
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:Maliki Hassani
ID: 35110488
Been stuck on this for days..
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 35110705
password isn't!

Chris
0
 

Author Comment

by:Maliki Hassani
ID: 35110739
Are you saying that you can't get into the modules?  It should be lowercase  "usf"
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 35110784
yes and I copied the text in case ... no luck
0
 

Author Comment

by:Maliki Hassani
ID: 35110815
okay, try this
KIR-Dashboard-New.xlsm
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 35110900
Nope still not!


Chris
0
 

Author Comment

by:Maliki Hassani
ID: 35110950
the only other password that I had this before was "password"
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 35111135
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
 

Author Comment

by:Maliki Hassani
ID: 35111167
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
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 500 total points
ID: 35111192
It does take a long time to open which I have assumed is graphics or the like

Chris
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now