#1
|
|||
|
|||
Only lower case in column without formula
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? |
#2
|
||||
|
||||
Please wrap code with code tags. Thank you
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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.
|
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |