Announcement

Collapse
No announcement yet.

Force a cell to show entered text in Upper case

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

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

  • #2
    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])
    Matt B

    Comment


    • #3
      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

      Comment


      • #4
        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)
            If Intersect(Target, Range("A1:A100")) Is Nothing Then Exit Sub
            
        Application.EnableEvents = False
        Target = UCase(Target)
        Application.EnableEvents = True
        
        End Sub

        Comment


        • #5
          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

          Comment


          • #6
            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

            Comment


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

              Comment


              • #8
                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

                Comment


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

                  Code:
                   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.
                  Regards,
                  Batman.

                  Comment


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

                    Thanks Batman - this seems to be working fine.

                    Comment


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

                      Code:
                       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.[/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

                      Comment


                      • #12


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

                        [email protected],
                        Welcome to the forum
                        Can you please start your own thread and reference this one if it is relevant to your question.
                        If the solution helped please donate to RSPCA

                        Sites worth visiting: Rabbitohs | Excel-it royUK | Excel Matters Rory | Kris' Spreadsheet Solutions | Domenic xl-central | SO The Macro Man | The Smallman

                        Comment

                        Working...
                        X