Flexibility With Static Features of An Excel Worksheet

[Ark68]

This will likely be an odd question. Many if not most will just simply shake their head and say "why?"

I wish to open up a second workbook, resize it, and display it over an already maximized worksheet from a primary workbook. I don't want the user to have any control of the superimposed window, so no menus, ribbons, tabs, headings or formula bar. I imagine I can code the hiding of the formula bar and headings as they are options in the view menu.

But I am looking for help (or notice that my expectations are unachievable) to "hide" the tabs, ribbons, menus and the contents of the window header banner (which holds the Quick Access Toolbar, close, minimize buttons etc). These "hidden" components would not be global, but applied only to the second opened workbook. Also, while the superimposd worksheet is visible, the user shouldn't have access to the primary worksheet behind it.

The reason I wish to do this? I am rebuilding a previous Excel application that relied heaviliy on forms. The complexity of using them in my application made them very inefficient and unnecessarily complicated. I'd like to see if I can recreate user form functionality with just clever use of a worksheet. But I can only allow the user access to the controls on the worksheet. Having access to features of Excel available though tabs, menus etc will be detrimental to the function of the application.

 

[Dan_W]

I stumbled across these routines a while ago. I have put the author's details below in the code. As with all things, just when you think that there is no way to accomplish something in Excel, ExecuteExcel4Macro comes to the rescue!

I suspect that the following two routines will manage to accomplish what you need. I suggest putting the code in a new module and setting wb to the workbook you want.
Also, the original version of the code maximised to full screen. I have commented out that part. If you want to manage the size of the window, just use ActiveWindow.Height = XXX (and so forth).

Hope that helps.

'=======================================
' Website:    http://www.kubiszyn.co.uk
' Clean Reading View
' 17.11.2012
'=======================================

' ViewSettings Type Def
Private Type ViewSettings
  headings As Boolean
  gridlines As Boolean
  vscrollbar As Boolean
  hscrollbar As Boolean
  wkbtabs As Boolean
  windowstate As Long
  formulabar As Boolean
  statusbar As Boolean
  drawingobjects As Long
End Type

' declare an instance of the ViewSettings Object
Private View As ViewSettings

Sub ReadingView()
Dim wb As Workbook
Set wb = Application.Workbooks.add   ' change this part to set wb to be the preferred workbook

With Application

With wb
  View.drawingobjects = .DisplayDrawingObjects
  .DisplayDrawingObjects = xlHide
End With
  
With ActiveWindow
  View.headings = .DisplayHeadings
  View.gridlines = .DisplayGridlines
  View.hscrollbar = .DisplayHorizontalScrollBar
  View.vscrollbar = .DisplayVerticalScrollBar
  View.wkbtabs = .DisplayWorkbookTabs
  View.windowstate = .windowstate
  ' set
  .DisplayHeadings = False
  .DisplayHorizontalScrollBar = False
  .DisplayVerticalScrollBar = False
  .DisplayWorkbookTabs = False
  .DisplayGridlines = False
' I've commented out this next line, because you said
' you wanted to be able to control the height and width of the window.
' .windowstate = xlMaximized
End With

' set
.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"

' store current Application Settings
View.formulabar = .DisplayFormulaBar
View.statusbar = .DisplayStatusBar
'set
.DisplayFormulaBar = False
.DisplayStatusBar = False

End With
 
End Sub

And this will return you to normal.

Sub NormalView()
With Application
With .wb
  .DisplayDrawingObjects = View.drawingobjects
End With
 
  With ActiveWindow
   .DisplayHeadings = View.headings
   .DisplayHorizontalScrollBar = View.hscrollbar
   .DisplayVerticalScrollBar = View.vscrollbar
   .DisplayWorkbookTabs = View.wkbtabs
   .DisplayGridlines = View.gridlines
   .windowstate = View.windowstate
  End With
 
.ExecuteExcel4Macro _
  "SHOW.TOOLBAR(""Ribbon"",True)"
 
.DisplayFormulaBar = View.formulabar
.DisplayStatusBar = View.statusbar

End With

End Sub

 

[Ark68]

Hi Dan, this looks very interesting. I'm excited!
Question though. Does this go in a worksheet or regular module, or in the workbook code?

 

[Dan_W]

Hi Dan, this looks very interesting. I'm excited!
Question though. Does this go in a worksheet or regular module, or in the workbook code?

