Force characters to upper case as text is typed into cell

  • All,

    Maybe this isn't possible, I have looked around and I keep finding this method:

    Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If Not .HasFormula Then
            .Value = UCase(.Value)
        End If
    End With
    End Sub

    However, the column I am working in (Column H) is limited in size, thus I have typed information in the cell, upper cased it and then found out my narrative was too long for the cell, after it was converted to Upper Case.

    I can simply hit, Caps Lock, but this needs to be dummy proof for others. We have had others fill out the form, all in lower case or proper case and then I had to go in and edit the entryies. So I thought, take the problem out, auto convert the text.

    The above will (sometimes) convert the text after you leave the cell, this is close to what I need, but would rather know how much room I have as I type.

    Ideas? Thanks in Advance.

  • Re: Upper Case Cell as Typing Occurs


    Please revise your thread title to define the objective, which seems to be this:


    but would rather know how much room I have as I type.

    What is it you want to accomplish? What do you mean by "how much room is left"?
    Test if text length exceeds cell width/height?
    Restrict text length to X characters?

    If your cell width/height is set to fixed size, there is no way, AFAIK, to determine how much room is left other than comparing text length to max allowed characters in a cell.

    State your requirements more precisely.

  • Re: Upper Case Cell as Typing Occurs


    My post is asking how to make the cell change to upper case as I type. Is that possible?

    The second thing of this happening is how much room is left to type, which I will know when it does the first, no matter if you have cap locks on or not, it will upper case each letter as you type it, not after you leave the cell or not at all in the case of the "On Change" worksheet event I posted.

    So the Title *is* what I am after, once that happens the second desire (knowing how much room is left as I type) is apparent. I could changed the end of it to "as you type." I do see how the length can be seen as the ultimate goal, however it's really Upper Case as I am Typing in the Cell.

    The column width is 44.14, I just need to automatically convert what is being typed to upper case, with out having to rely on someone doing it right and typing it all in caps... I have to fix so many entries, so looking for a way to fix it.

  • Re: Upper Case Cell as Typing Occurs

    I've got an idea with custom events and KeyPress, I'll have a go at it when I get home.

  • Re: Upper Case Cell as Typing Occurs

    What you want is the sheet equivalent of a KeyPress/KeyDown event on a userform or control like a textbox - These do not exist on a worksheet, and additionally, no code can execute while Excel is in Edit mode... my guess is you're stumped. Sorry.

  • Re: Upper Case Cell as Typing Occurs


    I was worried about that.. well not worried, but figured.. I knew I could do it via userform/textbox input, but this particular spreadsheet is sheet based (for the most part) Thanks!


  • Re: Force characters to upper case as text is typed into cell

    You could try to ensure that caps lock is on by using sendkeys... but that's not 100% sure to work... Just throwing that in there.


Participate now!

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