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.


    thanks


    Karl

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


    Exit Sub



    ErrH:


    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.


    Ady

Participate now!

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