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.
Talk To Other Members