Understanding this VBA code

**rez9000** · 10-28-2010, 05:18 AM

This is my first post, so hello to all!

I registered because there was a specific problem that I wanted solving. The problem was locking a cell after a specific date has been reached or passed.

There was an old thread here where a couple of users had written some VBA code.

However, because the spreadsheet and situation is different for me, the code can't be used exactly as it is.

Having investigated, I've managed to understand some parts of the code, but was wondering if anyone would be willing to enlighten me on the rest.

Please Login or Register  to view this content.

First off, what does the following mean:

Please Login or Register  to view this content.

And this?

Please Login or Register  to view this content.

Now, this is where it gets a bit hairy for me.

Please Login or Register  to view this content.

Other than an earlier statement saying that it's a variable, I can't see any other definition of 'cl'. So how does the 'rBooking' variable interact with the 'cl' variable?

I assume the 'Set' statement is defining something for 'mArea'. But what exactly is it defining?

I can see the purpose of the 'If' statement, but not how it fulfills that purpose.

And a question not related to the actual code. The values in the cells I want to apply this code to are imported from other spreadsheets where they are the result of various formulae. If the date trigger is reached and the cell is locked, if the data in the other spreadsheets are changed, will the value remain the same in the locked cell?

My apologies for a a mammoth opening post, and the rather involved question, but I may be repeating this exercise over the next few months for work, and rather than repost the same question, I thought I'd try and understand how it works.

**davesexcel** · 10-28-2010, 05:31 AM

PW is the worksheet Protection password

cl is a range in the range
basically for each cell in the range rBookings

this code searches the range and if it is <= today's date the it will lock the cells

What are you trying to do?

**royUK** · 10-28-2010, 05:54 AM

The code with the Loop deals with Merged cells, something to avoid. Never use Merged Cells.

The password code uses a Constant to store the password, this is then used in the code to unprotect/protect the sheet allowing the macro to lock the cells.

I think this would be better for you

Please Login or Register  to view this content.

**rez9000** · 10-28-2010, 07:01 AM

Originally Posted by davesexcel

PW is the worksheet Protection password

cl is a range in the range
basically for each cell in the range rBookings

this code searches the range and if it is <= today's date the it will lock the cells

What are you trying to do?

I work for a large organisation and we do data processing. I'm building a future capacity plan for my company.

I have 11 teams who each need to input forecasts. Each forecast (cell) is for a weekly period. The forecasts are proportionate to staffing on each team during each week. Staffing fluctuates according to employees taking holidays, which then in turn lowers or raises forecasts.

I've built spreadsheets that deal with leave which then inform the capacity plan. But, if a staff member calls in sick, that is entered on the leave chart, which then affects the forecast, which I want to avoid.

So I need a module that will lock the forecast in place a week before it is due, so that if staff do call in sick, the forecast itself isn't affected.

Yours and royUK's responses have gone a long way toward me sorting it out. I'm going to have a stab at it now and see if it works. In short, a very big thank you.

Originally Posted by royUK

The code with the Loop deals with Merged cells, something to avoid. Never use Merged Cells.

The password code uses a Constant to store the password, this is then used in the code to unprotect/protect the sheet allowing the macro to lock the cells.

I think this would be better for you

Please Login or Register  to view this content.

Again, many thanks for the info. I'll try it out and see how it goes.

**rez9000** · 10-28-2010, 07:42 AM

OK, new question:

Is there a way of locking a cell whose value is the result of a calculation, in such a way that, once a certain trigger occurs, the value cannot be altered even if the cells used for the calculation are?

E.G.

A3 = sum(A1+A2)

Then once the trigger occurred, no matter how much one changes the other cells (A1 or A2), A3 remains the same.

More specifically, I'm asking in terms of time-dependancy. So that after a specific date, A3 cannot be altered no matter how much A1 or A2 is.

**karl1985** · 10-28-2010, 08:32 AM

paste special values and protect?!

**rez9000** · 10-28-2010, 08:43 AM

Originally Posted by karl1985

paste special values and protect?!

Sadly not. The two cells making up the formula must remain editable, but the outcome must stay locked after a certain date.

**royUK** · 10-28-2010, 08:58 AM

Do you mean with a formula?

This should be a new question as the other one appears to be solved

**rez9000** · 10-28-2010, 09:41 AM

Originally Posted by royUK

Do you mean with a formula?

This should be a new question as the other one appears to be solved

Far from it!

I'm just easing you in!

Because I've got an office to run, it's difficult for me to check up on the forum regularly, as I'm constantly being called into meetings.

When I get home though, it'll be a lot easier to provide details and even put up sample sheets. That way, I can illustrate exactly what I'm facing.

**royUK** · 10-28-2010, 10:14 AM

I don't know what you mean, you appear to have two questions in this Thread

Understanding this VBA code

LinkBack

Thread Tools

Rate This Thread

Display

Understanding this VBA code

Re: Help with VBA code

Re: Understanding this VBA code

Re: Understanding this VBA code

Re: Understanding this VBA code

Re: Understanding this VBA code

Re: Understanding this VBA code

Re: Understanding this VBA code

Re: Understanding this VBA code

Re: Understanding this VBA code

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions