Hide or Lock column based on cell date

  • Dear All,

    Herewith i have attached production workbook for your reference.

    In this production sheet, we need to lock or hide or protect the entire column based of date.

    If today date is 19.09.13, all other column should locked or protect from editing except 18.9.13,19.9.13 dated column.

    Let me know and expecting help from you.

    Thanks in Advance.


  • Re: Hide or Lock column based on cell date

    I have been dancing all around this with recent posts both of my own and others... trying to create a solution specific to your need though.
    Unfortunately I've been up 19 hours after getting 3 hours sleep last night, so my mind is in an infinite loop of it's own.

    I will take another shot at this in the morning once I've had a little more sleep if no one else has helped by then.

  • Re: Hide or Lock column based on cell date

    Sorry for taking longer than I said... Had a family issue yesterday that I had to take care of.

    Add the following to a form control to run. Works for me so far... trying to get it to run without the form control, but haven't been successful so far, so if anyone else much more knowledgeable can help, would be appreciated. Tried to remove the redundant pieces as I pushed together a few things to make it. Hope it works for you. It selects the cells directly in the column that today's date references and unlocks them. Added a blank password, change the "" to "whateverpasswordyouwant" to add an actual password to the sheet.

  • Re: Hide or Lock column based on cell date

    So... realized in the original post that prior dates need to be accessible as well...

    Tried to make a loop that worked back to range (F4:F10), and upon hitting that range stopped, but am being unsuccessful so far.

    Can anyone give a hand? This is what I need to loop back to that range...

    Selection.Offset(0, -1).Select
        Selection.Locked = False
        Selection.FormulaHidden = False

    Which would go right after:

        ActiveCell.Offset(1, 0).Select
        Range(ActiveCell, Cells(ActiveCell.Row + 6, ActiveCell.Column)).Select
        Selection.Locked = False
        Selection.FormulaHidden = False

    Pretty sure I'm messing up the syntax etc with the loop I've tried, which was going to be a do:until loop.
    After that this should be a solution to the request.

  • Re: Hide or Lock column based on cell date

    Dear Sir,

    Thanks for your valuable reply.

    While run the code it shows following error.

    Object variable or with Block variable not set

    Can you help for this?

  • Re: Hide or Lock column based on cell date

    To Thisworkbook module

  • Re: Hide or Lock column based on cell date

    Dear Mr.jindon,

    i have noticed following mistake.

    i have added VB in my excel on 27/09/13.its lock all column except 27/9/13 and 26/09/13.
    But today (29/09/13) morning i have opened that excel.It is not changed with today date.
    Expecting feedback for this.

    Thanks in advance.

  • Re: Hide or Lock column based on cell date

    Dear Mr.Jindon,

    its working but need to go and run the code daily.in my application if date changed that code should automatically run.
    This is problem i having.
    Is it possible?
    Thanks in advance.

Participate now!

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