Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-24-2018, 07:30 AM
14spar15 14spar15 is offline Only lower case in column without formula Windows XP Only lower case in column without formula Office 2000
Advanced Beginner
Only lower case in column without formula
 
Join Date: Mar 2011
Posts: 97
14spar15 is on a distinguished road
Default 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?
Reply With Quote
  #2  
Old 08-24-2018, 07:43 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Only lower case in column without formula Windows 7 64bit Only lower case in column without formula Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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
Reply With Quote
  #3  
Old 08-24-2018, 09:24 AM
NoSparks NoSparks is offline Only lower case in column without formula Windows 7 64bit Only lower case in column without formula Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

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
Reply With Quote
  #4  
Old 08-26-2018, 02:57 PM
14spar15 14spar15 is offline Only lower case in column without formula Windows XP Only lower case in column without formula Office 2000
Advanced Beginner
Only lower case in column without formula
 
Join Date: Mar 2011
Posts: 97
14spar15 is on a distinguished road
Default

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.
Reply With Quote
  #5  
Old 08-26-2018, 03:53 PM
NoSparks NoSparks is offline Only lower case in column without formula Windows 7 64bit Only lower case in column without formula Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

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
Reply With Quote
  #6  
Old 08-26-2018, 09:48 PM
14spar15 14spar15 is offline Only lower case in column without formula Windows XP Only lower case in column without formula Office 2000
Advanced Beginner
Only lower case in column without formula
 
Join Date: Mar 2011
Posts: 97
14spar15 is on a distinguished road
Default

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

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Only lower case in column without formula Issue with Accented Letter Replacement (Upper/Lower Case) onlywonderboy Word VBA 1 12-21-2017 08:57 PM
Only lower case in column without formula Change lower case to caps whole document lmb100 Word 4 08-07-2015 06:57 AM
Only lower case in column without formula 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

Other Forums: Access Forums

All times are GMT -7. The time now is 12:56 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft