Help with protecting only certain cells based on a cells contents

**christopherjward** · 01-18-2013, 01:57 PM

Hi All

I am hoping that some one will be able to solve a problem i have with the attached file. Once a column has 8 entries between row 9 and 28 i want to protect all the cells to stop anymore being entered. this works but after a number of the columns between B and BK cells which should not be protected become protected?!

Any help/advice would be appriciated.

(Also any help on making the vba code more streamlined would also be appriciated!!)

**Mumps1** · 01-18-2013, 02:53 PM

Hello Christopher. Copy this code into the worksheet module (not a regular module). It will allow you to enter data into up to 8 cells in each column. When you try to enter data into the 9th cell, you get a warning message. I hope this does what you were looking for.

Private Sub Worksheet_Change(ByVal Target As Range)

Please Login or Register  to view this content.

**JBeaucaire** · 01-18-2013, 02:54 PM

You don't need VBA for this. Standard in-cell data validation will limit the number of entries allowed in any one column like so....

1) Unprotect the sheet, not needed.
2) Remove the Workbook_SheetChange macro in ThisWorkbook for now, it's not needed for this either
3) Highlight B9:BK28
4) Alt-D-L will open the data validation window
5) Apply the following settings on the highlighted cells:

Allow: Custom
Formula: =COUNTA(B$9:B$28)<9 (this formula will adjust itself for each column)

Click ERROR ALERT tab
Title: Maximum entries reached
Error Message: No more entries allowed. To enter a value in this cell you will need to remove one from another cell in this column.

MaxEntries.jpg

The main benefit here besides no VBA to manage, you can remove an entry in another cell and put one in a different cell, should be much friendlier to the user.

**christopherjward** · 01-18-2013, 05:15 PM

Thanks both for the help, both solutions are great! but raise another problem for me. What if i want to go over the set limit of 8? with the data validation it will not let me do this with out amending it.

basically i need the normal user to be able to take it the limit set and not go over or be able to remove what has been entered, and i need a 'authorised' person who can take it over the limit and remove entries if needed - does this make sense? thanks again for your help