Restrictions in format

How do I restrict some columns of having all text in UPPER CASE, some columns 
to have text only First In Capital Letter and some columns only in lower  
case??

Thanks

JPG
0
Utf
4/27/2010 3:07:01 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
668 Views

Similar Articles

[PageSpeed] 53

You can restrict using Data Validation but I would find that very annoying.

Example............in DV>Allow>Custom   =EXACT(A1,UPPER(A1))  will not allow
anything but Uppercase.

I would use event code to automatically change Case when anything is
entered.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    On Error GoTo ErrHandler
    Application.EnableEvents = False
    With Target
        If .Column = 1 Then
            .Formula = UCase(.Formula)
        End If
        If .Column = 2 Then
            .Formula = LCase(.Formula)
        End If
        If .Column = 3 Then
            .Formula = Application.Proper(.Formula)
        End If
    End With
ErrHandler:
    Application.EnableEvents = True
End Sub

Right-click on the sheet tab and "View Code".  Copy/paste the code into that
sheet module.

Edit column numbers to suit.

Alt + q to return to Excel.


Gord Dibben  MS Excel MVP

On Tue, 27 Apr 2010 08:07:01 -0700, juanpablo
<juanpablo@discussions.microsoft.com> wrote:

>How do I restrict some columns of having all text in UPPER CASE, some columns 
>to have text only First In Capital Letter and some columns only in lower  
>case??
>
>Thanks
>
>JPG

0
Gord
4/27/2010 5:15:08 PM
Reply:

Similar Artilces:

"Formatted Tooltip" or "Link in Tooltip"
Hi, i want to underline a line in a multiline tooltip. Is this possible? How does this work ? My collegue told me, that there is a possibility with a link, but string <A>this is a link </a> doesnt' work. Why? Thanx in advanced Detlef Here is a great tooltip control that does what you want (and more). http://www.codeproject.com/miscctrl/pptooltip.asp AliR. "Detlef" <DetlefMarsch@yahoo.de> wrote in message news:et9V21pkFHA.2916@TK2MSFTNGP14.phx.gbl... > Hi, > > i want to underline a line in a multiline tooltip. Is this possible? How > does t...

Format changing
I have some issues with an Excel file that I will try to explain. The file contains worksheets; 12 monthly progress reports, 1 data sheet, 1 information sheet, 12 comment sheets (to show progress against objectives set in the report). It is stored in the 2003 format. The reports have some text that is linked to the information sheet and the comment sheets and charts that update from the data sheet. Each report typically has 6 charts with the relevant comments. The information sheet is just to give the header information as the need for new files is reached and this information is commo...

Date format on axis in Excel 2007
I have Excel 2007 and my coworkers have Excel 2003. When they send me files with charts, the date format in Excel 2007 is shown as numbers. Anyone that has 2003 can see the correct format. I've checked the axis properties on the version I see in 2007 and it says that it's formatted as a date. What do I need to change in my settings to ensure the data is displaying correctly? -- J ...

Increasing DPI output for jpg format
I created a business card in Publisher and am trying to upload it to a Business Card site in jpg format for printing. I used save-as in Publisher and selected the jpg format. the site has informed me that the dpi is insufficient for good quality. How do I increase the quality of the image or increase the dpi resolution of the image before using the save-as option in Publisher. In Publisher it looks great but the jpg image looks lousy. Thanks, Stuart James When you right-click your creation and click save as a picture, there is an option to change the resolution just above the file...

Big background page numbers/ Can't change format of a range
Hello, I received a spreadsheet used for printing out tables of data. This spreadsheet contains two features which I would like to understand: 1) Each Excel range that is printed on a separate page contains a big page number in the background of the speadsheet: "page1", "page2" etc. This page number, however, is not printed. How is this achieved? 2) There is a gray are outside the actual data range - and I can't change the formatting of that area. The spreadsheet is not protected. Thank you Michael You're in "Page Break Preview". Click <Vie...

Road map with Chart Conditional Formating
Hi! I've tried to work through the several charts formatting techniques bu still haven�t found the answer to what seems to be a simple concept. I work at a motorway concessionaire. Our traffic comparisons are mad in relation to a reference traffic scenario, being divided in su stretches with different length. 1 THE SIMPLE WAY (NOT FOR ME, BUT FOR ANYONE I HOPE The length should be in the x axis of this chart. Only one bar would b necessary, being that a sum of the several bars (stacked bar chart? with it�s length proportional to the sub stretches length. Each bar should have a differe...

Conditional Formatting 02-15-10
I have a date field (birthdates) in column B In column A I do math on that date field to arrive at age (Today()-C3)/365) - (Not very accurate but works for my needs) I would like Column A to be conditionally formatted based on the Date (month and day) in Column B. I want the text to turn RED if we are approaching X number of days before that date (say 45). How can I do this? Thanks Dave On Feb 15, 8:13=A0am, "Dave" <dave@accessdatapros> wrote: > I have a date field (birthdates) in column B > In column A I do math on that date field to arrive at ag...

Conditional Format to highlight rows
Column A is not formatted as a date but does contain the year and month as 2010 03. How can I use Conditional Formatting to highlight that row when it is the current month and current year? thanks to all... Use the formula =AND(--LEFT(A2,4)=YEAR(TODAY()),--RIGHT(A2,2)=MONTH(TODAY())) as your condition. Or =AND(--LEFT($A2,4)=YEAR(TODAY()),--RIGHT($A2,2)=MONTH(TODAY())) if you want to highlight the whole row. -- David Biddulph "Jack Deuce" <stephen.moore@REMOVETOREPLYipa.net> wrote in message news:ripko51hnsc1sh8n1i126laq9vmtmnkon0@4ax.com... > Colu...

PivotTable question
Hi Am newbie with PivotTables & having annoying problem with dat formats. My raw data table has dates entered in mm/dd/yyyy which I have formate cells to show as mmm-yy. However in the Pivot Table, how do I get the dropdown list for th dates to show mmm-yy instead of the mm/dd/yyyy format? I need to get data by selecting months, not each individual date. For some strange reason for various tables, sometimes the dropdow list seems smart enough to change all the dates to mmm-yy format by itself other times it lists all zillion & ten dates in the mm/dd/yyyy format Can't figure ...

Turning off formatting
Hi, Is there anyway to turn off formatting so when I enter something in a cell excel will not alter it? Formatting the cell as text beforehand sometimes works, sometimes doesn't. So that's no good. I enter a lot of football scores such as 1-1 which excel insists on converting to a date! Then I have to reconvert back to scores and it is time consuming! If you're entering something that excel thinks is a date in a cell formatted as General, then excel will parse it as a date. I've never seen it do anything bad to entries I wanted treated as text when I preformat the...

Print cells that meet conditional formatting criteria
Can you take a sheet that you have applied conditional formatting too, and either create another sheet, or just print the cells that are red for example. Is it possible, yes. Is it easy, no. Chip Pearson has some nice code at: http://www.cpearson.com/excel/CFColors.htm Put your thinking cap on! I think it's usually much easier to duplicate the conditional formatting formulas in helper cells. Then inspect those helper cells for anything you want. Roundy wrote: > > Can you take a sheet that you have applied conditional formatting too, and > either create another sheet, or...

Is it possible to format a PSObject with a .ps1xml file?
This might be a totally ridiculous question...but here goes anyway: I have a couple of filters in modules that write PSObjects to output using code like: $x = New-Object PSObject -Property @{ one = "one" two = "two" } Write-Output $x There's just way more properties. How does one specify 'default' formatting styles for a System.Management.Automation.PSCustomObject like you can with other types using .ps1xml files? That may not even be the right question to ask. What I'd like would be a way to define the default formatting for ...

Can't Change Date Format
Hi I am working with MS CRM v3.0. When I try to change the date format to dd/mm/yyyy the organisation settings do not save. This is the case when changing any organisation settings. I click OK, but when I go back in the settings are as they were before. I really need to change some organisation settings, anyone know what's wrong here? Thanks Yeah I am getting the same error, any help on this would be greatly appreciated. Ozan. "Oolybel" <Oolybel@discussions.microsoft.com> wrote in message news:239A183B-6C39-478A-82F1-ED9EFCF5290A@microsoft.com... > Hi > ...

email format is changed from HTML to Text
I haven't done any research on this, I hope someone has the answer. Sometimes when I reply to a message, the email format is changed from HTML to Plain text automatically, It does't happen when I am writing a new email, it is just sometimes when I am replying. Are you sure that the message to which you are replying is HTML and not just plain text with foreign encoding? For example, I set the encoding for this message to Turkish, but it is still plain text. It just doesn't appear that way (to me anyway, and possibly also you). Ray at work "Alex Perez" <aperezny@h...

Send payment slip to email in PDF format
Dear all, I have a report called payment slip. Every employee get this report monthly. I want my system can send this report to their email address in PDF format and the PDF file is protected by password. Please give me any guidance how to do it. Thanks. Chlaris In general I would suggest you use something like http://www.lebans.com/reporttopdf.htm to convert your report to a PDF. However, I do not believe (and I could be wrong) that it does not allow password protection of the document (which really is a little overkill in most cases). that said, if you absolutely require the pas...

shortcut to center text other than format toolbar?
Is there a short cut ( CTRL + Something) that will allow you to center text? Thank you. Check out "Keyboard Shortcuts" in Excel Help. I don't remember what it is in Win XL (by default in MacXL it's CMD-e, but it's customizable). In article <8BAB75F0-3AE3-4DEF-BCE8-F658CA8FDABE@microsoft.com>, rdavia <rdavia@discussions.microsoft.com> wrote: > Is there a short cut ( CTRL + Something) that will allow you to center text? > Thank you. rdavia, as you know there is a button on the tool bar, with the key board, not so short, Atrl+1, Alt+h, down arro...

