FAQ - Force Upper Case

PROGRAMMING MANAGEMENT		A resource for users and developers of Microsoft Excel	Site Map	Search


			Email us your FAQ suggestion.

			Visit often ! Pages are updated daily.
	This is the Excel "How to" Frequently Asked Questions page
Answers to Frequently Asked Questions about how to do common tasks in Microsoft Excel.

The World of Atlas

Home

Excel Solutions and Examples

Access & Excel	Add-Ins	API	Charts	Class Modules	Comments	Conditional Formatting	Control Toolbox
Data Validation	Date & Time	Dialog Sheets	Edit	Errors	Events	Excel Fact & Fiction	Excel Object
Filter	Find / Replace	Format	Forms Toolbar	Formulas	Glossary	Hyperlinks	Internet & Excel
Keyboard	Menu Bar	Mouse	OLAP Cubes	Outlook & Excel	Pivot Tables	PowerPoint & Excel	Print
Ranges	Ribbon	Sheets	Sort	SQL	Tips & Tricks	User Defined Functions	UserForms
VBA & the VBE	VBA Magic	Window	Windows	Word & Excel	Workbooks	XLM "4.0 Macros"	XML

Excel Community

Excel "How to" FAQs

List of Excel "How to" FAQs with links to their answers.

Data entry: How to...		Formulas for sum, count, lookup, last entry: How to...		Worksheets and workbooks: How to...
	...identify duplicate entries in a range?		...return the last number and its cell address?		...display the worksheet tab name in a cell?
	...prevent duplicate entries in a range?		...return the last text and its cell address?		...display the workbook name in a cell?
	...delete duplicate entries in a range?		...return the last any type entry and its cell address?		...display the workbook path in a cell?
	...force letters to be UPPER CASE?		...return a table's intersecting row & column value?		...display the workbook path and name in a cell?
	...limit how many characters can be entered?		...return the second highest or lowest number?		...display the workbook path, name, sheet in a cell?
	...establish the "carriage return" affect in a cell?		...average without zeros?		...name a worksheet tab based on a cell value?
	...fill empty cells in a column with value from above?		...sum, average, or count colored cells?		...change column headers from numbers to letters?
	...stop Excel from converting an entry into a date?		...sum, average, or count to omit Min or Max?		...verify if a certain worksheet exists; create if not?
	...automatically insert colon : when entering a time?		...sum, average, or count across multiple sheets?		...verify if a certain workbook exists?
	...enter a number with a visible leading zero?		...sum, average, or count visible or unfiltered cells?		...verify if a certain workbook is open?
			...sum, average, or count with multiple conditions?		...add more rows or columns to my worksheet?
Data manipulation, extraction, parsing: How to...			...avoid #DIV/0!, #N/A, and other formula errors?		...know how many sheets my workbook can hold?
	...separate alphanumeric numbers and letters?		...use VLOOKUP to return value in column to left?		...sort sheets by tab name in alphanumeric order?
	...separate first and last names into their own cell?				...open a workbook to a certain sheet and cell?
	...extract all to the left of a certain character?	Programming Visual Basic for Applications: How to...			...prevent workbook being renamed or distributed?
	...extract all to the right of a certain character?		...create, install, and run a macro?		...save or export my workbook as an .exe file?
	...move negative sign from back to front of number?		...create, install, and utilize a User Defined Function?		...keep rows or columns visible when scrolling?
	...sort a list by more than three column fields?		...create, install, and utilize Event code?		...make a drop-down list for users to select sheets?
	...convert numeric values to their equivalent words?		...create, install, and utilize Class Module code?		...know when the workbook was created?
	...add or subtract a day, month, or year of a date?		...force another computer to enable my macros?		...know when the workbook was modified or saved?
	...delete rows where empty cells exist in a column?		...stop the screen movements while macros run?		...open all workbooks, modify them, then close all?
	...delete hyperlinks but keep their underlying text?		...close a workbook without the prompt to save it?		...recover or undo a worksheet I wrongly deleted?
			...delete a sheet without the prompt to confirm it?		...make a workbook expire by deleting itself?
