PDA

View Full Version : Lock specific columns



pontuskrusin
05-20-2013, 05:07 AM
Hi,
I want to lock column A:B in sheet "PartsData".
But I still want them to be unlocked for my macro, just locked for users.

I've tried this:

Private Sub Workbook_Open()
With ActiveSheet
.Unprotect Password:="TEST"
.Range("A:B").Locked = True
.Protect Password:="TEST"
End With
End Sub

I'm not good with VBA, thus my post in this forum.

Would be really thankful if anyone had an idea that could help.

Thanks!

//The amateur

p45cal
05-20-2013, 06:17 AM
you only need one line in the open event:ActiveSheet.Protect Password:="TEST", userinterfaceonly:=True
although it would be more robust if you named the sheet rather than hope the active sheet is the one you want to protect.
(I've not locked those columns because all cells are locked by default.)

pontuskrusin
05-20-2013, 06:26 AM
you only need one line in the open event:ActiveSheet.Protect Password:="TEST", userinterfaceonly:=True
although it would be more robust if you named the sheet rather than hope the active sheet is the one you want to protect.
(I've not locked those columns because all cells are locked by default.)


Alright,

So how would the code look in reality?
Here's a copy of my excel file.
http: / / ge. tt/ 2n7UI9h/v/0

Thank you for the answer!

p45cal
05-20-2013, 06:47 AM
Private Sub Workbook_Open()
Sheets("PartsData").Protect Password:="TEST", userinterfaceonly:=True
Call Tempo
End Sub
You only need to use userinterfaceonly once, say when the workbook is opened; it stays like that unless changed with another .Protect line or until the workbook is closed.

Paul_Hossler
05-20-2013, 05:03 PM
Don't forget to password protect the VBA project or anyone would be able to bypass the worksheet protection

Paul

pontuskrusin
05-22-2013, 01:03 AM
Private Sub Workbook_Open()
Sheets("PartsData").Protect Password:="TEST", userinterfaceonly:=True
Call Tempo
End Sub
You only need to use userinterfaceonly once, say when the workbook is opened; it stays like that unless changed with another .Protect line or until the workbook is closed.


Alright, so now I have used the vba to lock the sheet.
How do i unlock column A:B for example?
I want the user to be able to edit column A:B but not the rest of the sheet.

Thanks in advance!

Nec11
05-22-2013, 01:48 AM
Unlock your excel file, go to "Review-> Allow Users to Edit Ranges". There you select ranges or particular cell that you wont to stay all the time Unlocked. than close and re open your file.

pontuskrusin
05-22-2013, 02:02 AM
Unlock your excel file, go to "Review-> Allow Users to Edit Ranges". There you select ranges or particular cell that you wont to stay all the time Unlocked. than close and re open your file.


I don't know what I'm doing wrong but I've allowed users to edit range A:B but the whole sheet get locked anyway and you can't access column A:B.
There's got to be a way to put the unlock in the vba?

p45cal
05-22-2013, 02:08 AM
Unlock your excel file, go to "Review-> Allow Users to Edit Ranges". There you select ranges or particular cell that you wont to stay all the time Unlocked. than close and re open your file.That's one way!

Unprotect the sheet, select columns A:B, press Ctrl+1 (it's a shortcut to Format Cells), go to the protection tab and untick the Locked box, OK, then protect the sheet again.

pontuskrusin
05-22-2013, 02:37 AM
That's one way!

Unprotect the sheet, select columns A:B, press Ctrl+1 (it's a shortcut to Format Cells), go to the protection tab and untick the Locked box, OK, then protect the sheet again.

I managed to untick the lock-button for column A:B but as soon as i click protect sheet, the columns get locked.
The VBA-code locks the whole sheet and nothing could unlock just two columns.

Any other ideas?

Nec11
05-22-2013, 03:15 AM
in attachment

p45cal
05-22-2013, 03:33 AM
I managed to untick the lock-button for column A:B but as soon as i click protect sheet, the columns get locked.
The VBA-code locks the whole sheet and nothing could unlock just two columns.

Any other ideas?
This raises an eyebrow or two..

Are you saying, when you unprotect the sheet again after you've done the Ctrl+1 thing I suggested that when you go into Ctrl+1 again after selecting any cell in columns A or B that the tick is once again to be found in the Locked check box?!

If so, search in the code (Ctrl+F) for Locked in the entire project, both for the workbook you're working on and the Personal vba project; if you find any could you post the entire line(s) here and say wher you found them?

pontuskrusin
05-22-2013, 04:15 AM
in attachment
Thanks you.

BUT, I need a macro to be able to write in the locked/protected cells.
Therefore, I can't use the built in protection-feature in excel.
I have to use a vba to lock the sheet and therefore I cant use the "Allow Users to Edit Ranges" either.

pontuskrusin
05-22-2013, 04:18 AM
10046

Here's the excel file.
I want to protect sheet "PartsData" but still have column D as writable for users.
The macro at sheet "Parts" should still be able to write in column A and B in sheet "PartsData".

p45cal
05-22-2013, 04:26 AM
BUT, I need a macro to be able to write in the locked/protected cells.
That's what the userinterfaceonly thing is for. It allows vba to write to locked cells.



Therefore, I can't use the built in protection-feature in excel.
Yes you can.



I have to use a vba to lock the sheet You may use vba to protect the sheet, but you don't need it to lock cells (change them from locked to unlocked and back).



and therefore I cant use the "Allow Users to Edit Ranges" either.Allow Users to Edit Ranges is overkill for what you're trying to do.

I will look at the file in your subsequent message now.

p45cal
05-22-2013, 04:54 AM
Here's the excel file.
I want to protect sheet "PartsData" but still have column D as writable for users.
The macro at sheet "Parts" should still be able to write in column A and B in sheet "PartsData".
I have entered the line I suggested in msg#4, unlocked column D, made sure all the other cells on that sheet were locked, protected the sheet manually.
See attached.

pontuskrusin
05-22-2013, 05:32 AM
I have entered the line I suggested in msg#4, unlocked column D, made sure all the other cells on that sheet were locked, protected the sheet manually.
See attached.


I don't know how to thank you but,
Thank you so much!!! :bow:

You've just made a lot of people happy!