Set default text format

Hi Experts,
Is there a way to set the default format of few cells of worksheet to Text ot Number or Date? So that whenever it should stay there permanent and I would not have to chage it at all?

Thank you in Advance.
RadhaKrishnaKiJayaAsked:
Who is Participating?
 
PortletPaulfreelancerCommented:
For example, A1 can accept only numbers, nothing else.  A2 can accepts only Dates, nothing else.  Here, user can enter data, but not change the data type.  

I believe you need to define this input area as a "table", which will enable you to maintain a consistent format per column.

See Quick Start: Create an Excel Table (Excel 2010)

or: Setup Database in Excel (youtube) note: the presenter makes a data entry error when trying to use automatic calculations from the table - this is actually a good thing to know :-)

For data validation refer to this http://office.microsoft.com/en-au/excel-help/overview-and-samples-of-data-validation-HA001034657.aspx (although this is Excel 2002)
or a more recent video (Excel 2010)
0
 
Tapan PattanaikSenior EngineerCommented:
Hi RadhaKrishnaKiJaya,

Please  check these links.

Setting Up Excel Default Formatting:

http://www.pchelpsblog.com/2011/03/productivity-101-setting-up-excel-default-formatting/

http://www.meadinkent.co.uk/xlconfigure.htm
0
 
LifeN-TiCommented:
Hi,
Are you referring to in a saved worksheet that you utilize or overall in excel when you make a new workbook those cells are always that format?

If you're just talking about working within a specific worksheet right click the cell, go to format cells, and select the format you'd like it to default to under Categories:) if that's not what you meant just let me know, take care!
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
RadhaKrishnaKiJayaAuthor Commented:
Thank you LifeN-Ti and tapanpattanaik for your help.

tapanpattanaik, your links are very informative.  Thank you again.

LifeN-Ti, I am trying to make changes on a existing worksheet (saved worksheet).  Yes, we can make changes by right click --> Format Cells etc.  Here user can modify it.  Here what I am looking for is to make the cell permanent.  Even if user tries to modify, it should not work.  In other words, to make the chages permanent.

Can we code it in ThisWorkBook (hit Alt + F11 key, you would see it), so that it should maintain the default format permanently?  Please let me know.  Thank you.
0
 
LifeN-TiCommented:
Hi RadhaKrishnaKiJaya,
No problem! And ahhhh I see!

You want to protect the workbook and lock the cell so it can't be modified aside from changing the value inside (they can't change the format/delete it/etc only change the value). You can do that by going under Format Cells, then going to the "Protection" tab and selecting "Locked" - once you do that you then have to protect the workbook (review tab) as it mentions how under the locked option, once you protect the workbook and the cell is locked it cannot be modified with exception to the value of the cell. Is that what you meant? So they can't change the category even if they wanted to? If I misunderstood I apologize, just let me know:)
0
 
RadhaKrishnaKiJayaAuthor Commented:
Thank you LifeN-Ti, this is really helpful.  I tried your instructions which protects the whole worksheet.  Infact, I don't want to protect the worksheet altogether as the users need to update it frequently.  Instead, I just want protects the format of the cells.  For example, A1 can accept only numbers, nothing else.  A2 can accepts only Dates, nothing else.  Here, user can enter data, but not change the data type.  I know we can achieve it using simple VBA coding inside ThisWorkBook.  But I don't know how.  Please let me know if you have any idea.  Thank you.
0
 
PortletPaulfreelancerCommented:
Hi, are your worksheet formatting and data validation needs solved?
0
 
RadhaKrishnaKiJayaAuthor Commented:
Hi PortletPaul, Thank you for the post.  They are very helpful.  I did some temporary fix manually.  Here I was looking for some kind of global macro, which should do it automatically.  For example, the worksheet can read ThisWorkBook file upon activating the macro (hit Alt + F11 key).  Thank you.
0
 
RadhaKrishnaKiJayaAuthor Commented:
Thank you guys for your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.