Solved: Prevent users from editing every 'nth' row - eg every 3rd or 4th row.

[Airwalker]

Hi,
I'm working on a set of rather large sheets where we have products in column C, with 4 rows for each product (Base, Promo, Total Planned, Sales In).

'Base' and 'Promo' are user input rows, with formulas in 'Total Planned' and 'Sales In'. Each sheet can vary in length (some down to 300 or so rows, and others as far down to 5000+ rows!) depending on the number of products in each category.

The data is consistent in the sense that 'Base' is every 1st row, 'Promo' is every 2nd, 'Total Planned' is every 3rd, and 'Sales In' is every 4th.

What I would like to do (without having to protect each sheet, or go through and define each row) is to tell Excel that if the user goes in to edit either the 'Total Planned', or 'Sales In' rows, to prevent them from making any changes to these rows.

I saw something on here a while back in the knowledge base (found from an external link) where the code told Excel to protect every 3rd row, or column, or a defined range (the example sheet that was attached had all three as examples), but for the life of me I can't find it anywhere! Not even from the site I'm sure I found it through - I even remember saving the example file, but I can't even find that now!!

I've seen a few similar sets of code around here that protect a set range, or specific row, but they don't seem to allow for multiple sets of ranges (rows/columns).

Is there anyone that either knows the code I'm looking for and can direct me back to it, or someone that can show me how this would be done so that I can adjust the code for my own requirement?

Many thanks in advance.

Wai

[Bob Phillips]

Why do you not want to protect the sheet, that is what it is for?

[Airwalker]

Hi,

I have some buttons that will run macros, and a vastnumber of other macros doing different functions, and protecting the sheet will disable these. I know there is code that can disable / re-enable the protection, but this will mean adding that extra code to the start and end of a vast number of macros, whereas instead I can add one extra macro to each sheet to tell it to protect every 3rd or 4th row.

The other reservation I have is that I would also need to protect certain cells, then repeat this (or copy the format, if this would pull in the protection?) and duplicate the process over several lines/sheets. The total file sizes(after using various methods to control memory use and clean up file size) is 216Mb (96Mb zipped with 'Optimized' compression in WinZip) - I hope this gives you a good idea of the sheer scale of the project.

The sheets are used as a forecasting / planning tool across over 4000 products, over 14 different categories, over a 2 year period (Jan '08 - Dec '09) by week, with month totals. I have all the sheets built, and linked, (14 input sheets and 1 'Totals / Summary' sheet). The tool also links into a SAP download to pull in volumes for each product that have been invoiced, as well as financial information to provide a historical and forward values. The information will then feed into summarised management reports where they can view by account/product/week/month etc where we are in the month/year in relation to where we would like to be.

This sheet will then be duplicated for each sales account for them to plan projected sales forecasts/promotional activities. This information is then also sent over to our inventory control so that they can provide the details to our factories across Europe so they know how much stock of each line we will require.

The macros contained include those to summarise data by month/week/quarter and year, by product/product group/business group. There are also a vast number of macros to provide different print views, and also (due to the amount of data being handled by the total sheet) macros to pull in the values from the inout sheets (as values only).

This has been a mammoth task that I have been working on over the last 6 weeks, and now that it is (near to) complete I've been asked to protect the 'Total Planned' and 'Sales In' to make sure our sales guys don't overwrite them. That's when I remembered seeing the code that automatically brought up a message whenever every 3rd or 4th row (or whatever value you set it to) had been amended.

I hope this helps clear up why I don't particularly want to use the built in protection, and also what the sheet is being used for?

Many thanks,

Wai

[lenze]

Place this in the ThisWorkBook module.


Private Sub WorkBook_Open()
Sheet1.Protect "password" UserInterFaceOnly: = True

This will allow your codes to run, while preventing manual entries in "Locked" cells
lenze

[georgiboy]

Do as lenze said with the workbook that works well. you may want a simple piece of code to protect every Nth row something like this.


Sub LockCells()
Cells.Locked = False

Range("A4").Select
Selection.EntireRow.Locked = True

Do While ActiveCell <> ""
ActiveCell.Offset(4, 0).Activate
Selection.EntireRow.Locked = True
Loop

End Sub


Hope this helps

[Airwalker]

Thanks, both of you! I used lenze's code but did make a minor modification (in order to allow the use of the autofilters I have installed).

In the end I protected the cells manually, by editing one set for one product (unlocking the first two data entry rows) and copying that over the rest. Georgi's code did throw me an error, but as I was pushed for time today I just unlocked the cells for data entry along one product (leaving formulated areas locked) and copied this down filtering to the first line and using ctrl-G / visible cells, unfiltering and pasting formats. This actually ended up being quicker than I thought it would be at first!

You've helped me put in the final piece of the puzzle! Thanks again!

Wai