Automatic Protecting Question

[Ewing25]

Right now i have it so when a user opens the spreadsheet it asks them 2
questions

then after it filters based on the value the user entered it automatically
password protects it.

The problem is that when a user saves the spreadsheet and then comes back to
it the program gives them an error since its trying to protect an already
protected sheet.

How can i make it so it bypasses this part? any ideas?

Heres my Code:


Private Sub Workbook_Open()

UserForm2.Show


Dim Message1, Title1, MyValues
Message = "Please enter your name (First and Last)"
Title = "Name" '<--Change
MyValues = InputBox(Message, Title)
Sheets("Summary").Range("E8") = MyValues

Dim Message2, Title2, MyValue
Message = "Please Enter Last 5 Digits of Your Card Number"
Title = "AMEX Number" '<--Change
MyValue = InputBox(Message, Title)

If MyValue = "" Then
Sheets("Expense Amex").Protect password:="welcome"
Exit Sub

End If


Range("Cardholder_Number").AutoFilter
Range("Cardholder_Number").AutoFilter Field:=1, Criteria1:=MyValue,
visibleDropDown:=False
Sheets("Expense Amex").Protect password:="welcome"
On Error Resume Next


End Sub


Thanks,
Alex

 

[Mike H.]

You don't get an error protecting an already-protected spreadsheet. I just
tested that and that is not the problem. Since I can't run your code exactly
because I don't have your Userform, I don't know what might be causing the
problem, but you can always add an unprotect statement to an unprotected
sheet and not get an error. Try that and then I bet you won't get the error
again. And if you do get the error debug to see EXACTLY what line of code is
causing the error.

 

[RyanH]

There is no problem with protecting a already protected worksheet.

The issue is you are tyring to filter a range on a protected worksheet.
This is not good. Try Unprotecting your sheet then protecting again.

Also I'm not sure why you have On Error Resume Next at the end of your code,
it is irrelevant at the end of your code.

Hope this helps!