faq707-1572
Posted: 4 Mar 02 (Edited 5 Mar 02)
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.
-------------------------------
