Vba to control of visibility, position, and scroll bars for each of several worksheets in the same workbook

How, for several worksheets (ws) in one workbook, do I use vba to:
- make set each ws, independently, as either visible or hidden
- for each ws set its scroll bars (on or off)
- for each ws set the .top .width .left .height  values for each, within the same workbook window.

Thanks
Kelvin4Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:
Hi,

Do you need these attribute changes to occur when each worksheet is active (selected), or do you wish to set all the attributes at one point in time (prior to any of the worksheets being active; for example, whilst the workbook is opened, or following another event such as clicking a button intended to specifically change the attributes)?

Thanks for your clarification.

BFN,

fp.
0
Kelvin4Author Commented:
Thanks.
Top priority is - when the workbook is opened.

However, not far down the road, I'll need to show some Excel sheets (and hide others) in particular contexts.

So I'd be glad to take it step at a time with you now...  improving my understanding of the relevant bits of the object model and its control would be timely.

Kelvin
0
[ fanpages ]IT Services ConsultantCommented:
No problem.

Have you got an existing workbook you would like the code added to, & that you can post within a following comment, or would you just like to see an example working in a sample workbook?
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Kelvin4Author Commented:
My existing workbook drives a visio application (superior ribbonX in excel vs visio, plus added spread sheet support for creating lists of visio output drawings). So I'd rather ask for a sample file, and have a first shot at transferring the methods.

Thanks
0
Kelvin4Author Commented:
Hi FanPages,
Congratulations!!..   (I  read: "08 Sep 2013: Appointed Community Volunteer: Topic Advisor (Microsoft Excel)"). Your latest appointments contributes to a fantastic service, which I've enjoyed for ~ 2yrs.

Given your additional load, can you indicate your availability for this Q? Then I can schedule my coding around the 'block' I have on viewing Excel sheets. Somehow (and I regret I cant quite see how) I've lost sight of all except one excel sheet, and I seek the most effective methods to force the others to 'reveal themselves'.

If those methods fail, I'll have to migrate the whole excel component of the project into a fresh excel file to identify the killer code or whatever.

Cordially,

Kelvin
0
[ fanpages ]IT Services ConsultantCommented:
Hi Kelvin,

Thanks for your well wishes.

Your question is actually on my pile of "to look at" today.  That & three others.  I'm working on one of the other three now, but I can bump your question up the 'priority stack' thereafter.

I'll come back to you later today in any respect, but I do appreciate the reminder all the same :)

BFN,

fp.
0
[ fanpages ]IT Services ConsultantCommented:
Hi Kelvin,

Sorry for the delay.

I tried to use the site two days ago & could not gain access as pages were not loading at all.

Yesterday I tried again & found that the site's Cascading Style Sheet settings were not displaying properly & I could not click on any buttons.

Hopefully today means everything is back to normal.

Please review the workbook I have attached (& the Visual Basic for Applications code I have transposed below), & let me know if this meets your requirements (or you can work with it, & build upon it, for your own purposes).

' --------------------------------------------------------------------------------------------------------------
' [ http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28233792.html ]
'
' Question Channel: Experts Exchange > Software > Office / Productivity > Office Suites > MS Office > MS Excel
'
' ID:               28233792
' Question Title:   Vba to control of visibility, position, and scroll bars for each of several worksheets...
' Question Asker:   Kelvin4                                   [ http://www.experts-exchange.com/M_6064894.html ]
' Question Dated:   2013-09-08 at 13:51:24
'
' Expert Comment:   fanpages                                   [ http://www.experts-exchange.com/M_258171.html ]
' Copyright:        (c) 2013 Clearlogic Concepts (UK) Limited                           [ http://NigelLee.info ]
' --------------------------------------------------------------------------------------------------------------

Option Explicit
Private Sub Workbook_Open()

  Dim lngVisible                                        As Long
  Dim objWorksheet                                      As Worksheet
  
  On Error Resume Next
  
  For Each objWorksheet In ThisWorkbook.Worksheets
  
      lngVisible = objWorksheet.Visible
      
      Select Case (objWorksheet.Name)
      
          Case ("Sheet1")
              lngVisible = xlSheetVisible
          
          Case ("Sheet2")
              lngVisible = xlSheetHidden
          
          Case ("Sheet3"), _
               ("Sheet4"), _
               ("Sheet5")
              lngVisible = xlSheetVeryHidden
               
          Case ("Sheet6")
              lngVisible = xlSheetVisible
          
          Case Else
          
      End Select ' Select Case (objWorksheet.Name)
          
      objWorksheet.Visible = lngVisible
      
  Next objWorksheet
               
