Force a cell to show entered text in Upper case

  • 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])

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


    albelina,


    Try adapting this bit of worksheet code:

    Code
    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
        If Target = Range("a1") Then Target = UCase(Target.Value)
    End Sub

    If I've been helpful, let me know. If I haven't, let me know that too. 

  • 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.

    Code
    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
  • 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

  • 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

  • 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

  • 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:



    Hope this helps.


    Regards,
    Batman.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

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



    Hope this helps.


    Regards,
    Batman.[/QUOTE]



    Batman,
    I want to use this code to do something slightly different. Can you help me please?


    I want to force a cell within the range I set to default to Tahoma size 8 font in black text. How can I add these bits to the code above please?


    Thanks and have a great day.


    Mark

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!