Remove content when saving / loading spreadsheet

[Guest]

In Excel 2003, I need to simulate a "login/logout" function within Excel
document, without requiring user intervention, preferably through
Login/password pop-up screen. Based on the login information (match of
user/password with predefined values within the workbook), content of
specific cells / sheets may / may not be displayed. One feature of this
login/logout function will be also to remove the content of specific cells
(where password was stored). User cannot be relied upon to 1) authorize
macros to be run and 2) to execute macro manually. Document will be used
internally so security needs are medium.

 

[Alan]

This can only be done using VB code, ie Macro's. It's easily possible to
force the user to enable Macro's on opening by making the file useless if
it's opened with Macro's disabled. This can be done by hiding all worksheets
except one with a title or warning message. Other hidden sheets can be
displayed (or not according to the entry level you chose) by unhiding them
with code if the correct password is entered.
You should note though that Excel protection is weak and any password
including the password to prevent the viewing of VB code can be easily
broken by anyone with a little knowledge and the will to do so. It is
therefore only suitable in a security sense for casual users and definitely
not for sensitive data.
Regards,
Alan.

 

[Guest]

Thanks Alan. That's pretty much what I gathered from other postings. However,
as I am a newbie to VBA, do you know of any sample code you could point me to
to achieve this (in particular the steps to be performed before the file is
opened: remove content, hide sheet)?

Any help would be much appreciated.

Thanks

 

[Alan]

This is the principal, on opening a pop up asks for a password, if the
password is (in this case '1234') sheets 2 and 3 are unhidden, if not they
stay hidden and an error message appears. On saving the file sheets two and
three will be hidden. After you enter the code save the file, come out of it
and reopen it.
Put the code below in 'This Workbook' To do this press Alt and F11 to open
the VB editor, on the top left you'll see 'This Workbook', double click that
and copy and paste the code in. Watch out for text wrap in the e-mail, the
'BeforeSave' line four from the bottom will probably appear as two lines, it
should all be on one line,

Private Sub Workbook_Open()
Dim Password
Password = Application.InputBox("Enter Password")
If Password = "1234" Then
Sheet2.Visible = xlSheetVisible
Sheet3.Visible = xlSheetVisible
Else
MsgBox "Password Invalid"
End If
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Sheet2.Visible = xlSheetVeryHidden
Sheet3.Visible = xlSheetVeryHidden
End Sub

Regards,
Alan.

 

[Guest]

Thanks Alan. Very much appreciated.

Best Regards,