prevented fron opening links because of restrictions set on compu.
when i try to open a link from outlook i receive a message saying i'm prevented from opening because of restrictions set on the computer and to contact system administrator. its a home pc. Help please??? denis.t wrote: > when i try to open a link from outlook i receive a message saying i'm > prevented from opening because of restrictions set on the computer and to > contact system administrator. its a home pc. Help please??? And what did you find when you expended a small effort to do a Google search on the web and in this newsgroup BEFORE asking? http://www.google.com...

Conditional Formatting
I'm trying to conditionally format a cell so that if it contains what cell $K$5 says, then it will be colored a certain color, if it contains what cell $L$5 says, it will be colored another color...but to be colored it can contain more than what is in K5 and L5, as long as K5 and L5 are in it somewhere. Make sense? Help? Thanks! It sounds like these cells contain text? If so, you need to use the =FIND() formula in the conditional formatting conditions, change it from "Cell Value is" to "Formula is" If you give examples of the data, we can be mor...

format cells #6
I have solved this so it is just my curiosity at play here. Using someone else's sheet, I entered a formula (=D235/80) into a cell expecting a numeric result not realizing that the cell was formatted as Text (so I got to see the formula as text). So I changed the cell format to Number with 4 places. To my surprise, the cell still displayed the formula. I looked for a leading ' or space but there was none. Retyping the formula produced the result I expected in the first place. What is going on? TIA Len ______________________________________________________ Changing the forma...

Can I have Paste Link copy formating?
I have a macro (Excel 2003) that paste links into another worksheet, however, the formating of the font (size and color) does not come across. Sheets("Highlights").Select Range("$AR$2:$AR$2").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Sheets("Summary").Select Range("J2").Select ActiveSheet.Paste Link:=True I noticed that if I do this manually, it works( color comes across). Is there a special form of the paste link command to use? Pasting the link essentially builds a formula that points...

Changing formats in cells
I am opening a text(tab delimited) file in Excel. When I enter a number with hyphens in it, Excel automatically changes it to a date. I have reformatted the cells many different ways, but everytime I try to save the file, the numbers get converted back to dates!!! What can I do? Hi, YOu can pre-format the cells to Text. or prefix the data with an apostrophe. jeff >-----Original Message----- >I am opening a text(tab delimited) file in Excel. When I enter a number with hyphens in it, Excel automatically changes it to a date. I have reformatted the cells many different ways, but ...

date formatting BUG for CRM !!!!!!!!!!!!!!!!!!!!!1
When I use case customization, and want to display the datetime of createdon instead of date, I click formatting tab, the Display Type dropdownlist only has ONE item Date Only. Thus I cannot format it to Date and Time like mm/dd/yyyy hh:mm Is this a microsoft bug ? Afraid this is "as designed". Matt Parks MVP - Microsoft CRM ---------------------------------------- ---------------------------------------- On Fri, 28 Jan 2005 12:17:05 -0800, "Drew" <Drew@discussions.microsoft.com> wrote: When I use case customization, and want to display the datetime of created...

Format Problem 02-09-10
Two part question. First, I have the following code in a form. Testing it in debug mode, this is what I get. ? format(datepart("yyyy", now()),"yy") 05 How come I am getting output of 05? Today is February 10, 2010. Shouldn't I be getting 10? Where this is leading to is below. (Second part of question, related partially I think to problem outlined above.) Me.JulianExpirationDate = Format(DateAdd("yyyy", 5, Now()), "dd/mm/yyyy") ? me.JulianExpirationDate 09/02/2015 Me.JulianExpirationDate = Format(Year(Me.JulianExpirationDate...

Excel 2007: cell shading (not conditional formatting)
cell A1 is shaded green. I want cell C1 to be shaded the same color as A1. When cell A1 is changed to yellow, cell C1 should also change to yellow. Help? select a1 and then do hold ctrl key down when you click C1. this called a discontiguous slection or something like that. now when you change a1, c1 also change because it also selected. if maybe you want someway link theses formats together so change to one is automatic to other i think that not possible. except if you decide to use conditional formats with both cell have same condition. "youngst2010" <y...

Time formatting
I need to be able to right a conditional Formatte that can highlight a cell (group of cells) in a sheet based on the time of day ... For instance... if the starting time (cell C4) is 02:29 and the ending time (cell D5) is 04:30 then any time with in this range the cell should be high lighted. Any other time the cell is not. Scott S. A. Stone wrote: > I need to be able to right a conditional Formatte that can highlight a > cell (group of cells) in a sheet based on the time of day ... For > instance... if the starting time (cell C4) is 02:29 and the ending time > (cell D5)...