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.

 

[smozgur]

As I tried to explain, ReadView is not in a standard module but in Sheet1 class module. You cannot call it from Workbook_Open event procedure as you try to do above.

Since the hyperlink makes the call inside the sheet class module, it can access its own procedures, even Private ones. However you cannot call the same procedure from another class module (ThisWorkbook) because of two requirements.

I already explained why you should put this procedure into a standard module instead class module (Sheet1). So I'll skip this preferred way and assume you need to keep it in the class module.

To be able to access and call Sheet1 class module procedure from another class module ~ it is ThisWorkbook's event procedure - Workbook_Open in your sample:
First requirement: the procedure must be Public.
Reason: because Private procedures can be only called by the class object itself - which means inside that class module. That's why it works when you click on the hyperlinks. Worksheet_FollowHyperlink also belongs to Sheet1 class object and it can access the other procedures in the same class module.
Resolution: Change ReadingView procedure as a Public procedure (replace Private as Public or just remove Private since all procedures are Public if Private is not used.

Public Sub ReadingView()
'... codes here
End Sub

Second requirement: It must be called as the sub procedure of the owner class object - Sheet1 here.
Reason: Class object procedures cannot be called by using the procedure name only from another class module / module. This way all class objects can contain procedures with the same name which is not possible in standard modules in a VBA project.
Resolution: Call it from the Workbook_Open as shown below.

Private Sub Workbook_Open()
    Call Sheet1.ReadingView
End Sub

I removed the other lines that is not related with this issue. You can rewrite other necessary part.

Summary: Procedures in class modules are not accessible by the other objects / modules unless they are Public and they must be called as the owner object's procedure - i.e. Call Sheet1.ReadingView - not Call ReadingView only.

Hope this helps.

 

[Ark68]

Hi Suat, thank you for your patience. VBA is not a native language for me LOL, so sometimes I get tied up with the "official terminology". I think I'm getting lost in translation with respect to the different 'modules'.

I moved all 5 modules (see post #10) into what I assume as a "standard" module. ie I added a module in the modules folder of the project and put the code in that. I changed all the "Private Sub" to "Public Sub", but when I got to the second part of your advice to change my Worksheet_Open to code to

Call Sheet1.ReadingView

I realized my interpretation was wrong. I have no sheet to reference.

So with all the code is back into the worksheet module where it was originally, and changes made as suggested the behaviour is for the most part as expected! Yay!

A big thank you for helping me get over this hurdle! And of course, a special shout out and nod to Dan W for giving me the lead on this method!!

I am noticing though the hyper link I have left on my worksheet to revert back to normal view isn't 100%. It reveals the ribbon, but nothing else. It doesn't reveal the scrollbars, formula bars, tabs nor does it maximize (although I'm not sure I've instructed it to maximize).

' reset normal view
Public Sub NormalView()

With Application
Stop
With ActiveWorkbook
  .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

I wonder if it has anything to do with this declaration code in the sheet module.

Option Explicit

'================================================================================
'
' Author:                   Mark Kubiszyn
' Website:                  http://www.kubiszyn.co.uk
' Email, Comments / Bugs:   mark@kubiszyn.co.uk
'
' Clean Reading View
' 17.11.2012
'
'================================================================================

Private Const PreventEditModeRange = "J1,N1" ' ## adjust as required

' 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

 

[Dan_W]

Hi

Sorry, it's been a busy week at work, so I am only just reading this now.

Worksheet subroutines
When you say you moved all five routines into a standard module, are you saying you moved the Worksheet subroutines into the standard module too? I'm not sure that's going to work if all you did was copy/paste. Presumably, the code that calls the ReadingView and NormalView subroutines is in the worksheet subs?

Also, in your Workbook_Open code, you have Application.ScreenUpdating = False. Can I ask why? Is it turned back on at some point?

View and ViewSettings
Also, I think you referred to the Private Type Viewsettings as a routine - it's just a user-defined data type for your variables. Because the code I posted prefaces it with Private, this means that it needs to be in the same module (in the declaration section) as the two subroutines ReadingView and NormalView.

It's also important that you have the line for the View variable there too, but it isn't clear where you've put that line:

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

 

[Dan_W]

And now I've just seen the additional posts - I hadn't updated the page since I saw it last night.

Thank you, Suat, for your help.

The only thing I would say is in relation to this point in Ark68's last post:

"I am noticing though the hyper link I have left on my worksheet to revert back to normal view isn't 100%. It reveals the ribbon, but nothing else. It doesn't reveal the scrollbars, formula bars, tabs nor does it maximize (although I'm not sure I've instructed it to maximize). "

Essentially, what the code in ReadingView does is save all the settings of your ActiveWindow when you execute the sub (e.g., .DisplayGridlines, etc) and then sets them all to False. NormalView just puts ActiveWindow back to those saved settings. So it should just be the same as it was before you executed ReadingView. You don't even need to do that; you could just manually go through a set each setting to either True or False, as you prefer.

As for the Maximised window, that will be because the original author of the original code had this line in the ReadingView sub:

.windowstate = xlMaximized

Just delete that, and it should be fine.

 

[Ark68]

Thank you Dan, your support is greatly appreciated.

 

[Ark68]

If I may once again put up my hand for help!
Things worked (flawlessly?) once I got things all straightened away ... private to public, and ReadingView and NormalView in the worksheet module.
But as my application development progressed, I'm finding an issue.

I need to call the ReadingView sub from another workbook. The following code is in wb1, and the ReadingView sub is in workbook2 (wb2), worksheet("FORM").

Application.Run "permit_info.xlsm!ReadingView"

When called, I get "Cannot run the marco permit_info.xlsm!ReadingView'. The macro may not be available in this workbook or all macros may be disabled."

Could this be because the macro resides in a worksheet module? The "ReadingView" macro fails if I put it in a standard module.

 

[Ark68]

Disregard. I went with a totally different approach. Rather than trying to call the ReadingView macro from the non-hosting workbook, I just put that sub in wb1 and referenced the changes in that code to apply to wb2.

Seems to be working for now.