Entering data in upper case

Topic

New Reply

I have been asked a question to which I don’t have an answer with the exception of the obvious. However as a`five-eighths’ I thought I might venture here to ascertain if there is an answer.

Without recourse to the keyboard or the mouse can an Excel worksheet and/or workbook be configured so that all text entries are in upper case? And if so how? Something to set the Caps Lock to On when the worksheet is opened and Off when it is closed come to mind. Unfortunately this is beyond my abilities.

Assistance would be very much appreciated,

Reply | Quote

Replies

You’d have to toggle the Caps Lock key when the user switches from the workbook to another application or back too – you wouldn’t want all your text in Word to be in upper case too, or the password you enter for a website.

But it wouldn’t be a foolproof solution – users can turn off Caps Lock, or hold down the Shift key while entering text.

Perhaps you should reconsider the goal – why would anyone want all text in a worksheet to be in upper case? It’s exceedingly ugly, and the time of mainframes that only support text in upper case is 30 years or more behind us…

Reply | Quote

Perhaps you should reconsider the goal – why would anyone want all text in a worksheet to be in upper case? It’s exceedingly ugly, and the time of mainframes that only support text in upper case is 30 years or more behind us…

I should make it clear that it is not my goal but that of a third party. For what it is worth I entirely agree with the sentiments you express about the use of upper case text. But the third party is immune to them.

Thank you for your kind and courteous response.

Reply | Quote

You could use VBA code but it has a serious disadvantage: it will disable the undo feature, so it’ll be more difficult to correct mistakes.

– Right-click the sheet tab.
– Select “View Code” from the popup menu.
– Enter or copy the following code:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim oCell As Range
  Application.EnableEvents = False
  For Each oCell In Target
	If Not oCell.HasFormula And Not IsNumeric(oCell) Then
	  oCell = UCase(oCell)
	End If
  Next oCell
  Application.EnableEvents = True
End Sub

– Switch back to Excel.

See the attached sample workbook.

Reply | Quote

You could use VBA code but it has a serious disadvantage: it will disable the undo feature, so it’ll be more difficult to correct mistakes.

See the attached sample workbook.

Hans,

Thank you – disadvantage noted. I will pass this on with a suitable acknowledgment of your assistance.

Malcolm

Reply | Quote

You could use VBA code but it has a serious disadvantage: it will disable the undo feature, so it’ll be more difficult to correct mistakes.

– Right-click the sheet tab.
– Select “View Code” from the popup menu.
– Enter or copy the following code:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
	 Dim oCell As Range
	 Application.EnableEvents = False
	 For Each oCell In Target
	   If Not oCell.HasFormula And Not IsNumeric(oCell) Then
		 oCell = UCase(oCell)
	   End If
	 Next oCell
	 Application.EnableEvents = True
   End Sub

– Switch back to Excel.

See the attached sample workbook.

Hello Hans

Is there any technique which will display dates in upper case (FRIDAY AUGUST 14, 2009)?

Reply | Quote

There is no date format that displays the names of days and months in upper case, so you’ll have to use a workaround.

1. Convert the dates to text strings, e.g. by inserting an apostrophe in front of them:

‘FRIDAY, AUGUST 14

This has a serious disadvantage: since the values aren’t real dates any more, you cannot use them in calculations directly (but you can use the DATEVALUE function to convert the strings back to dates).

2. Enter dates in one column, and formulas of the form =UPPER(TEXT(A1,”dddd mmmm d, yyyy”)) in the next column.

3. Use a font that has only upper case letters such as Perpetua Titling MT. Disadvantage is that such fonts are not “standard”, but on the other hand they convert everything to upper case without needing any code.

Reply | Quote

You could have one worksheet (“ActiveSheet”) which is used for all the data entry, formulae etc and another (fully protected) worksheet (“DisplaySheet”) with each cell filled correspondingly

=IF(ISTEXT(‘ActiveSheet’!A1),UPPER(‘ActiveSheet’!A1),’ActiveSheet’!A1)

It won’t capitalise dates and special formatting will have to be reapplied, but it may be what the client is looking for.

Not nice though

Reply | Quote