VBA – Making your Excel application really cool

Posted on February 23, 2015 by

Some time ago I was working over an Excel file, making it look like a cool application. As far as I was getting paid for it, it had to look really outstanding 🙂

In VBA, there is a way to make your Excel file looks outstanding. The easy way is to block almost everything from view, in order to give the user the feeling that he is not in the standard Excel. Something like this:

Users are happy, that they do not see the status bar, grid lines, toolbar, scroll bars and the rest of the beauties from Excel. It really looks cool, eh? How it is achieved? Pretty much with two subs – one to deactivate all of the aforementioned at the start of the program and one to activate it back.

As simple as this 🙂 Here comes the code:

Option Explicit
Sub Outstanding()

    Application.ScreenUpdating = False
    Application.EnableEvents = False

    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"

    ActiveWindow.DisplayHeadings = False
    ActiveWindow.DisplayGridlines = False
    Application.DisplayFullScreen = True
    Application.DisplayStatusBar = False

    Application.WindowState = xlMaximized
    ActiveWindow.WindowState = xlMaximized

    Application.DisplayFormulaBar = False
    Application.CommandBars("Cell").Enabled = False

    With ActiveWindow
        .DisplayHorizontalScrollBar = True
        .DisplayVerticalScrollBar = False
    End With

    Application.ScreenUpdating = True
    Application.EnableEvents = True

End Sub

Sub BackToNormal()

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    ActiveWindow.View = xlNormalView
    ActiveWindow.DisplayHeadings = True
    ActiveWindow.DisplayGridlines = True
    Application.DisplayStatusBar = True
    Application.DisplayFullScreen = False
    Application.DisplayFormulaBar = True
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"

    Application.ScreenUpdating = True

    Application.CommandBars("Cell").Enabled = True

    With ActiveWindow
        .DisplayHorizontalScrollBar = True
        .DisplayVerticalScrollBar = True
    End With

    Application.ScreenUpdating = True
    Application.EnableEvents = True


End Sub

Option Explicit

Sub Outstanding()

Application.ScreenUpdating = False

Application.EnableEvents = False

Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"

ActiveWindow.DisplayHeadings = False

ActiveWindow.DisplayGridlines = False

Application.DisplayFullScreen = True

Application.DisplayStatusBar = False

Application.WindowState = xlMaximized

ActiveWindow.WindowState = xlMaximized

Application.DisplayFormulaBar = False

Application.CommandBars("Cell").Enabled = False

With ActiveWindow

.DisplayHorizontalScrollBar = True

.DisplayVerticalScrollBar = False

End With

Application.ScreenUpdating = True

Application.EnableEvents = True

End Sub

Sub BackToNormal()

Application.ScreenUpdating = False

Application.DisplayAlerts = False

ActiveWindow.View = xlNormalView

ActiveWindow.DisplayHeadings = True

ActiveWindow.DisplayGridlines = True

Application.DisplayStatusBar = True

Application.DisplayFullScreen = False

Application.DisplayFormulaBar = True

Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"

Application.ScreenUpdating = True

Application.CommandBars("Cell").Enabled = True

With ActiveWindow

.DisplayHorizontalScrollBar = True

.DisplayVerticalScrollBar = True

End With

Application.ScreenUpdating = True

Application.EnableEvents = True

End Sub

Enjoy it!