Solved

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

Posted on 2011-03-11
19
337 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
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
 

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

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
My experience with Windows 10 over a one year period and suggestions for smooth operation
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

751 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