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!
[color green]'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.[/color]
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
[color green]'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.[/color]
ActiveSheet.SetBackgroundPicture ("C:\My Documents\My Pictures\Yosemite.jpg")
End Sub
Sub ResetWorkSheet()
With ActiveWindow
.DisplayGridlines = True
.DisplayHeadings = True
.WindowState = xlMaximized
[color green]' xlMaximized or xlNormal[/color]
End With
ActiveSheet.SetBackgroundPicture ("")
End Sub
Sub ClearActiveWindow()
With ActiveWindow
.DisplayHorizontalScrollBar = False
.DisplayVerticalScrollBar = False
.DisplayWorkbookTabs = False
End With
[color green]
' Application.DisplayScrollBars = False
' Turns scrollbars off for all workbooks[/color]
End Sub
Sub ResetActiveWindow()
[color green]' Resets the scrollbars for all workbooks[/color]
With ActiveWindow
.DisplayHorizontalScrollBar = True
.DisplayVerticalScrollBar = True
.DisplayWorkbookTabs = True
End With
End Sub
Sub ClearApplicationControls()
Dim OneBar As CommandBar
[color green]
' First the normal screen[/color]
With Application
.DisplayFullScreen = False
.DisplayFormulaBar = False
.DisplayStatusBar = False
End With
[color green]
' Hide all Command Bars[/color]
On Error Resume Next
For Each OneBar In CommandBars
OneBar.Visible = False
Next
On Error GoTo 0
[color green]
' Now viewing full screen[/color]
With Application
.DisplayFullScreen = True
.DisplayFormulaBar = False
.DisplayStatusBar = False
End With
[color green]
' Hide all Command Bars[/color]
On Error Resume Next
For Each OneBar In CommandBars
OneBar.Visible = False
Next
On Error GoTo 0
[color green]
' Disable the Menu Bar only required once[/color]
CommandBars("Worksheet Menu Bar").Enabled = False
End Sub
Sub ResetApplicationControls()[color green]
' First viewing full screen[/color]
With Application
.DisplayFullScreen = True
.DisplayFormulaBar = True
.DisplayStatusBar = True
End With
[color green]
' Turn on main CommandBars[/color]
CommandBars("Standard").Visible = True
CommandBars("Formatting").Visible = True
[color green]
' Now the normal screen[/color]
With Application
.DisplayFullScreen = False
.DisplayFormulaBar = True
.DisplayStatusBar = True
End With
[color green]
' Turn on main CommandBars[/color]
CommandBars("Standard").Visible = True
CommandBars("Formatting").Visible = True
[color green]
' Re-enable the Menu Bar[/color]
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()[color green]
' Runs procedures that reset Excel toolbars and exit the interface.
' Also saves the workbook and exits Excel completely.[/color]
Me.Hide
ResetAll
[color green]'ThisWorkbook.Save
'Application.Quit[/color]
End Sub
[color red]
**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.[/color]
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
[color green]
'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.[/color]
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>-------------------------------
