• 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.

Lock the cells once data entry done & Date condition

T

TPR

Member
Dear Friends,

Am appending a sample file.
"B" & "F" columns are carrying Dates. I want such a formula in all cells of those columns by which when the work book will be opened on a date that particular date will be shown on the very next cell of that sheet. i highlighted yellow. But the previous day will remain same, that will not be changed.
Moreover all the data of the past days can not be changed(will be auto locked by a fixed password), even if PC date is manually changed.

In a nutshell:

1) Date will be automatically changed in the very next cells of column "B" & "F"

2) If a data is entered in any cell of any sheet of this workbook, that will be auto-locked by a pre-defined password. Only admin shall be able to modify that.

And it will be done in all sheets.

Please help.
Thanks in advance.

Regards
TPR.
 

Attachments

  • sample.xlsx
    8.9 KB · Views: 4
TPR
hmm ? ... just under 'Ask an Excel Question'
under VBA Macros ... this would be more possible ... at least for me.
 
TPR
the 1st part ... verify !
today: B4 & F4 have values 23/07/2017 - okay
tomorrow: B5 & F5 will be automatic 24/07/2017, no matter what would be there and after that those cells will be locked too!
and so on with those dates - or how?

>> other cells as explained in 'nutshell' #2:
works already! After edit lock cell! Only password is missing!
 

Attachments

  • sample (4).xlsb
    15.5 KB · Views: 6
vletm said:
TPR
the 1st part ... verify !
today: B4 & F4 have values 23/07/2017 - okay
tomorrow: B5 & F5 will be automatic 24/07/2017, no matter what would be there and after that those cells will be locked too!
and so on with those dates - or how?

>> other cells as explained in 'nutshell' #2:
works already! After edit lock cell! Only password is missing!
Click to expand...
Hi,
Nothing is happening Sir. I told that 23-7-17 will remain as it was. in the next cell which are highlighted yellow will show the day, on that day the workbook will be opened. But all the row of 23-07-17 i.e. Row 4 will be locked automatically, nothing can be modified, except the admin, he / she will be able to modify by a pre-defined password. Similarly afetr the day of 24-07-17,(if the workbook is opened on 24-07-17 & data is entered on that day) the rows of 4 & 5 will be locked. in this way the sheet will progress. When July month will be ended, data will be entered in the next sheet for the month of August.

Regards,
TPR
 
... and what did You? Nothing?
You didn't nothing,
if You didn't notice nothing!

As I tried to write #1 verify
... means what do You really mean?

'nutshell' #2: worked already ... really nothing?

Now, You added few new cases ... ex Monthly-sheet.
I did something which I didn't test for that case too.

Now there are passwords too.

Note: I hope that You really understood Your 'nutshell #2':
2) If a data is entered in any cell of any sheet of this workbook, that will be auto-locked by a pre-defined password. Only admin shall be able to modify that.
That works too ...
 

Attachments

  • sample (4).xlsb
    20.4 KB · Views: 2
vletm said:
... and what did You? Nothing?
You didn't nothing,
if You didn't notice nothing!

As I tried to write #1 verify
... means what do You really mean?

'nutshell' #2: worked already ... really nothing?

Now, You added few new cases ... ex Monthly-sheet.
I did something which I didn't test for that case too.

Now there are passwords too.

Note: I hope that You really understood Your 'nutshell #2':
2) If a data is entered in any cell of any sheet of this workbook, that will be auto-locked by a pre-defined password. Only admin shall be able to modify that.
That works too ...
Click to expand...
Hi,
Almost ok.
Only issue is that entry would be done for the date of 24-07-17 & future dates. Therefore locked cells would be only for Row 4, rest cells of other rows will not be locked for today.
Similarly tomorrow locked cells will be Row number 4 & 5. day after tomorrow locked cells will be Row number 4, 5, & 6............contd/- in this pattern.

That means for today the data entry operator will be able to modify the data for today itself, tomorrow he / she will not be able to modify any data of today.

Regards
TPR
 
Almost ok?
1) ...entry would be done for the date of 24-07-17 & future dates...
that is possible (if 24-07-17 is today) and also for future days
2) Only rows until previous days will lock and also those today's dates!
3) Same will happen next time - previous rows will lock as well next time's dates
4) Today or any day,
anybody can modify the data ONCE as You wanted in Your 'nutshell' #2.
If a data is entered in any cell of any sheet of this workbook, that will be auto-locked by a pre-defined password. Only admin shall be able to modify that.
Which I have tried to confirm already twice!
> What do You mean?
 
vletm said:
Almost ok?
1) ...entry would be done for the date of 24-07-17 & future dates...
that is possible (if 24-07-17 is today) and also for future days
2) Only rows until previous days will lock and also those today's dates!
3) Same will happen next time - previous rows will lock as well next time's dates
4) Today or any day,
anybody can modify the data ONCE as You wanted in Your 'nutshell' #2.
If a data is entered in any cell of any sheet of this workbook, that will be auto-locked by a pre-defined password. Only admin shall be able to modify that.
Which I have tried to confirm already twice!
> What do You mean?
Click to expand...

If I consider my nutshell #2, that also does not serve in the last file. all the cells are locked, how entry will be done ?

However, my mgmt wants my last conditions.
Pls help. Sorry for bothering you.
 
Did You test that file or not?
If tested then answer is Yes,
if not tested then answer is No.
What do You mean 'all the cells are locked'?
... There are locked only cells which You have wanted!
+ those the newest date!
Do We try to write about same file?
What do You mean 'how entry will be done?'
... Did You even try to entry something?
... It would be good that Your ... something ... wants something as nutshell #2.
It is challenge if You cannot answer even the most simply questions!
 
vletm said:
Did You test that file or not?
If tested then answer is Yes,
if not tested then answer is No.
What do You mean 'all the cells are locked'?
... There are locked only cells which You have wanted!
+ those the newest date!
Do We try to write about same file?
What do You mean 'how entry will be done?'
... Did You even try to entry something?
... It would be good that Your ... something ... wants something as nutshell #2.
It is challenge if You cannot answer even the most simply questions!
Click to expand...
Hi,
See entries will be done on the present day, that means 24-07-17, today's cells of that row & next rows that means from row number 5 to unlimited will be unlocked for today only, tomorrow row 5 will be locked, next row i.e. row number 6 will remain unlocked & B6,F6 will show 25-07-17 if the workbook is opened & data is entered, otherwise remain blank.
The day workbook will be opened & data will be entered B6 & F6 will show that day. In this way the sheet will be continued for this month. similarly next sheet will carry the data for the month of august.

Regards
TPR
 
It is challenge, if You cannot answer even the most simply questions!

Do You write about discussion which TPR has started:
Lock the cells once data entry done & Date condition?

Do You write about TPR's writing:
Am appending a sample file.
"B" & "F" columns are carrying Dates. I want such a formula in all cells of those columns by which when the work book will be opened on a date that particular date will be shown on the very next cell of that sheet. i highlighted yellow. But the previous day will remain same, that will not be changed.
Moreover all the data of the past days can not be changed(will be auto locked by a fixed password), even if PC date is manually changed.
In a nutshell:
1) Date will be automatically changed in the very next cells of column "B" & "F"
2) If a data is entered in any cell of any sheet of this workbook, that will be auto-locked by a pre-defined password. Only admin shall be able to modify that.
And it will be done in all sheets.

Do You write about file which can find in #6 Reply?

or what?

 
vletm said:
It is challenge, if You cannot answer even the most simply questions!

Do You write about discussion which TPR has started:
Lock the cells once data entry done & Date condition?

Do You write about TPR's writing:
Am appending a sample file.
"B" & "F" columns are carrying Dates. I want such a formula in all cells of those columns by which when the work book will be opened on a date that particular date will be shown on the very next cell of that sheet. i highlighted yellow. But the previous day will remain same, that will not be changed.
Moreover all the data of the past days can not be changed(will be auto locked by a fixed password), even if PC date is manually changed.
In a nutshell:
1) Date will be automatically changed in the very next cells of column "B" & "F"
2) If a data is entered in any cell of any sheet of this workbook, that will be auto-locked by a pre-defined password. Only admin shall be able to modify that.
And it will be done in all sheets.

Do You write about file which can find in #6 Reply?

or what?
Click to expand...
yes
 
I have tried to get answers ... #4, #6, #8
Do You know what is an answer?
Did You test that file or not?
If tested then answer is Yes,
if not tested then answer is No.
What do You mean 'all the cells are locked'?
... There are locked only cells which You have wanted!
+ those the newest date!
Do We try to write about same file?
What do You mean 'how entry will be done?'
... Did You even try to entry something?
... It would be good that Your ... something ... wants something as nutshell #2.
It is challenge if You cannot answer even the most simply questions!
Or
did You change all rules?
 
Okay!
You don't know what is an answer!
You're maybe just changing rules or something testing?
You have to have clear image of this, what someone would to get.
Which should be someway connected, what has written in #1.
You haven't even try to tell, what is missing?
If rules are changing all the time ... that is a challenge!
Have You reread that #11?
Have anybody else read that?
 
You must log in or register to reply here.
Back
Top