MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Security worksheet

  Asked By: Dora    Date: Feb 21    Category: MS Office    Views: 1744

Please could you help me understand and solve this problem:

I have a workbook with multiple worksheets that the user should not
see and change. I tested many methods independently to protect them:

1. I protected the worksheets with VBA in chosing for visible
xlsheetveryhidden from the properties sheet.

2. I put the codes in the worksheet
Private Sub HideSheet()
Sheet1.Visible = xlSheetVeryHidden
End Sub
I also used these codes when I open the workbook.
Sub Protect_all()
Dim WS As Worksheet
For Each WS In Worksheets
Next WS
End Sub

3.I did from Tools protection, protect sheet.

I always get this error when I run the macros
Run Time error "1004"

Do you think I could use UserInterfaceOnly:=True, but I am not sure
how to use it.



1 Answer Found

Answer #1    Answered By: Rochelle Elliott     Answered On: Feb 21

This is kind of an out-of-the-box way of dealing with it but...

In each of your worksheets  add this little code

Private Sub Worksheet_Activate()


End Sub

This makes it so anytime a user  tries to open  another worksheet  the
workbook automatically reopens the only worksheet you want them to see,
thus denying them the ability to see or manipulate the worksheet. Like
I said out-of-the-box but effective

Didn't find what you were looking for? Find more on Security worksheet Or get search suggestion and latest updates.