How do I 'lock' a cell so format cannot change?

Topic

New Reply

Hi

I would like to lock a cell so that whatever data is input it ends up in the same format.

We would like the cell to display data as dd/mm/yyy – I have selected this is the Format Cells option but if someone types in dd.mm.yy it accepts it and I would like it to change it automatically to dd/mm/yyy.

Hope that makes sense?

Can anyone offer me any advice on this please?

Thanks

Penny

Reply | Quote

Replies

You could run a macro on cell change that re-formats the cell? The problem is determining what the user meant to enter – dd/mm/yyyy, mm/dd/yyyy, yy.mm.dd, etc

cheers, Paul

Reply | Quote

To lock a cell so the format can not be changed, requires that the cell be locked so the value can not be changed either.

But the problem you mention is not formatting change at all. The format remains for dates the format you chose. The problem is that excel does not recognize the entry using periods as a date but text. Entries that excel can convert to dates it will and then will have the format you have on the cell. Things that do not look like dates will be text.

You could create a macro to convert text or numbers to dates. Some example code can be found at MS MVP Chip Pearson’s website: http://www.cpearson.com/excel/DateTimeEntry.htm

Steve

Reply | Quote

Thanks for your replies Uranium and Bronze Lounger

I’m wanting to set the spreadsheet up so that any text input into the spreadsheet by any user is automatically altered to our requirements, rather than a macro having to be run to amend after the event.

Going on what you say – I’m not sure this is achieveable

Thanks

Penny

Reply | Quote

Going on what you say – I’m not sure this is achieveable

I think this is correct. If you use code or a macro to manipulate the text in the cell and create a date from it, I doubt that the end result will be accurate.

If someone types a recognizable date it will be switched to your date format, but if they type the date as text, sometimes with the year first (yy.mm.dd or yyyy.mm.dd or yy.m.d, etc.) and sometimes with the month first (mm.dd.yy or mm.dd.yyyy or m.d.yy, etc.) you will have a very hard time converting it to the correct date…..

Reply | Quote

Going on what you say – I’m not sure this is achieveable

I would say it is achievable via training. Train the users to enter the dates properly and they will be converted properly.

If your users can not be trained to consistently enter the dates into one cell, you could have them use multiple cells. Have pulldowns for the year, month, and (based on year and month) the number of days in that month. Then from these numbers you could calculate the day…

Steve

Reply | Quote

Penny,

Have you looked at Data –> Validation? You can use this to force date input while providing a prompt for the user to show the correct format. You can select a whole range of cells then enter the validation parameters and it will apply to all. If you later insert a row within the range the validation applies to it will also apply to the inserted row.

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

FWIW, I agree with RG. By using validation, when they roll over the cell, a note will pop up and in that note you can show them the format that they need to use.

Reply | Quote

Retired Geek!!! That’s fab! I’ve done it and I think that will provide the ‘training’ that others refer to!

Marvelous…

Thanks once again to this forum!!

Penny

Reply | Quote

Hey There,

Data Validation is great when you want to provide some easy guidence to users, and has some useful ways of setting rules for what data is acceptable.

Just keep in mind that Data Validation doesn’t prevent people copying and pasting data that isn’t valid onto those cells.

Bit of a flaw in my opinion.

Love these forums,

Paul.

Reply | Quote

Paul:

I have had the same copy issue that you described when using Validity to set data entry into a cell. Someone can copy a cell or cells and overwrite the Validity check. Does anyone out there know of some way to allow pasting of cells, but enforce Validity checks? This is a legitimate issue when using excel for consistent information gathering within organizations. Excel is easy and most individuals can use it on a basic level, but they do not understand the product to a depth to understand that the paste function can get them into trouble.

Reply | Quote

No input validation can tell if “08/09/10” is a correct date. Is it DD/MM/YY or MM/DD/YY or even YY/MM/DD? Therefore ALWAYS ask for day, month, year in separate cells, then validate each field and build the date value in a locked cell. Use another locked cell to display a warning if invalid data is entered. Remember your validation depends on the context – 1980 is a valid date of birth but an invalid date of death.

Another thing to bear in mind if your spreadsheet is shared between users is that many users have “incorrect” localisation for the default date format. Always specify a format when converting dates to strings.

Reply | Quote

As for enforcing validity checks, if your check is done using a list in the spreadsheet use a value returned from a VLOOKUP on the list rather than the value entered. If the user circumvented your validation the result will be #N/A

Reply | Quote

How about a pop-up calendar. The attached spreadsheet describes how to do this in Excel 2003. Not sure about 2007. In the code, you have to format the column to the date format you want.

Reply | Quote

Just a tip for data entry Ctrl + ; enters the current date.
This is often useful to “orient” oneself as to the default date format used by the machine.

Reply | Quote

Just keep in mind that Data Validation doesn’t prevent people copying and pasting data that isn’t valid onto those cells.Bit of a flaw in my opinion.Love these forums,Paul.

No input validation can tell if “08/09/10” is a correct date. Is it DD/MM/YY or MM/DD/YY or even YY/MM/DD?

How about a pop-up calendar.

Valid points all! The trick is to balance the ability and understanding of your users with the time and money constraints on developing the application. I used to have a customer who could break his application no matter what I did including:

Hiding all the Excel menus and only displaying a custom menu with his options all implemented in VBA.
Turning off the Big Red X
Locking all the cells and gathering/validating all input via dialog boxes.
Setting Ignore Remote Requests to keep him from opening a second copy of the app after he minimized the 1st instance.
Etc. Etc. Etc.

Remember the basic rule of application development 90% of the app takes 10% of the development time while the other 10% of the app {error/exception processing} takes the other 90% of the development time. Y%MV

And of course there is no way to keep them from entering 8/8/10 when them wanted 8/18/10 or 51.27 when they wanted 51.72 etc. It all comes back to GIGO.

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote