Lock column stopping further entry when pre defined total reached

Hello All,

Is there a way to lock a column when a cell containing a running total reaches a pre-defined value then triggers a Msg box advising the total has been reached?

This will need to apply to 365 columns of numeric data.

Thanks,

John

Hi there,

Does each column have its own Total Cell, or should all columns be locked once the value in a single Total Cell reaches a preset maximum value?

Regards,

Greg M

Thanks for replying so promptly.

What I'm trying to achieve is an Annual leave planner for next year.

I have 120 staff each occupying a single row. The columns represent the days of the year. I can have a maximum of 16 staff off on leave on any given day.

As leave is booked I put a 1 in the cell intersecting the person's name and the day required.

Each column will be auto summed. What I want is for the column to be locked off when 16 days have been booked and a Msg box just warning the user if possible.

Hope that makes sense.

Regards,

John

Hi again,

Yes, that makes sense, but I imagine you DON'T want to lock the cells which already contain a "1" - otherwise you won't be able to make changes once you've booked 16 days for a staff member who then changes his mind about the day(s) he's already requested. Is this the case?

Regards,

Greg M

An alternative to locking, which ultimately has the same effect, but allows for changes that might be needed, as Greg has observed.

Assuming your first autosum is in B122 (based on description).

Apply Data Validation to B2:NB121 using a custom criteria with a formula of =IF(B$122<17,1,"")

Edit the input messages and error alerts as desired.

Hi again,

Take a look at the attached workbook and see if it does what you need.

All cells in a "day column" remain unlocked and unhighlighted until sixteen "1's" have been entered in the column.

Once sixteen "1's" have been entered, all empty cells are locked and highlighted in grey to indicate that no more leave days can be booked for that day. The cells which contain "1's" are unlocked and unhighlighted. Once any of the "1's" is deleted (thereby bringing the total number below sixteen) all cells in the column are unlocked and unhighlighted to allow further days (up to a total of sixteen) to be booked.

The code involved is as follows:

Standard VBA Module:

Please Login or Register  to view this content.

The highlighted values may be changed to suit your own requirements.


Worksheet VBA Module:

Please Login or Register  to view this content.

Hope this helps - please let me know how you get on.

Regards,

Greg M

Hi, each column needs to lock as the required total of 16 is reached

Originally Posted by Aestivator1

Hi, each column needs to lock as the required total of 16 is reached

Why does it specifically need to be 'Locked'?

The method I suggested on post #7 will prevent any additional entries when the total is reached so is as good as locking the column, but will still allow you do delete an entry and add another if you make a mistake.

Thanks Greg. That is just the very thing.

I need to adjust slightly as the max leave entitlements for spring and autumn are a little lower. Is that easy to do?
I'm not sure I understand the difference between worksheet and standard module.

Originally Posted by Aestivator1

I need to adjust slightly as the max leave entitlements for spring and autumn are a little lower.

Again, check the suggestion for Data Validation. You could customise each column with very little effort.

Thanks Jason. I'll give it a go.

Hi again,

Many thanks for your feedback.

Take a look at the attached workbook and see what you think.

An additional row has been added below the Totals row - it contains the maximum number of leave days allowed for each day of the 366 days in the worksheet. I've entered 16 in each of the cells, but you can change these values to whatever is appropriate for the individual day.

I've also added Data Validation so that only a "1" or a blank value may be entered in any of the "booking" cells.

Regarding:

I'm not sure I understand the difference between worksheet and standard module.

A Standard VBA Module is created by selecting "Insert >> Module" from the main menu in the VBA Editor.

A Worksheet VBA Module doesn't have to be created (it exists once the worksheet is created) and is accessed by right-clicking on the worksheet tab and selecting "View Code" from the resulting menu.

Hope this helps - as before, please let me know how you get on.

Regards,

Greg M

Thanks Greg. Really grateful for your help. That is just what I was looking for.

I have two header rows one for the date and one for the day as opposed to your one. Which bit of the code do I need to adjust for the range?

Thanks again,

John

Hi again John,

My workbook contains two defined Name ranges, "tblDateCells" and "tblTotalCells" which are used by the VBA code.

To create these Names in your own workbook do the following:

Unprotect the worksheet

Select the 366 cells which contain (day? date?) values in the row immediately above the row which corresponds to the first staff member - in my workbook the cells to be selected are in Row 1

Select Formulas >> Name Manager >> New, enter "tblDateCells" (without quotes) in the "Name" box, select the appropriate worksheet from the dropdown list in the "Scope" box, click on "OK", and then click on "Close"

Now select the 366 cells which contain Total values (Row 122 in my workbook)

Select Formulas >> Name Manager >> New, enter "tblTotalCells" (without quotes) in the "Name" box, select the appropriate worksheet from the dropdown list in the "Scope" box, click on "OK", and then click on "Close"

Protect the worksheet

Hope this helps - as before, please let me know how you get on.

Regards,

Greg M

All good now Greg. Thanks for your help.

Hi again John,

Many thanks for your feedback - glad I was able to help.

Best regards,

Greg M