• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Locking the cell automaticall once the date passed

Status
Not open for further replies.
S

sambashivamk

New Member
Hi,


I have created a volume updation tracker for my team.I mentioned the date on Cell A1 and the teams are updating the data in a2,a3,a4....like this..and once this date (a1) is passed all the remaining a2,a3,a4 should get locked automatically. how to do this Pls suggest.
 
Hi Sambashivam ,


The following procedure should do the job :

Code: 
Public Sub Worksheet_Activate()
Set Date_Entry_Cell = Range("A1")       ' Change this as required
Set Data_Entry_Range = Range("A2:A100") ' Change this as required
ActiveSheet.Unprotect
If Date_Entry_Cell <= Date Then
Data_Entry_Range.Locked = True
Else
Data_Entry_Range.Locked = False
End If
ActiveSheet.Protect
End Sub

Narayan
 
Last edited:
Deary Narayan,


Thanks for your help.


I have a date in B1 and locking range is B2:B25 but this code will lock entire sheet once the date passed . I want only the cells B2:B25 to be locked. Need ur input in this regard.


Thanks.
 
Hi Sambashivam ,


By default , all the cells in a worksheet are locked ; when you protect the worksheet , the locking comes into effect.


What you can do is select the entire worksheet ( click on the small square to the left of column A , and above row 1 ) and unlock all the cells ; thereafter when you run the macro , only the cells which are locked will be not available to the user ; all other cells will be available.


As far as the cells to be locked are concerned , change the addresses in these statements :

Code: 
Set Date_Entry_Cell = Range("A1")
Set Data_Entry_Range = Range("A2:A100")

Narayan
 
Last edited:
@all


Hi!


In this cases, when a user wants to send an e-mail to a contributor, shouldn't it be more polite to post its own mail instead of asking others to share theirs?

And I write this having shared my e-mail many times as NARAYANK991 usually does.


I was just wondering... and I think that it'd be more fair, safer and with less exposure to the non starter part to do that... isn't it so?


I'm considering to begin answering in this cases something like "no, I can't share mine, but you post yours and I'll write you". Maybe it sounds a little rude, but not such as rude as what being asked.


Regards!
 
Totalmente entender, te aprecio sinceros comentarios, vamos a mantenerlo simple. He trabajado en la hoja de excel y ahora tienen una mejor comprensión de la misma, gracias de nuevo por mostrar a mí. Siempre he tenido la gente hacer este tipo de hojas de software, pero ahora que mi empresa ha despedido a mucha gente, tengo que aprender, y qué manera mejor que la de un experto.


Lo siento si no contesto lo suficientemente rápido, pero estoy trabajando para una firma de abogados que hacen de mi vida un infierno, estoy a su disposición 24/7, se va esta noche a volar con dos de mis jefes de Detroit EE.UU. (un estado que necesita ayuda) para ayudar a cerrar nuestra oficina allí, así que no será en este sitio web mucho más.


Muchas gracias,

Sam!


English:


Totally understand, I appreciate you candid comments, lets keep it simple. I have worked on the excel sheet and now have a better understanding of it, thanks again for showing it to me. I've always had other people make these types of software sheets, but now that my company has laid off so many people, I have to learn, and what a better way than that of an expert.


Sorry if I don't reply fast enough, but I'm working for a law firm that make my life hell, I'm at their disposal 24/7, will be leaving tonight to fly out with two of my bosses to Detroit USA ( a state that needs help) to help close our office there, so won't be on this website much.


Many thanks,

Sam!
 
Hi.
I really appreciate if someone can help me in executing macros as mention.
I try to many site to excute my query but fail to do so. I also execute above code but fail to use. So i again write in this forum so that
My team need to data entry on daily basis on today's date. All cells should be locked only today's will be open for data entry/editable. Also it should be protected too.

61536

I really appreciate if someone help me.
Thanks
 
Above DineshK continues here:

Lock today's cell (row/column)

Hi. I really appreciate if someone can help me in executing macros as mention. I try to many site to excute my query but fail to do so. I also execute above code but fail to use. So i again write in this forum so that My team need to data entry on daily basis on today's date. All cells should be...
chandoo.org

No more replies here.
 
Status
Not open for further replies.
Back
Top