blocking unprotect popup

J

jindon

MrExcel MVP

Joined

Aug 21, 2004

Messages

16,995

• Mar 20, 2007

• #2

When you protect, add one line

Sheets("yoursheetname").EnableSelection = xlUnlockedCells

 

Upvote 0

J

JoeS01

Well-known Member

Joined

Jun 25, 2005

Messages

832

• Mar 20, 2007

• #3

Sorry jindon, but that does not work as required. If I double click anywhere on the protected sheet, or hit a key stroke, then the Microsoft Excel message window pops up.

would you have any other suggestions ?

 

Upvote 0

Stormseed

Banned

Joined

Sep 18, 2006

Messages

3,274

• Mar 20, 2007

• #4

Sorry jindon, but that does not work as required. If I double click anywhere on the protected sheet, or hit a key stroke, then the Microsoft Excel message window pops up.

would you have any other suggestions ?

Click to expand...

Will you be able to tell the exact message the popup shows? I guess you have a protected sheet without a password and whenever the user tries to enter something you have a popup.Try unprotecting the sheet saving it and re-opening the same.

 

Upvote 0

R

royUK

Well-known Member

Joined

Jul 24, 2002

Messages

1,606

• Mar 20, 2007

• #5

You need to make sure tat any cells that require entries are unlocked, then add a password to the worksheet protection. That way only users with the correct password will be able to edit the sheet.

 

Upvote 0

J

jindon

MrExcel MVP

Joined

Aug 21, 2004

Messages

16,995

• Mar 20, 2007

• #6

Sorry jindon, but that does not work as required. If I double click anywhere on the protected sheet, or hit a key stroke, then the Microsoft Excel message window pops up.

would you have any other suggestions ?

Click to expand...

Do you have sheet event driven code on the sheet module?

 

Upvote 0

J

JoeS01

Well-known Member

Joined

Jun 25, 2005

Messages

832

• Mar 20, 2007

• #7

thanks Storm seed, but that did not work either.

You are right in that I have a protected sheet without a password.

I have also tried 'Application.DisplayAlerts = False' without success

 

Upvote 0

J

JoeS01

Well-known Member

Joined

Jun 25, 2005

Messages

832

• Mar 20, 2007

• #8

Roy, it is a bit more complicated.

After users do their test, and hit a Reset button, VBA code unprotects the Results worksheetsheet, update the results, then protects it again. The addition of a password makes this not possible , unless VBA handles the password internally without manual intervention.

Would you have any suggestions please?

 

Upvote 0

Richard Schollar

MrExcel MVP

Joined

Apr 19, 2005

Messages

23,707

• Mar 20, 2007

• #9

Joe

In code this would be:

Code:

Sheets("Results").Unprotect "YourPasswordHere"
...do stuff...
Sheets("Results").Protect "YourPasswordHere"

If you apply a password to your Visual Basic Project, then users won't be able (easily) to derive what the password is.

Hope this helps!

 

Upvote 0

J

JoeS01

Well-known Member

Joined

Jun 25, 2005

Messages

832

• Mar 20, 2007

• #10

Many thanks Richard, and to all other helpers. Richard's suggestion worked, except the format is as shown, wioth brackets around the password.

I was unaware that Excel VBA would handle a password

Code:

Sheets("Results").Protect ("YourPasswordHere")

 

Upvote 0