PDA

View Full Version : [SOLVED] Protect Sheets But Allow Data Entry From UserForm



thombill
09-14-2005, 07:50 AM
I have a workbook that has 4 spreadsheets in it. Two of the spreadsheets are for information and two of the spreadsheets are for calculations and reporting.

I created a UserForm that populates two of the spreadsheets. The problem is that once I have protected the Sheets so that the user can not change any formula's, the user form cannot input the data on to the protected sheets.

Does someone know a way that I can protect the sheets but allow the UserForm to work?

I thought maybe a conditional protection that is only on when the UserForm is not loaded.......

:doh:

austenr
09-14-2005, 08:06 AM
Hi. Try this:


ActiveSheet.Protect UserInterfaceOnly:=True

The trick is to protect the sheet with the UserInterfaceOnly parameter. Here's an example:
After this statement is executed, the worksheet is protected -- but your VBA code will still be able to make changes to locked cells and perform other operation that are not possible on a protected worksheet.

mdmackillop
09-14-2005, 09:43 AM
Hi Austen
I'd never taken notice of that parameter. Very useful. Stored away for the future. :work:

MWE
09-14-2005, 11:31 AM
Hi. Try this:

ActiveSheet.Protect UserInterfaceOnly:=True

The trick is to protect the sheet with the UserInterfaceOnly parameter. Here's an example:
After this statement is executed, the worksheet is protected -- but your VBA code will still be able to make changes to locked cells and perform other operation that are not possible on a protected worksheet.



this is an interesting approach. How does it differ from simply having theVBA code unprotect the sheet before updates and protecting it after?

austenr
09-14-2005, 11:56 AM
That is an interesting question. I really do not know. This is something I found one day when looking for information on another sheet protection issue.

Ken Puls
09-14-2005, 11:59 AM
this is an interesting approach. How does it differ from simply having theVBA code unprotect the sheet before updates and protecting it after?

Hey MWE,

Just run it in a Workbook_Open event, and you don't have to unprotect/reprotect the sheets as it renders it unnecessary. The only caveat is that this setting is not persistent (discarded when workbook is closed). You MUST retrigger it every time the workbook is opened.

MWE
09-14-2005, 12:25 PM
Hey MWE,

Just run it in a Workbook_Open event, and you don't have to unprotect/reprotect the sheets as it renders it unnecessary. The only caveat is that this setting is not persistent (discarded when workbook is closed). You MUST retrigger it every time the workbook is opened.
Thanks. This is a most interesting appoach. I need to do some testing to see how various scenarios are handled.

Ken Puls
09-14-2005, 12:28 PM
Truly, it's 6 of one...

From my own perspective I don't use it. I alway trigger protection on the sheets at open, and then unprotect/reprotect when necessary. I really don't find the extra lines of code that onerous.

Maybe I'm just a control freak though... ;)

peacenik
09-14-2005, 11:16 PM
This is very relevant to a job I am doing at the moment. I set up a workbook open code as follows:


Private Sub Workbook_Open()
For i = 1 To Sheets.Count
Sheets(i).Protect UserInterfaceOnly:=True, AllowFiltering:=True
Next
End Sub

But then when I run my code, I get an error when I run this code,



BOM.Range("I3:" & LastCol & LastRow).Locked = False


I can fix the problem by adding these rows either side of it.



BOM.Unprotect
BOM.Range("I3:" & LastCol & LastRow).Locked = False
BOM.Protect UserInterfaceOnly:=True, AllowFiltering:=True


So, this makes me wonder if the UserInterfaceONly bit actually works.

Ken Puls
09-14-2005, 11:36 PM
Peacenik, what version of Excel are you using? The following works for me in 2003:


Sub test3()
ActiveSheet.Protect Userinterfaceonly:=True, AllowFiltering:=True
Range("B1").Locked = False
End Sub

peacenik
09-14-2005, 11:37 PM
Office XP.

Ken Puls
09-14-2005, 11:44 PM
Unfortunately, I can't help you out there. Someone with XP will come along though. :)

PS... To anyone who was following Austen's question deactivating a workbook, I split those comments out to a new thread (http://www.vbaexpress.com/forum/showthread.php?p=43872#post43872)

austenr
09-15-2005, 08:09 AM
Here is the link to where I found the info if anyone wants to check it out.

http://www.j-walk.com/ss/excel/faqs/protectionFAQ.htm

thombill
09-15-2005, 09:08 AM
Thank you all,

This code works well for what I need. One more question though and it probably has an obvious answer that I am overlooking.

In the code below I am setting the protect scenario with a password, however I do not know how to set what the password is in the code or maybe I did and didn't know it or is there a default?



Private Sub Workbook_Open()
Sheet1.Protect Password:=True, UserinterfaceOnly:=True, AllowFiltering:=True
Sheet3.Protect Password:=True, UserinterfaceOnly:=True, AllowFiltering:=True
Sheet4.Protect Password:=True, UserinterfaceOnly:=True, AllowFiltering:=True
Sheet5.Protect Password:=True, UserinterfaceOnly:=True, AllowFiltering:=True
End Sub

austenr
09-15-2005, 09:11 AM
You set it in the properties section on the left side of the VBE.

MWE
09-15-2005, 09:12 AM
Here is the link to where I found the info if anyone wants to check it out.

http://www.j-walk.com/ss/excel/faqs/protectionFAQ.htm
thanks, this is a useful link and explained a few things that were fuzzy.

Note the paragraph on passwords and security:
Don't confuse protection with security. Worksheet protection is not a security feature. Fact is, Excel uses a very simple encryption system for worksheet protection. When you protect a worksheet with a password, that password -- as well as many others -- can be used to unprotect the worksheet. Consequently, it's very easy to "break" a password-protected worksheet
I wonder what those other passwords might be

Another interesting (and should have been obvious ... SHBO ... I need this acronym a lot recently) item:
Protecting a worksheet can also facilitate data entry. When a worksheet is locked, you can use the Tab key to move among the Unlocked cells. Pressing Tab moves to the next Unlocked cell. Locked cells are skipped over.

thombill
09-15-2005, 09:19 AM
Thank All!!!

I appreciate the help.