End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)

  Dim blnActiveWindow_DisplayGridlines                  As Boolean
  Dim blnActiveWindow_DisplayHeadings                   As Boolean
  Dim blnActiveWindow_DisplayHorizontalScrollBar        As Boolean
  Dim blnActiveWindow_DisplayVerticalScrollBar          As Boolean
  Dim blnActiveWindow_DisplayWorkbookTabs               As Boolean
  Dim dblActiveWindow_Height                            As Double
  Dim dblActiveWindow_Left                              As Double
  Dim dblActiveWindow_Top                               As Double
  Dim dblActiveWindow_Width                             As Double
  Dim lngActiveWindow_WindowState                       As Long
  
  On Error Resume Next
  
  blnActiveWindow_DisplayGridlines = ActiveWindow.DisplayGridlines
  blnActiveWindow_DisplayHeadings = ActiveWindow.DisplayHeadings
  blnActiveWindow_DisplayHorizontalScrollBar = ActiveWindow.DisplayHorizontalScrollBar
  blnActiveWindow_DisplayVerticalScrollBar = ActiveWindow.DisplayVerticalScrollBar
  blnActiveWindow_DisplayWorkbookTabs = ActiveWindow.DisplayWorkbookTabs

  dblActiveWindow_Height = ActiveWindow.Height
  dblActiveWindow_Left = ActiveWindow.Left
  dblActiveWindow_Top = ActiveWindow.Top
  dblActiveWindow_Width = ActiveWindow.Width
  
  lngActiveWindow_WindowState = ActiveWindow.WindowState
  
  Select Case (Sh.Name)
  
      Case ("Sheet1")
          blnActiveWindow_DisplayGridlines = False
          blnActiveWindow_DisplayHeadings = True
          blnActiveWindow_DisplayHorizontalScrollBar = True
          blnActiveWindow_DisplayVerticalScrollBar = True
          blnActiveWindow_DisplayWorkbookTabs = True
          
          lngActiveWindow_WindowState = xlMaximized
          
      Case ("Sheet2")
          blnActiveWindow_DisplayGridlines = True
          blnActiveWindow_DisplayHeadings = False
          blnActiveWindow_DisplayHorizontalScrollBar = False
          blnActiveWindow_DisplayVerticalScrollBar = False
          blnActiveWindow_DisplayWorkbookTabs = True
          
          dblActiveWindow_Height = 10#
          dblActiveWindow_Left = 100#
          dblActiveWindow_Top = -50#
          dblActiveWindow_Width = 600#
          
          lngActiveWindow_WindowState = xlNormal
          
      Case ("Sheet3"), _
           ("Sheet4"), _
           ("Sheet5")                       ' These worksheets will be Very Hidden so no changes are necessary
 
      Case ("Sheet6")
          blnActiveWindow_DisplayGridlines = False
          blnActiveWindow_DisplayHeadings = False
          blnActiveWindow_DisplayHorizontalScrollBar = True
          blnActiveWindow_DisplayVerticalScrollBar = True
          blnActiveWindow_DisplayWorkbookTabs = False
          
          lngActiveWindow_WindowState = xlMinimized
          
      Case Else
  
  End Select ' Select Case (Sh.Name)
  
  ActiveWindow.DisplayGridlines = blnActiveWindow_DisplayGridlines
  ActiveWindow.DisplayHeadings = blnActiveWindow_DisplayHeadings
  ActiveWindow.DisplayHorizontalScrollBar = blnActiveWindow_DisplayHorizontalScrollBar
  ActiveWindow.DisplayVerticalScrollBar = blnActiveWindow_DisplayVerticalScrollBar
  ActiveWindow.DisplayWorkbookTabs = blnActiveWindow_DisplayWorkbookTabs
  
  ActiveWindow.Top = dblActiveWindow_Top
  ActiveWindow.Height = dblActiveWindow_Height
  ActiveWindow.Left = dblActiveWindow_Left
  ActiveWindow.Width = dblActiveWindow_Width
  
  ActiveWindow.WindowState = lngActiveWindow_WindowState
  
End Sub

Open in new window


Also note that the workbook contains seven (7) worksheets.  However, once the Workbook_Open() event code has run you will only see three (3) of these.

Thanks.

BFN,

fp.
Q-28233792.xls
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kelvin4Author Commented:
Hi fanpages,
Many thanks for the attached, which I was unable to attend to yesterday.
I'll get right into it now, and report back quickly.

Appreciatively
Kelvin
0
[ fanpages ]IT Services ConsultantCommented:
No problem at all, Kelvin.

Thanks for letting me know.
0
Kelvin4Author Commented:
Hi fanpages,
Many thanks for a comprehensive solution.
One question:
-  When you write;  dblActiveWindow_Height = 50# , what does the '#' do?

Thanks again
Kelvin
0
[ fanpages ]IT Services ConsultantCommented:
Hi,

The # symbol (or "type declaration character") indicates a Double data type.

For example, declaring...

Dim dblActiveWindow_Height#

is the same as:

Dim dblActiveWindow_Height As Double


When used on the line you indicated, I am explicitly making the value of 50 a Double-precision floating-point number so that the execution of the code is quicker; in that if I just typed 50 (without the # suffix), the value would have to be converted to a Double data type by the Visual Basic for Applications code interpreter at run-time to 'load' into the variable (dblActiveWindow_Height).

I could have also typed:

dblActiveWindow_Height = 50.0

If you enter this, you will see it be automatically changed to what I did originally type:

dblActiveWindow_Height = 50#

In this single instance, this may not appear to be a great improvement but if, for example, that single line was within a loop (of hundreds, or thousands) of iterations, or there were multiple variables being set at once, &/or a If statement comparing a variable against a known value, then you may well notice a worthwhile speed improvement if the typed 50 (that would default to an Integer data type) had to be converted to a Double data type every time the respective line of code was executed (many times over in a loop, for example).

This is a practice I adopted from my very early usage of Microsoft Visual Basic (for Windows) back in 1991, when PCs had less Random Access Memory, compounded with the fact that the speed of the Central Processing Units were much slower than the processors we use today.

Finding "shortcuts" like this to improve the speed of execution was, in some cases, the difference between code execution completing in a reasonable time, or even completing at all (instead of seeing an "Out of Memory" error)!

So, in some respects, I do this because I have always done this, & it is not strictly necessary (now).  It does no harm other than, perhaps, confusing those unfamiliar with it.  You are not the first to query my code in this way.

If you read any of my other code listings (provided in other questions) you will see I also do this for other data types, for example:

Dim lngValue As Long
lngValue = 0&

Dim sngValue As Single
sngValue = 0!

Dim curValue As Currency
curValue = 0@

You may not see this approach (as often), as the default data type without a type declaration character as a suffix to an explicit value, is an Integer in any respect:

Dim intValue As Integer
intValue = 0%

is equivalent to

Dim intValue As Integer
intValue = 0

BFN,

fp.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.