Capital letters

  • Is it easy for excel to automaticall capitalise when you type into a cell?? i tried the Proper function but not sure if i used it right. I just want to type into a cell but have all words capitalised.



  • Re: Capital letters

    I think I know what you're trying to achieve, you just want Excel to quietly validate the text you've just entered and make it upper case without any fuss?

    Seems reasonable enough but I can't think of a simple way to do this. Using a formula doesn't sound like the ideal answer because I don't think it's achieving the 'quiet validation' I (think) you want.

    You can achieve this with a bit of VBA code if you like, I was bored so here's something that worked for me... Note. You need to use a class module (so you can hook into the application.worksheet events).........>>

    >>Put this in the 'This Workbook' code window

    Dim xlWs As clsWsEvent

    Private Sub Workbook_Open()
    Set xlWs = New clsWsEvent
    End Sub

    >>Insert a class module called clsWsEvent and paste this into the code window

    Public WithEvents xlWs As Excel.Worksheet
    Private Rng As Excel.Range

    Private Sub Class_Initialize()
    Set xlWs = ActiveWorkbook.Sheets("Sheet1")

    End Sub

    Private Sub xlWs_Change(ByVal Target As Range)

    On Error GoTo ErrH

    If Not Rng Is Nothing Then

    If Rng.Address = Target.Address Then

    Exit Sub

    End If

    End If

    Set Rng = Target

    xlWs.Range(Target.Address).Value = UCase(xlWs.Range(Target.Address))

    Exit Sub


    Dim strErr As String

    strErr = "An error has occured in clsWsEvent: " & vbCrLf & Err.Description
    MsgBox strErr, vbCritical, "clsWsEvent"

    End Sub

    If you do want to use something like this and you get stuck and need some help let me know. If you want me to email you the Excel file that works let me know.


Participate now!

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