Only lower case in column without formula

14spar15 · #1 08-24-2018, 07:30 AM

I’m trying to convert all uppercase letters in just one column to lowercase. I am continually pasting data into this column so I do not want to do this with a formula that would have to involve a second column. I looked into data validation but it seems it is limited to correcting at the time of data entry which won’t work for me. I tried this…

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Value = VBA.LCase(Target.Value)
End Sub

but the way it was written seems to apply to the complete sheet and not just one column. If this could be corrected (I don’t know the procedure) it may still be a problem as I find if more than one cell is being pasted at once it brings a run-time error. I was hoping that there was maybe a way to format the column but I can’t figure that out either. Any ideals here?

Pecoflyer · #2 08-24-2018, 07:43 AM

Please wrap code with code tags. Thank you

NoSparks · #3 08-24-2018, 09:24 AM

Perhaps something along the lines of

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
If target.column = 5 Then
    For Each cel In target
        cel.value = LCase(cel.value)
    Next cel
End If
End Sub

14spar15 · #4 08-26-2018, 02:57 PM

Thank you for the help here. When I first tried this I didn’t think it was working but after messing with it for a while I found out it’s just not working as I expected. In case I am using this wrong I will tell you how I inserted this code. I pressed Alt +F11 and double-click the worksheet that I want this to apply to. I changed the Target.Column = 5 to Target.Column = 3 to fit my application. Any capital letters that are added to any cell in column 3 are changed to lowercase when exiting that cell. As I was hoping for I can also paste several rows at one time and everything gets updated to lowercase. One of the drawbacks is I am adding about 12 columns and however many rows starting with A1. In this case the application does not work. I was thinking I would just clear the contents or delete the data in column 3 and copy and paste just that column (containing caps) but I get a Run-time error when I try to delete or clear the content of any the cells in this column. Now the code stops working completely until I exit and restart the program. I found that it works to just paste the column three contents (With caps) right over the column 3 (on sheet with code) and that seems to work if I don’t allow the error to occur first. Is there an easy fix to first allow me to paste data with all 12 columns and then column 3 get updated and also a fix so that if a cell is cleared or deleted in this column I don’t get the error message and have to restart the program? Thank you.

NoSparks · #5 08-26-2018, 03:53 PM

Try this

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, cel As Range
With ActiveSheet
    Set rng = Intersect(.Columns(3), .UsedRange)
    If Not rng Is Nothing Then
        Application.EnableEvents = False
        For Each cel In rng
            cel.Value = LCase(cel.Value)
        Next cel
        Application.EnableEvents = True
    End If
End With
End Sub

14spar15 · #6 08-26-2018, 09:48 PM

Perfect, no matter how I add/delete data this keeps working perfectly. Thanks so much for the help.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Issue with Accented Letter Replacement (Upper/Lower Case)	onlywonderboy	Word VBA	1	12-21-2017 08:57 PM
Change lower case to caps whole document	lmb100	Word	4	08-07-2015 06:57 AM
Regular expressions: lower case after hyphen	SusanG	Word	2	06-06-2012 11:58 PM
Search for paras beginging with a lower case	MShroff	Word	1	10-19-2010 06:41 AM
Upper to lower case	jd	Excel	1	04-28-2006 07:40 AM