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

elfynstargazer

New Member
Joined
Oct 5, 2016
Messages
10
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!!!
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

elfynstargazer

New Member
Joined
Oct 5, 2016
Messages
10
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

Board Regular
Joined
Nov 2, 2015
Messages
151
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

Code:
Sub LockSelection()
Dim MyRng As Range

Set MyRng = Selection
MyRng.Locked = True



End Sub
 

HotRhodium

Board Regular
Joined
Nov 2, 2015
Messages
151
More better if you can find the range you want to lock somehow then set MyRng as that should work good



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

elfynstargazer

New Member
Joined
Oct 5, 2016
Messages
10

ADVERTISEMENT

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

Board Regular
Joined
Nov 2, 2015
Messages
151
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

Code:
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
 
Last edited:

elfynstargazer

New Member
Joined
Oct 5, 2016
Messages
10

ADVERTISEMENT

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

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

Board Regular
Joined
Nov 2, 2015
Messages
151
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

New Member
Joined
Oct 5, 2016
Messages
10
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

New Member
Joined
Oct 5, 2016
Messages
10
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!
 

Forum statistics

Threads
1,136,256
Messages
5,674,657
Members
419,520
Latest member
talha_ansari

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top