Locking and unlocking columns based on date.

Cornhusker

New Member
Joined
Oct 20, 2010
Messages
19
My worksheet has a tab for each month with the name of the sheet the month and year (Feb 11, Mar 11, etc.)

In cells (D3:AH3) I have the day of the month.

I can't use an open event to lock cells because macros are not enabled when the file is opened.

Instead I want to use a close event to protect and lock the entire worksheet.

Then when the user opens the worksheet he/she will have to enable macros and then I'll need a macro to trigger that will unlock all of the columns except those that are from previous dates.

It goes the long way around but accomplishes what I want.

I would need the two seperate codes 1 to lock all of the cells on all the sheets with a close event.

another to unlock all the cells except the ones from previous days. Each sheet represents a different month and the range (d3:ah3) has 1-31 for the days of the month.

Thanks for the help
 
This worked great until about 2 weeks ago. I have narrowed it down to this line of code:

Code:
    .Range(.Columns(34), .Columns(CLng(Format(Date, "d")) + 3)).Locked = False

I get the following error when I run the macro:

"Unable to set the Locked property of the Range class"

by trial and error I figure that it is somewhere in the "CLng" function from above. If I substitute the following the macro works fine:

Code:
    .Range(.Columns(34), .Columns(3)).Locked = False

Not sure why it worked before, but doesn't know. Any advice would be appreciated.
 
Upvote 0

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
If you get that error and then while the code is highlighted in yellow, you select this part:

Code:
CLng(Format(Date, "d")) + 3

right-click and "add watch," then what value does it say that results in? It should be 27 I think, since today is the 24th and add 3... If that is the case, then are you able to go manually to columns 27 thru 34 and unlock them?
 
Upvote 0
If you get any more awesome I'm going to have to fly up to Chicago and buy you some beers. There was a row that had merged cells that one of the users had not asked me prior to the modification. His password has been revoked. :) The value was 27, but I was unable to manually unlock the cells, which gave me the merged cells are unable to be modified message.

I unmerged the cells and it works yet again.
 
Upvote 0
If you wanted to, you could add to your code to make it un-merge all cells on each sheet before locking - should be relatively easy to get the code with the macro recorder.

...Everything I know was taught to me on this forum; I had never heard of vba until several hundred posts in I think! :beerchug:
 
Upvote 0
I may do that. I'm glad you said that about the posts. I was about to enroll in an Excel class focusing on VBA so that I could start solving the problems of others on here rather than having to post questions all the time. I still may. :)
 
Upvote 0
I think it's a great idea to take a class. When I started to just browse threads on here instead of only coming to ask my own questions, that's when I started to get a feel for the enormity of possible approaches that I not only didn't know how to do but was completely unaware of... Surely a structured approach (like a class) would be great to make sure you have a good foundation.

Hiker95 has provided a great list of resources in post #2 here: http://www.mrexcel.com/forum/showthread.php?t=537982

Shamefully, I've never really taken the time to do a class or read a full book... But I did purchase the Power Programming 2003 listed in hiker95's post, as well as a book about Integrating Access and Excel 2007 which has proved very, very handy when I've tried to look things up in it... Have fun!
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,106
Members
452,302
Latest member
TaMere

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
Back
Top