Solved

Set default text format

Posted on 2013-01-27
9
430 Views
Last Modified: 2013-02-09
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.
0
Comment
Question by:RadhaKrishnaKiJaya
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 21

Expert Comment

by:Tapan Pattanaik
ID: 38825675
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
 
LVL 2

Expert Comment

by:LifeN-Ti
ID: 38825677
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
 

Author Comment

by:RadhaKrishnaKiJaya
ID: 38827382
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 2

Assisted Solution

by:LifeN-Ti
LifeN-Ti earned 250 total points
ID: 38828597
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
 

Author Comment

by:RadhaKrishnaKiJaya
ID: 38828866
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
 
LVL 48

Accepted Solution

by:
PortletPaul earned 250 total points
ID: 38849483
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 38866961
Hi, are your worksheet formatting and data validation needs solved?
0
 

Author Comment

by:RadhaKrishnaKiJaya
ID: 38871954
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
 

Author Closing Comment

by:RadhaKrishnaKiJaya
ID: 38871958
Thank you guys for your help.
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will show you how to use shortcut menus in the Access run-time environment.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question