Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 10 of 10

Thread: Force a cell to show entered text in Upper case

  1. #1
    Join Date
    30th November 2004
    Posts
    7

    Force a cell to show entered text in Upper case

    Hi

    Is there any way to format a cell in Excel so that any text entered will be shown in uppercase? The cell will be continually updated.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    4th November 2004
    Location
    London
    Posts
    35

    Re: Force a cell to show entered text in Upper case

    You could either use a font that is only a caps font or alternatively use the formula:

    =UPPER([insert text or cell ref here])

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    26th January 2003
    Location
    Indiana, USA
    Posts
    784

    Re: Force a cell to show entered text in Upper case

    albelina,

    Try adapting this bit of worksheet code:
    VB:
    Private Sub Worksheet_Change(ByVal Target As Excel.Range) 
        If Target = Range("a1") Then Target = UCase(Target.Value) 
    End Sub 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,708

    Re: Force a cell to show entered text in Upper case

    Hi albelina

    Right click on the sheet name tab and select View Code, in here paste the code below, which is set to only work on A1:A100. If you need all cells, remove the entire first line (If Intersect(Target, Range("A1:A100")) Is Nothing Then Exit Sub). If you need another area watched then change A1:A100 to the range needed.
    VB:
    Private Sub Worksheet_Change(ByVal Target As Excel.Range) 
        [I]If Intersect(Target, Range("A1:A100")) Is Nothing Then Exit Sub[/I] 
         
        Application.EnableEvents = False 
        Target = UCase(Target) 
        Application.EnableEvents = True 
         
    End Sub 
    
    

  5. #5
    Join Date
    30th November 2004
    Posts
    7

    Re: Force a cell to show entered text in Upper case

    Hi

    Thanks for your responses. I used Dave's code and it worked great.

    thanks a lot guys

    Albelina

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    30th November 2004
    Posts
    7

    Re: Force a cell to show entered text in Upper case

    Hi

    Me again!
    I have since gone back to this spreadsheet and tried this code again and it does not seem to work any more. I have repasted the code but still it is not working. If I open a new work book then it works fine. Is there some setting that I may have set inadvertantly which will stop this code from working?

    thanks

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    13th October 2004
    Posts
    53

    Re: Force a cell to show entered text in Upper case

    Hi all

    I used Daves code which works great as you enter text cell by cell but what I want to do is copy a range of cells into A1:A100 and the macro to run converting everything to uppercase.

    Currently when I try it I get Error 13 - Type mismatch thown back at me. Can anyone offer some code to handle this?

    TIA,

    Steve

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    7th June 2003
    Location
    Melbourne
    Posts
    25

    Re: Force a cell to show entered text in Upper case

    Its also possible to use Data Validation to force the future entry of text into this range as Capitals or spacea

    Select A1:A1000
    Data | Validation
    Allow..... Custom

    Formula.......=SUMPRODUCT(--((CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))>64)+(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))=32)),--(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<91))=LEN(A1)

    Cheers

    Dave

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    8th September 2004
    Location
    Northampton, England
    Posts
    2,597

    Re: Force a cell to show entered text in Upper case

    albelina,

    It may be that you have interrupted the program and that EnableEvents is currently switched off (it doesn't automatically reset when the program ends). Try the following steps:

    1) Go to the Visual Basic Editor (Alt + F11)
    2) Open the Immediate Pane (Ctrl + G)
    3) Click into the Immediate Pane and enter:
    Application.EnableEvents = True (Enter)
    4) Return to the worksheet and re-test.

    Steve,

    You need to process each cell separately. Try replacing your code with the following:

    VB:
    Private Sub Worksheet_Change(ByVal Target As Excel.Range) 
         
        Dim rngCell As Range 
         
        If Intersect(Target, Range("A1:D100")) Is Nothing Then Exit Sub 
         
        Application.EnableEvents = False 
        For Each rngCell In Target.Cells 
            rngCell = UCase(rngCell) 
        Next 
        Application.EnableEvents = True 
         
    End Sub 
    
    
    Hope this helps.

    Regards,
    Batman.

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    30th November 2004
    Posts
    7

    Re: Force a cell to show entered text in Upper case

    Thanks Batman - this seems to be working fine.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Force Text To Capital/Upper Case
    By cnl in forum EXCEL HELP
    Replies: 3
    Last Post: August 29th, 2007, 03:16
  2. Force Upper Case In Input Box
    By teelinks in forum EXCEL HELP
    Replies: 20
    Last Post: March 13th, 2007, 05:11
  3. Automatically Force Text to Upper/Proper Case
    By Dave Hawley in forum Excel FAQ
    Replies: 0
    Last Post: September 21st, 2005, 16:36
  4. Replies: 2
    Last Post: December 29th, 2004, 20:27
  5. InputBoxes Force Upper case Proper Case
    By Timbo in forum EXCEL HELP
    Replies: 14
    Last Post: October 29th, 2004, 23:28

Bookmarks

Posting Permissions

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