Protecting a cell using VBA

  • I have a spreadsheet with a column of formulas that sums up a series of cells to the right of the column. The problem is that someone is changing those values. I tries protecting the cells/worksheet, but I then found out that there is an unprotect xla file that is floating around the office that must have been used to unprotect my sheet, and screw it up again. I'm guessing that whoever is doing this isn't noticing that the math is being done for him or her, because the number entered is always just about the average cell more than what it should be.

    How can I disable keyboard input, or allow input that doesn't "stick", to specific rows, columns, and cells?

  • Re: Protecting a cell using VBA


    Well you can't disable the key board just for that cell. But what you can do is track the cell selection event for the worksheet. And if the target cell is selected. Pop up a message and when the user clicks ok have the code activate a different cell.

    If you need an example let me know. I'll whip one up.

  • Re: Protecting a cell using VBA

    I kind of figured that that would be the answer. I just wrote around it. I basically made a refreshsheet function that rewrites the appropriate functions and copies them down the appropriate columns or across the appropriate rows whenever it's triggered. The triggering event then is when the cell before the current active cell was one of those cells with a function. For rewriting the functions (almost 850 of them), copying them all, and recalculation, it's surprisingly quick. I started to just copy over the one cell that was active, but then found that if a series of cells were deleted, I couldn't find a way to save the range that was chosen before a new cell became active. I think I walked around the block to get next door, but it works well.

    Thanks for the input!

  • Re: Protecting a cell using VBA


    There's some code in Module One that saves a range. It's fired by the worksheet Selection_Change event. That may help you.

    There is also some code in Module One that will restore that info and it is fired by the Worksheet_Change event.

    Give it a "target" to fire on and it could help with your situation (or someone else's - which is why I'm posting it here...)



Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!