From memory, it can go into all of the above. I believe the original code had it in the worksheet, but I could be wrong. I just noticed some code in the snippets above that shouldn't be there:

Sub ReadingView()
Dim wb As Workbook
Set wb = Application.Activeworkbook ' change this part to set wb to be the preferred workbook

It should look like the screen capture below. I selected a bunch of cells to demonstrate that it functions like a spreadsheet:

 

[Ark68]

Hi Dan,
I'm at this point in my project now where I want to apply thgese changes. But, I'm finding it challenging on how to get it to work.

All the code you provided I put in my primary worksheet module.
I've made some changes (in blue) to the ReadingView sub.

Sub ReadingView()
'Dim wb As Workbook
'Set wb_permit = Workbooks("permit_info.xlsm")   'I've commented these lines out as this workbook is declared in a module ('declarations') that executes with {workbook open} that declares and sets all my public variables.

With Application

With wb_permit
  View.drawingobjects = .DisplayDrawingObjects
  .DisplayDrawingObjects = xlHide
End With
  
With ActiveWindow
  View.headings = .DisplayHeadings
  View.gridlines = .DisplayGridlines
  View.hscrollbar = .DisplayHorizontalScrollBar
  View.vscrollbar = .DisplayVerticalScrollBar
  View.wkbtabs = .DisplayWorkbookTabs
  View.windowstate = .windowstate
  ' set
  .DisplayHeadings = False
  .DisplayHorizontalScrollBar = False
  .DisplayVerticalScrollBar = False
  .DisplayWorkbookTabs = False
  .DisplayGridlines = False
' I've commented out this next line, because you said
' you wanted to be able to control the height and width of the window.
' .windowstate = xlMaximized
  .windowstate = xlNormal
  .Width = 2292    'window width in pixels
  .Height = 845     'window height in pixels
End With

I assume to have all these things done, I have to call the ReadingView sub from somewhere. I aalso assumed this should be doen from the {workbook open} module so that the window resizes on opening.

Private Sub Workbook_Open()
    Application.ScreenUpdating = False
    declaration 'the workbook is declared and set in here
    Stop
    ReadingView
    recall = 0
    start_2
End Sub

I am recieving a "Sub or Function not defined error" with the line in red.

Anyone able to offer me a solution?

 

[Ark68]

Bump

 

[smozgur]

All the code you provided I put in my primary worksheet module.

Worksheet module is a "Class Module" and it is not like standard modules which are public to other objects and modules. You can't access class module sub procedures (ReadingView in this sample) without calling them with the actual class object (like Sheet1.ReadingView in this sample). That's why we generally use Module for placing our methods / sub procedures instead writing them in the class modules like worksheet and workbook modules.

Yes, you can still use class modules for this purpose but you'll have to refer the hosting object (sheet here) and it is not a good practice for public code. Basically, if you have to, only write the methods in the class modules that is only related with that class and nothing to do with the other objects and never to be called by other objects. Other than that, keep using standard modules to store your sub procedures.

To solve this proble,: Just insert a new module (VBE->Insert->Module) and move ReadingView sub procedure into this new module. There might be other problems in the code that I didn't look for but this particular problem will go away as the initial call will be able to access the subroutine.

 

[Ark68]

Thank you Suat! I got this code working wonderfully the way it was intended ... with hyperlinks. But, that defeats the purpose a bit. I would prefer it automatically resize and have all the features disabled when the workbook is opened. I'll work with your suggestion as see where it goes.

 

[smozgur]

I would prefer it automatically resize and have all the features disabled when the workbook is opened

You will still call the sub procedure (ReadingView) in Workbook_Open. There is nothing to change there.

Just move the ReadingView procedure into a new standard module.

 

[Ark68]

Suat, hello.
So ... I moved all 5 routines (parts of the whole package)

• private type viewsettings,
• Sub worksheet_FollowHyperlink, (needed for the use of hyperlinks which are used to select they view type)
• Sub Worksheet_BeforeDoubleClick, (not sure what this does)
• Sub ReadingView and (the preferred view I am trying to achieve)
• Sub NormalView (back to normal)

into a standard module.

My worksheet open code:

Private Sub Workbook_Open()
    Application.ScreenUpdating = False
    declaration
    Call ReadingView
    recall = 0
    start_2
End Sub

Still getting a "Sub or Function not defined error." with the line in red.