Results 1 to 7 of 7
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Can a range of cells be set to be automatically uppercase text regardless of what is typed -- without running a macro to convert the text or using data validation to insist they enter in uppercase?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Not really - you'd need VBA code to do that - but you could let the user enter text in a range of cells, and use formulas with the UPPER function to display the text in upper case in another range.

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Can code in VBA force a range of cells to be uppercase?

    [quote name='HansV' post='776010' date='19-May-2009 20:44']Not really - you'd need VBA code to do that - but you could let the user enter text in a range of cells, and use formulas with the UPPER function to display the text in upper case in another range.[/quote]

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Right-click the sheet module.
    Select "View Code" from the popup menu.
    Copy the following code into the module:

    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 Application.IsText(oCell) Then
    	  oCell.Value = UCase(oCell.Value)
    	End If
      Next oCell
      Application.EnableEvents = True
    End Sub
    Warning: this code will disable the Undo feature, so users cannot easily correct mistakes.

  5. #5
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='776017' date='20-May-2009 01:10']Warning: this code will disable the Undo feature, so users cannot easily correct mistakes.[/quote]

    Sorry for butting in here:

    Has this been addressed in 2007? From experience, I find that any additional feature that disables the UNDO function, adds less value than UNDO. I have backed out of so much code that disables UNDO, as I find it too big a loss to suffer. I know that there is a very complex way of getting around this, but when I looked, it was way too difficult for me.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    No, Excel 2007 does not let the user undo a macro action either.

    Microsoft might be working on a solution for a future version - see Excel’s VBA Undo Problem.

  7. #7
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='776562' date='23-May-2009 11:39']No, Excel 2007 does not let the user undo a macro action either.

    Microsoft might be working on a solution for a future version - see Excel’s VBA Undo Problem.[/quote]

    If Word can undo a VBA macro, why can't Excel?

    Maybe it will in the future. I found this patent, filed by Microsoft in 2004 and issued last month <Aug 08>: Method and system for enabling undo across object model modifications. It's all very complicated and not too specific, but this figure gives me hope that Microsoft might be working on the problem:
    aaaaaaahhhhhhhhh, so maybe there is something to look forward to. Do these things actually take sooooo long, patent filed 2004, maybe my grandchildren will benefit. I'll be able to sit 'em on my knee and tell them all about the days that we could'nt undo after a ..........

Posting Permissions

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