Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2011-03-11
19
Medium Priority
?
347 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 2000 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

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

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

Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

927 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