none
Using the Proper or Upper Function RRS feed

  • Question

  • Hi,

    I have several columns in a spreadsheet that I am creating that will contain only 1 letter.  I would like that letter to automatically capitalize. I don't care if I use the proper or upper functions to accomplish that but if there is no text in the cell, I want the cell to remain blank.

    Can anyone help walk me through how I can accomplish that?

    Thank you

    rick-rox

    Wednesday, October 9, 2019 7:04 PM

All replies

  • If you want the letter to be capitalized in the cell itself, you need VBA code.

    Right-click the sheet tab.

    Select 'View Code' from the context menu.

    Copy the following code into the worksheet module:

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim rng As Range
        If Not Intersect(Range("A:A,D:D,K:N"), Target) Is Nothing Then
            Application.ScreenUpdating = False
            Application.EnableEvents = False
            For Each rng In Intersect(Range("A:A,D:D,K:N"), Target)
                rng.Value = UCase(rng.Value)
            Next rng
            Application.EnableEvents = True
            Application.ScreenUpdating = True
        End If
    End Sub

    In this example, all entries in columns A, D and K to N will be capitalized. You can change this to suit your needs.

    Switch back to Excel.

    Save the workbook as a macro-enabled workbook (.xlsm).

    Make sure that you allow macros when you open the workbook.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, October 9, 2019 7:28 PM