PDA

View Full Version : Solved: Strange problem when protecting/unprotecting sheets



Cougarina
07-17-2011, 05:03 AM
I'm working on a difficult workbook since I never know how many rows there will be. Since the users only should enter data in Sheet1 and on Sheet 2-4 only be able to fill in data in one cell, like E13. I protected the sheet except the cell where the user is able to enter data, which then calculates some money to dispense.

I copy the data to the other sheets when the user leaves the G column. No problems there.

I have this code.
Sub Skydda_Blad()
Dim wrbBok As Workbook
Dim wrsAktivtBlad As Worksheet, wsBlad As Worksheet

Set wrbBok = ActiveWorkbook
Set wrsAktivtBlad = ActiveSheet
'Loop through all sheets
For Each wsBlad In Worksheets

'Sheet that we wont protect
If wsBlad.Name = "Sheet1" Then GoTo NastaBlad
If wsBlad.Name <> wrsAktivtBlad.Name Then
wsBlad.Protect Password:="a1b2c3d4" ' not the actual password ;-)
End If

NastaBlad:
Next wsBlad

End Sub

This gives me a strange behavior that I get cells selected and then cant work, Excel won't let me change cell with tab or enter. Does anyone have a better code or any idea why excel behaves like this?

What I want is that the data from Sheet1 is copied to Sheet2-4 but the user should only be getting access to one cell.

Ask if it's unclear what I mean and I'll try to explain.:dunno
The use of this workbook is so complicated that I have problem explaining it even in my native language.

Bob Phillips
07-17-2011, 08:54 AM
Do you have the locked property of E13 set to disabled?

Cougarina
07-17-2011, 09:18 AM
I locked every cell except E13. As I understand it, locking cells and protect the sheet is one way to prevent users from accessing the cells accept the ones allowed. Since I copy data from Sheet1 to the other three I need to unprotect the sheet and then protect them again, this gives me the strange behavior.

Bob Phillips
07-17-2011, 09:44 AM
You are absolutely right, and what you are doing should work fine, so I am a bit mystified. Can you post the workbook?

p45cal
07-17-2011, 09:50 AM
Since I copy data from Sheet1 to the other three I need to unprotect the sheet and then protect them again Not to manipulate data with vba you don't!
If you use the likes of:
wsBlad.Protect userinterfaceonly:=true
this will allow vba to move things around, but not the user.
However, this command only remains active for that session (while the workbook is open). If the workbook is closed, then reopened, you have to execute the statement again. So put a statement for each sheet that needs to have it, in the Workbook_Open event.

Cougarina
07-17-2011, 10:17 AM
Thanks to both of you for helping. Will try p45Cal's example as I think it will help me solve the problem.

Cougarina
07-18-2011, 02:24 AM
I cant seems to get it working. I get a message telling me that the sheet is protected. So back to square one then with the protect/unprotect mess.

Got it working now. Sheet2.Protect Password:="1a2b3c4d", UserInterFaceOnly:=True, now I can continue to make the rest work.:clap:
:beerchug: on me.