Need help to lock/unlock multiple sections of a worksheet as when clicked by user

[elfynstargazer]

Hi guys,

I am an excel savvy person, but not too expert like VBA or macro programmers. As such, I need some help on figuring out how to lock certain portions of my worksheet upon a click of a button.

Imagine a 30-day calendar spread page.
I need to have 30 buttons which will enable the user to lock only the columns under Day 1 when clicked on the button related to Day 1. And, similarly, to unlock it again upon clicking. The rest of the columns under Day 2 to Day 30 are still editable.

I have tried the basic 'protect page', 'allow users to edit ranges' method and a few other basic vba attempts which all didn't turn out to produce the results I want.

Pls pls, your help is much appreciated!!!

Thank you!!!

 

[elfynstargazer]

How do I upload the file here? I think its best to show the excel file directly, so you guys can get a better picture.

 

[HotRhodium]

This seems like it may be a more complicated idea than you are thinking. What are you intending to do with this? If you want it to lock just the cells you have designated for a specific date you need to name them and then find them then lock them. You could just have the user select cells they want to lock like this

Sub LockSelection()
Dim MyRng As Range

Set MyRng = Selection
MyRng.Locked = True



End Sub

 

[HotRhodium]

More better if you can find the range you want to lock somehow then set MyRng as that should work good

Sub LockSelection()
Dim MyRng As Range
 Set MyRng = Selection
 MyRng.Locked = True
 MyRng.Interior.Pattern = xlGray8
End Sub

 

[elfynstargazer]

Hi HotRhodium,

Thanks a lot for your quick feedback.

I have multiple files of the same format for use by different marketing staff for them to key in orders, for every day of the month.
Because of file sharing between more than 2 persons, we thought of putting in an extra measure to have locking features so that once the day is complete, it will be locked and past sales orders cannot be altered simply or accidentally, unless you unlock it with a password.

As for the method you have suggested above, will it be workable for 30 times on a same sheet? i.e. having 30 ranges for 30 days in a month?

I can't go in manually and lock it each time the day ends, as I have about 10 files like this across the department. So, I need the user to be able to lock it on their own without having to go through much hassle with the excel programming.

 

[HotRhodium]

Oh yeah you should be able to lock unlock as many times as you want. I use this guy because you can lock and unlock (set lockme to true to lock and false to unlock. When I use it I make it a function and check the range then send back true when the function works and false when it fails

Public Sub LockUnlockRng(LockMe As Boolean, InputRng As Range)
 InputRng.Locked = LockMe
 InputRng.Interior.Pattern = xlGray8
End Sub

Are the ranges for each date Named?

With calendars I name everything with a function like "Date" & Format(MyDate,"mmddyy")

that way I can set it to a range variable and send it off to the next function. don't forget to unprotect the sheet then lock it again (If you want you can lock it so the code can change it without unlocking it). hope this helps

 

[elfynstargazer]

Okay this idea sounds great, but it is way too complicated for my level of excel!

Is there any other way you could guide me, or maybe I can send you the file, so you could show me a sample, then I can do the rest ? >.<

I would really appreciate that...

 

[HotRhodium]

It is not a problem for me but it is not easy to share workbooks here. It seems that you have 2 options

1: Use selected cells. This will allow the user to select cells and then click a button and lock them. This will be the easiest and you should be able to implement.

2: Use range names. Much higher level. First all ranges you want to lock must be named. then resolve range based on input criteria then make the selection needed and lock.

so is the range that represents a "Day" named? Is the calendar dynamically created? Is there some form of logical progression to the calendar? EG the 1st of the month is only in column A the 2nd only in column B etc?

 

[elfynstargazer]

Wow, thank you thank you! That was brilliant. I think the selected cells method is really easy and do-able. I'm trying it out now. Thanks! I think it should settle my problem!
TQ TQ TQ~~~ many thanks again~!

 

[elfynstargazer]

Hi,

I have another question...can you help me figure this code out...I need to insert a password protection here to compliment my locking button. Where do i insert the [protect password("password") command in my current coding?

Private Sub Workbook_Open()
Dim wksht As Worksheet
For Each wksht In ThisWorkbook.Sheets
With wksht
.EnableOutlining = True
.Protect contents:=True, userInterfaceOnly:=True, AllowFormattingCells:=True, AllowInsertingRows:=True, AllowDeletingRows:=True
End With
Next wksht
End Sub

Many thanks!