Data Validation: How to...			...save as existing name without prompt to confirm?		...convert each worksheet into its own workbook?
	...make the cell's drop-down arrow always visible?		...deal with a Macro warning when no macros exist?
	...type the first letter to select that part of the list?		...create a Message Box timed to click OK by itself?	XL Zone: Assorted questions...
	...adjust the drop-down list's size, width, or font?		...establish a "carriage return" line in my MsgBox?		Why Excel asks to save when nothing changed?
	...have more than 8 visible drop down list items?		...display a progress bar while my macro runs?		Why Excel asks to update links but there are none?
	...have one list dependent on another list?		...copy to or go to the next available row or column?		Why the 'Too many different cell formats' message?
			...locate the first empty cell in a row or column?		How do I make the Reviewing Toolbar go away?
Format: How to...			...know the last row for one or more columns?		*How do I find wildcard characters "?" and "" ?**
	...conditionally format for more than 3 conditions?		...know the last column for one or more rows?		How do I keep a button always visible on the sheet?
	...colorize the active cell, row, or column?		...know the address of the sheet's used range?		How do I autoclose a workbook after inactivity?
	...format a date when concatenating it with text?		...know the address of the sheet's data range?		How can I put a running clock on my spreadsheet?
	...make a font invisible so a cell's value is not seen?		...select a range of unknown size or location?		How do I ask for input of an unambiguous date?
	...make a cell's value flash or blink to get attention?		...fill a formula down to the last used row?		How do I customize the buttons on a Message Box?
	...format a formula's returned value characters?		...find multiple entries of the same value in a range?		How do I show a MsgBox in a corner, not centered?
	...format the sum of times for hours and minutes?		...get the user name of the computer or network?		What can I do to learn more about Excel and VBA?

How to force letters to be UPPER CASE?

Example	You are entering text values in a range and you want them to be displayed in upper case as they are entered. Examples, Hello should be displayed as HELLO, and 123abc456def should be displayed as 123ABC456DEF. You cannot count on users always having the Caps Lock set to upper case.
Format	These fonts will show character letters in upper case. Remember, formatting a cell only affects what you see. The actual value in the cell will be what was entered, which might still be lower case character letters. If you think the font appearance is worth the trade-off of no involvement by Data Validation or VBA, here are the font styles: Algerian Castellar Engravers MT Felix Titling Goudy Stout Perpetua Titling MT Showcard Gothic Stencil
Manual	Data Validation can be applied to the cells, for example range A1:A10, so only upper case letters are allowed. Select range A1:A10 and then... In Excel version 2003 or before: From the worksheet menu, click Data > Validation. In Excel version 2007: From the Ribbon, click the Data tab, and in the Data Tools section, select Data Validation. From the drop down list select Data Validation. In the Data Validation dialog where you would be now, from the Allow drop down list, select Custom. In the Formula field, enter =EXACT(UPPER(A1),A1) Click onto the Input Message tab to enter optional text, such as entry instructions, that will appear when the user selects that cell. Click onto the Error Alert tab and select "Show error alert after invalid data is entered". From the Style drop down list, select Stop, and in the Title and Error message fields, enter the text you want your users to see in the warning message that will appear when a lower case entry is attempted. Click OK to confirm your Data Validation settings.
VBA	A Worksheet_Change event can also accomplish this, still with the example of range A1:A10. Right click on your sheet tab, left click on View Code, and paste the following procedure into the large white area that is the worksheet module. Press Alt+Q to return to the worksheet. Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("A1:A10")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub Application.EnableEvents = False Target.Value = UCase(Target.Value) Application.EnableEvents = True End Sub

Top of Page

List of Excel "How to" FAQs

Home

About Atlas

Excel Consulting

Excel Training

Excel Testing

Atlas Store

Contact Atlas