Upon running a macro in Excel that opens a UserForm, you are presented with the form, but all the toolbars, scrollbars, gridlines and other unsightly things are still visible in the background. This will show you how to hide and reset all those things, input a background image and generally make your project more presentable. Let's roll!

'Paste this code in the main module. Sub_Start() is the procedure that is run upon clicking the button in Excel, and displays the UserForm.

Option Explicit

Sub Start()
    ClearAll
    FormInterface.Show
    Unload FormInterface
End Sub

Sub ClearAll()
    Application.ScreenUpdating = False
    ClearWorkSheet
    ClearActiveWindow
    ClearApplicationControls
    Application.ScreenUpdating = True
End Sub

Sub ResetAll()
    Application.ScreenUpdating = False
    ResetWorkSheet
    ResetActiveWindow
    ResetApplicationControls
    Application.ScreenUpdating = True
End Sub

Sub ClearWorkSheet()
    With ActiveWindow
        .DisplayGridlines = False
        .DisplayHeadings = False
        .WindowState = xlMaximized
    ' xlMaximized or xlNormal
    End With

'If you want a background image behind your UserForm, put it in here. This one is just an example. Otherwise you will have a blank white background.
    ActiveSheet.SetBackgroundPicture ("C:\My Documents\My Pictures\Yosemite.jpg")
End Sub

Sub ResetWorkSheet()
    With ActiveWindow
        .DisplayGridlines = True
        .DisplayHeadings = True
        .WindowState = xlMaximized
' xlMaximized or xlNormal
    End With

    ActiveSheet.SetBackgroundPicture ("")
End Sub

Sub ClearActiveWindow()
    With ActiveWindow
        .DisplayHorizontalScrollBar = False
        .DisplayVerticalScrollBar = False
        .DisplayWorkbookTabs = False
    End With


    ' Application.DisplayScrollBars = False
    ' Turns scrollbars off for all workbooks
End Sub

Sub ResetActiveWindow()
' Resets the scrollbars for all workbooks
    With ActiveWindow
        .DisplayHorizontalScrollBar = True
        .DisplayVerticalScrollBar = True
        .DisplayWorkbookTabs = True
    End With
End Sub

Sub ClearApplicationControls()
Dim OneBar As CommandBar

    ' First the normal screen
    With Application
        .DisplayFullScreen = False
        .DisplayFormulaBar = False
        .DisplayStatusBar = False
    End With

    ' Hide all Command Bars
    On Error Resume Next
        For Each OneBar In CommandBars
            OneBar.Visible = False
        Next
    On Error GoTo 0

    ' Now viewing full screen
    With Application
        .DisplayFullScreen = True
        .DisplayFormulaBar = False
        .DisplayStatusBar = False
    End With

    ' Hide all Command Bars
    On Error Resume Next
        For Each OneBar In CommandBars
            OneBar.Visible = False
        Next
    On Error GoTo 0

    ' Disable the Menu Bar only required once
    CommandBars("Worksheet Menu Bar").Enabled = False
End Sub

Sub ResetApplicationControls()
    ' First viewing full screen
    With Application
        .DisplayFullScreen = True
        .DisplayFormulaBar = True
        .DisplayStatusBar = True
    End With

    ' Turn on main CommandBars
    CommandBars("Standard").Visible = True
    CommandBars("Formatting").Visible = True

    ' Now the normal screen
    With Application
        .DisplayFullScreen = False
        .DisplayFormulaBar = True
        .DisplayStatusBar = True
    End With

    ' Turn on main CommandBars
    CommandBars("Standard").Visible = True
    CommandBars("Formatting").Visible = True

    ' Re-enable the Menu Bar
    CommandBars("Worksheet Menu Bar").Enabled = True
End Sub

Okay, you'll notice that only ClearAll is run. To reset Excel, paste this code in the button that exits your UserForm and returns you to Excel (Or exits the UserForm and saves then exits Excel.)

Uncomment the ThisWorkbook.Save and Application.Quit if you want Excel to save and close down upon exiting the UserForm, also paste the code below ** to open the UserForm while Excel opens. This way though, the user cannot access the workbooks. You may wish to have a password form that takes you to the workbooks, so only the user who knows the password can access the workbook. Leave them commented for testing.

Private Sub BtnExit_Click()
' Runs procedures that reset Excel toolbars and exit the interface.
' Also saves the workbook and exits Excel completely.
     Me.Hide
     ResetAll
'ThisWorkbook.Save
     'Application.Quit
End Sub

**Paste this code in ThisWorkbook in the Project Window. It will open the UserForm when Excel opens. Read the note above to find out more.

Option Explicit

Private Sub Workbook_Open()
    Start
End Sub

Now we need to disable the QueryClose button (the little X in the top right corner of the window) so that the user can't close the form. This is vital to do, because only upon clicking the Exit button is Excel reset with all it's formula bars, toolbars and whatnot. It won't remove the button, but will disable it.

In the UserForm with that Exit button, paste this code...

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If Cancel <> 1 Then
        Cancel = 1
    End If
End Sub

Okay, remember I said about a password form? Dead simple. Have an extra button by the Exit button, let's call it Close. This button displays another UserForm with a textbox (let's call it TextWord) and 2 buttons. One button is a cancel button (obvious), and the other is the Enter button. Upon clicking Enter button, it checks if the word entered in TextWord is correct, if it is, you go to the workbook. If not, you get some sort of boolean error message.

Private Sub TextWord_Change()
     PasswordChar = "*"
End Sub

Private Sub ButtonEnter_Click()
     If TextWord = "YOURPASSWORD" Then
          Me.Hide
          ResetAll
     Else MsgBox "Incorrect password", vbInformation, "Error"
     End If
End Sub

Private Sub ButtonCancel_Click()
     Me.Hide
     UserForm.Show
End Sub

'UserForm.Show is the name of the Userform that you will return to if Cancel is pressed.
'YOURPASSWORD is whatever password you want. Capitalisation to be remembered.
'PasswordChar = "*" sets whatever text is entered into TextWord into *'s.
'It's important to have ResetAll if the password is correct, now you can go to Excel and play around with the worksheet, which is now resetted.

Well, hope that helps any of you. I'm not entirely sure what "Option Explicit" is, but I was always taught to put it at the top of every bit of code. Apparantley it "forces explicit declaration of variables" whatever that means.

-------------------------------<EOF>-------------------------------

Back to VBA Visual Basic for Applications (Microsoft) FAQ Index
Back to VBA Visual Basic for Applications (Microsoft) Forum

My Archive

My FAQ Archive

Come Join Us!

Posting Guidelines

Jobs

VBA Visual Basic for Applications (Microsoft) FAQ

Presentation

My Archive

Join Tek-Tips® Today!

Join Tek-Tips^® Today!