Locking and unlocking columns based on date.

[Cornhusker]

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

 

[Cornhusker]

This worked great until about 2 weeks ago. I have narrowed it down to this line of 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:

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

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

 

[taigovinda]

If you get that error and then while the code is highlighted in yellow, you select this part:

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?

 

[Cornhusker]

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.

 

[taigovinda]

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!

 

[Cornhusker]

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.

 

[taigovinda]

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!