VBA code to prevent typing in Cells and Columns

  • Hi,

    Just wondering if anyone had, I am guessing 5 mins to help me out with something that is probably very obvious (but not to me - at the moment)

    Essentially I have a template spreadsheet with months in columns and departments in rows. Rows per department will be consistent throughout but will vary between departments. I have written code that will prevent overtyping previous month / future month data, and also to ensure each department can only enter data for their department. Individually both sets of code run.

    However, what I need to do is amend the code so that rows and columns will both be blocked except for those that intersect each other - and that is where I have broken it.

    Just wondering if someone could take a peek at my attempt, and when you've finished laughing point me in the right direction.

    From the error messages I think it doesn't like the 2 lots of "for each"

    Any help is appreciated.

    Many thanks


  • Maybe

    Sub LockCells()
        With Sheet1.Cells(1).CurrentRegion
            .Parent.Unprotect "hello"
            .Locked = 0
            If Application.CountIf(.Rows(2), "TT") > 0 Then .Rows(2).Locked = 1
            If Application.CountIf(.Columns(1), "TT") > 0 Then .Columns(1).Locked = 1
            .Parent.Protect "hello"
        End With
        Sheet2.Visible = 2
    End Sub

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Hi there KjBox..... (and Everyone Else)

    Firstly, that's a hell of a lot neater and tidier than I have done. :) As said I am kind of rusty (probably totally corroded) with VBA having had nearly 3 years away from it and now finding myself using it more and more, so some is coming back to me, (too slowly) but am refreshing and learning new every day.

    I think I can understand as to what you have done (as in understand the code) ... I've copied the code in and run it however it is not doing what I intended. (However at least it's not falling over at the not liking 2 For statements any more. )

    This is due to my rather poor description so have attached a screenshot of a mini mocked up spreadsheet to hopefully illustrate what I am trying to do.

    I've added a few comments to the spreadsheet - just as text in cells.

    Essentially what I am trying to achieve is if the value of a cell within the range M2:AB2 is "TT" AND if the value of a cell in A5:A200 is "TT" then lock all the cells in the range A5:AB200 except where the rows and columns intersect.

    M2:AB2 will always only have one TT maximum as it relates directly to a month.
    A5:A200 will always have the same number of TT values however this will vary depending upon the department selected.

    Dept A will always be on rows 5-8
    Dept B will always be on rows 10-22
    Dept C will always be on rows 24-25
    etc etc

    Any pointers/help would be massively appreciated.

    Many thanks

  • Rather than an image, with which we cannot work, can you please attach your actual mini mocked up spreadsheet.

    Any cells that normally contain text or data fill with anything.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

Participate now!

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