Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    May 2003
    Location
    Mid-Cheshire, England
    Posts
    275
    Thanks
    1
    Thanked 3 Times in 3 Posts
    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,

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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...

  3. #3
    3 Star Lounger
    Join Date
    May 2003
    Location
    Mid-Cheshire, England
    Posts
    275
    Thanks
    1
    Thanked 3 Times in 3 Posts
    [quote name='HansV' post='788801' date='13-Aug-2009 15:51']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...[/quote]

    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.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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.
    Attached Files Attached Files

  5. #5
    3 Star Lounger
    Join Date
    May 2003
    Location
    Mid-Cheshire, England
    Posts
    275
    Thanks
    1
    Thanked 3 Times in 3 Posts
    [quote name='HansV' post='788809' date='13-Aug-2009 16:32']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.[/quote]

    Hans,

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

    Malcolm

  6. #6
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='HansV' post='788809' date='13-Aug-2009 11:32']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.[/quote]
    Hello Hans

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

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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.

  8. #8
    3 Star Lounger
    Join Date
    Feb 2003
    Location
    Runcorn, Cheshire, United Kingdom
    Posts
    372
    Thanks
    0
    Thanked 2 Times in 2 Posts
    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'!A 1),'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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •