PDA

View Full Version : Solved: Auto capitalize a column



Emoncada
01-03-2008, 07:42 AM
is it possible in vb to have like column ("F:F") on change to auto capitalize the column?

lucas
01-03-2008, 07:48 AM
All caps or proper case?
This is worksheet code.....not standard module
This will make it proper case:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim lRow As Long
lRow = Cells(Rows.Count, 1).End(xlUp).Row


' If it's not Column A, then we don't run
If Intersect(Range("A1:A" & lRow), Range(Target.Address)) _
Is Nothing Then Exit Sub


' Stop other events
With Application
.EnableEvents = True


' Errors if we clear ranges of cells in target column
On Error Resume Next
' Convert the target text to Proper case
Target.Value = Application.Proper$(Target.Value)


' Re-Enable events
.EnableEvents = True
End With
End Sub

lucas
01-03-2008, 08:13 AM
Em, the above is for column A. To change it to column F you must change a couple of things:


Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim lRow As Long
lRow = Cells(Rows.Count, 6).End(xlUp).Row



' If it's not Column A, then we don't run
If Intersect(Range("F1:F" & lRow), Range(Target.Address)) _
Is Nothing Then Exit Sub



' Stop other events
With Application
.EnableEvents = True



' Errors if we clear ranges of cells in target column
On Error Resume Next
' Convert the target text to Proper case
Target.Value = Application.Proper$(Target.Value)



' Re-Enable events
.EnableEvents = True
End With
End Sub

Emoncada
01-03-2008, 08:17 AM
No that seems to just Capitilize the first Letter.


I need something like this.
User Enters

"mxls05813w"

What I want is this
"MXLS05813W"

Can this be done?

lucas
01-03-2008, 08:23 AM
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
'Change A1:A10 to the range you desire
'Change UCase to LCase to provide for lowercase instead of uppercase
If Not Application.Intersect(Target, Range("A1:A10")) Is Nothing Then
Target(1).Value = UCase(Target(1).Value)
End If
Application.EnableEvents = True
End Sub

rory
01-03-2008, 08:26 AM
Small variation of Steve's code to do upper case and to account for multiple cells being changed at once:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim strCol As String, rngCell As Range
strCol = "F" ' <- adjust as necessary


If Intersect(Target, Columns(strCol)) Is Nothing Then Exit Sub


' Stop other events
Application.EnableEvents = False


On Error Resume Next
For Each rngCell In Intersect(Target, Columns(strCol))
' Convert the target text to upper case
rngCell.Value = UCase$(Target.Value)
Next rngCell

' Re-Enable events
Application.EnableEvents = True
End Sub

JKwan
01-03-2008, 08:28 AM
Try:
Target.Value = UCase (Target.Value)

lucas
01-03-2008, 08:32 AM
Nice Rory, that will pick up the stragglers....

Bob Phillips
01-03-2008, 08:36 AM
Nice Rory, that will pick up the stragglers....

I don't get it. How do you change multiple cells at once?

lucas
01-03-2008, 08:45 AM
I admit I didn't check it. I thought if there were other words in the column before the code was added that the code would loop but it doesn't...
Next rngCell

Emoncada
01-03-2008, 09:04 AM
That looks great. Thanks works Thanks.

rory
01-03-2008, 09:36 AM
Select more than one cell, start typing, then press Ctrl+Enter. If you don't loop through the cells, then Ucase(Target) will error because you're passing it an array. Same thing will happen if you paste a load of cells in.

Bob Phillips
01-03-2008, 10:22 AM
Yeah, like anyone is going to enter the same value in all the cells.

As for pasting, it doesn't work, your code should be



Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim strCol As String, rngCell As Range
strCol = "F" ' <- adjust as necessary


If Intersect(Target, Columns(strCol)) Is Nothing Then Exit Sub


' Stop other events
Application.EnableEvents = False


On Error Resume Next
For Each rngCell In Intersect(Target, Columns(strCol))
' Convert the target text to upper case
rngCell.Value = UCase$(rngCell.Value)
Next rngCell

' Re-Enable events
Application.EnableEvents = True
End Sub


But I just cannot see that anyone with this rqeuirement would be doing it via a mass copy and paste.

rory
01-03-2008, 10:44 AM
That will teach me to type in haste - thanks for catching that. However, I don't see why it is so far fetched that someone might type the same info in a few cells at once (or paste it in) - I have several workbooks with change events that cater for entering the same value in several cells (usually as a shortcut text which gets converted into some specific text). If you don't loop through the Target, you should at least test the count or only operate on the first cell IMO.