Alternative to "Decluttering" a Worksheet Without Using an Application Wide Application

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
4,564
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hello,
I use this common code to "de-clutter" one of my worksheets that I use exclusively as a form. Doing this limits the amount that the user can do.
Anyway, it's written to minimize the entire workbook on the worksheet hosting the form. This was OK when the form was in a separate workbook independent of the workbook calling the workbook (with the form) to open.

I have changed things now and the "form" worksheet is part of the workbook that opens it. But this code applies to the application, so it is applied to the entire workbook which is inappropriate when really I only need the worksheet acting as a form to be dumbed down.

I assume that as an application function, I am unable to just limit the effects of this code to an individual worksheet. Is there a way I can get the same results without having two separate workbooks, one with the code applied (with the form) and the other as is, normal?

VBA Code:
Sub ReadingView()
'Stop
    Dim maxWidth As Integer
    Dim maxHeight As Integer
    Dim appLeft As Integer
    Dim appTop As Integer

    With Application
        .ScreenUpdating = False
        With wb_permit
            Application.windowstate = xlMaximized
            maxWidth = Application.Width
            maxHeight = Application.Height
    ' store current ActiveWorkbook settings
            View.drawingobjects = .DisplayDrawingObjects
    ' set
            wb_permit.Windows(1).Visible = True   '<--- THE WORKBOOK HOSTING THE WORKSHEET WITH THE FORM WHILE ALSO HOLDING THE PROCESS TO CALL UP THE "FORM"
            ws_form.Activate    '<--- THE WORKSHEET ACTING AS A FORM
        End With
    
        With ActiveWindow
    ' store current ActiveWindow settings
            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
        
    ' Call CenterApp(maxWidth, maxHeight)
            appLeft = maxWidth / 2
            appTop = maxHeight / 2
            .windowstate = xlNormal
            .Width = 480
            .Height = 250
            .Top = appTop
            .Left = appLeft
            .Width = 470
         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
    Application.ScreenUpdating = True
End Sub
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Forum statistics

Threads
1,221,692
Messages
6,161,336
Members
451,697
Latest member
pedroDH

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top