Results 1 to 13 of 13
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Controlling Text Entered into a Cell (2002/2003)

    Is there any way I can control the entering of data in a cell to ensure all data is uppercase.

    I know I could use a UPPER formula in another cell referencing the input cell but I thought conditional formatting might be able to handle it?

    Does anyone have any suggestion on the best way to handle this?

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Controlling Text Entered into a Cell (2002/2003)

    The only way I can think of is either by using an event macro or by using Data, Validation.

    The latter:

    In the validation window select Allow: Custom
    Write this formula (assuming cell A1):

    =NOT(ISERROR(FIND(A1,UPPER(A1))))

    And fill in appropriate error messages and such in the rest of the dialog.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Controlling Text Entered into a Cell (2002/2003)

    Excellent , thanks for that Jan it now provides an error whenever a lowercase value is entered. Is there any way I can automatically covert the value to UPPERCASE. I am not familiar with an event macro so dont know of the limitations or capabilities?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Controlling Text Entered into a Cell (2002/2003)

    You can do the automatic conversion as follows:
    - Decide which cells should be converted automatically. In the example below, I have used A1:B20; adapt as needed.
    - Right-click the worksheet tab and select View Code.
    - Copy or type the following code into the window that appears:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    Application.EnableEvents = False
    If Not (Intersect(Target, Range("A1:B20")) Is Nothing) Then
    For Each oCell In Intersect(Target, Range("A1:B20"))
    If Not oCell.HasFormula Then
    oCell = UCase(oCell)
    End If
    Next oCell
    End If
    Set oCell = Nothing
    Application.EnableEvents = True
    End Sub

    - Switch back to Excel.

  5. #5
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Controlling Text Entered into a Cell (2002/2003)

    Hans,

    I think he wants the characters converted to uppercase as they are entered. (Nothing could be entered in lowercase).
    Or, I may be confused , as usual.
    Chuck Reimer
    I'm from the Government and I'm here to help...

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Controlling Text Entered into a Cell (2002/2003)

    I'm afrraid I don't know how to monitor typing in a cell or in the formula bar.

  7. #7
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Controlling Text Entered into a Cell (2002/2003)

    Hans,
    I attached a worbook with a very crude (NOT pretty) macro that has limited ability to do what I think they want. I know it could be improved.

    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Controlling Text Entered into a Cell (2002/2003)

    Chuck, your workbook uses the Worksheet_Change event, just like the code I posted higher up in this thread in <post#=389147>post 389147</post#>. Like my code, it only converts the cell value to upper case when the user confirms the entry, not while (s)he is typing. But my code doesn't have the restrictions yours has.

  9. #9
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Controlling Text Entered into a Cell (2002/2003)

    Hans,

    --------------------------
    I think he wants the characters converted to uppercase as they are entered. (Nothing could be entered in lowercase).
    Or, I may be confused , as usual.
    --------------------------
    I was confused! <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

    Obviously I did NOT read your post well enough to understand it. I was thinking you wrote a macro that would make the changes to the specified area after the entering was done. I really need to slow down and read the replies better!

    Well, there is always the next time....

    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

  10. #10
    Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Controlling Text Entered into a Cell (2002/2003)

    Hans

    Thank you so much that worked perfectly.

    This forum is fantastic as I have been searching through help and everywhere to find out if this was possible.

    Thanks for taking the time to help.

    Regards
    Adam

  11. #11
    Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Controlling Text Entered into a Cell (2002/2003)

    Hans

    Since I have run this I cannot undo the previous/all entries - does the macro stop this from happening? (either by using the Ctrl-Z short cut or the icon.)

    Also is it possible to select cells in different parts of the worksheet (i.e A1:B20, D120, G1:I20) as there are some cells that I still want to allow values as entered but UPPERCASE the selected cells.

    Thanks again in advance,

    Adam

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Controlling Text Entered into a Cell (2002/2003)

    1. Each time you run a macro in Excel, the undo buffer is cleared. Since the Worksheet_Change code is run each time you enter something in a cell, undo has effectively become disabled.

    2. You can make the code work on different areas as follows:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range, oRange As Range
    Application.EnableEvents = False
    ' Adapt the range as needed.
    Set oRange = Range("A1:B20,D120,G1:I20")
    If Not (Intersect(Target, oRange) Is Nothing) Then
    For Each oCell In Intersect(Target, oRange)
    If Not oCell.HasFormula Then
    oCell = UCase(oCell)
    End If
    Next oCell
    End If
    Set oCell = Nothing
    Set oRange = Nothing
    Application.EnableEvents = True
    End Sub

  13. #13
    Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Controlling Text Entered into a Cell (2002/2003)

    Thanks again Hans works like a charm

Posting Permissions

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