PDA

View Full Version : Solved: Hide worksheets file close



SDave
12-16-2009, 12:40 PM
Evening all,

The attached workbook is designed for multiple users who have to enter a username and specific password to access their assigned worksheet(s). The problem I'm currently faced with is that once the file has been saved and closed, those worksheets that have been unloaded are still visible.

What I am attempting to do is hide all visible worksheets bar one (ImportantInformation) on file close. Unfortunately for some reason I can't quite get the command to function whatsoever.

I've tried multiple variations using Workbook Before Close, to no avail. Any help would be much appreciated.

FYI the usernames and passwords for each worksheet can be found in L2:N10 on the ImportantInformation worksheet.

Thanks in advance.

Simon Lloyd
12-16-2009, 12:57 PM
Check the kbase (http://www.vbaexpress.com/kb) there's a brilliant example there!

mbarron
12-16-2009, 01:19 PM
Change you Workbook_BeforeClose event to:

Sub Workbook_BeforeClose(Cancel As Boolean)

ThisWorkbook.Unprotect Password:="Password"
Dim wSht As Worksheet
Application.ScreenUpdating = False
For Each wSht In Worksheets
If wSht.Name <> "ImportantInformation" Then
wSht.Visible = xlVeryHidden

End If
Next
Application.ScreenUpdating = True
ThisWorkbook.Protect Password:="Password"

End Sub

SDave
12-21-2009, 04:21 AM
Simon, thanks for the heads up.

mbarron, thank you, the modified code you supplied worked a treat - much appreciated.