Need uppercase and propercase when entered in cell

[gibsongk55]

Hi,

How can I use VBA to automatically set uppercase and propercase upon entry in cells.

This is what I need for the entire worksheet according to columns below:

Proper Case Columns C, F, J, K, M, N, O, Q

UPPER CASE Columns D, L, R

Thanks,

GIbs

 

[Richard Schollar]

Hi

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rProp As Range, rUpper As Range
Dim cell As Range

On Error GoTo exit_here

Set rProp = Application.Intersect(Target, Range("C:C, F:F, J:J, K:K, M:M, N:N, O:O, Q:Q"))

Set rUpper = Application.Intersect(Target, Range("D:D,L:L,R:R"))

Application.EnableEvents = False
If Not rProp Is Nothing Then
    For Each cell In rProp.Cells
        If Len(cell.Value) > 0 Then _
            cell.Value = Application.Proper(cell.Value)
    Next cell
End If
If Not rUpper Is Nothing Then
    For Each cell In rUpper.Cells
        If Len(cell.Value) > 0 Then _
            cell.Value = UCase(cell.Value)
    Next cell
End If

exit_here:
    Application.EnableEvents = True


End Sub

 

[kpark91]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 3 Or Target.Column = 6 Or Target.Column = 10 Or Target.Column = 11 Or Target.Column = 13 Or Target.Column = 14 Or Target.Column = 15 Or Target.Column = 17 Then
        Target.Value = WorksheetFunction.Proper(Target.Value)
    ElseIf Target.Column = 4 Or Target.Column = 12 Or Target.Column = 18 Then
        Target.Value = UCase(Target.Value)
    End If
End Sub

 

[Trevor G]

Behind your sheet (Use right mouse button on sheet name and select View Code), select the drop down Object and select Sheet then other drop down to change and add this code, you will have to adpat the ranges

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("D2:D32")) Is Nothing Then Target = UCase(Target)
If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub
On Error Resume Next
If Not Intersect(Target, Range("E1:E32")) Is Nothing Then
Application.EnableEvents = False
Target = StrConv(Target, vbProperCase)
Application.EnableEvents = True
End If
On Error GoTo 0
End Sub

 

[gibsongk55]

Hi Guys,

Thanks for the input. I tried all three sets of code above and neither one of them do anything when i enter text in those columns.

Anything else to try?

Thanks,

Gibs

 

[Trevor G]

Where have you put the code and how have you added it?

 

[gibsongk55]

Hi Trevor,

Just like you said. I went into the visual basic editor.

spreadsheet name is template.xls

+ VBAProject (Template.xls)
+Microsoft Excel Objects
-Modules
DL_propercase

under DL_propercase I have:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("D:D, L:L, R:R")) Is Nothing Then Target = UCase(Target)
If Target.Cells.Count > 1 Or Target.HasFormula Then Exit Sub
On Error Resume Next
If Not Intersect(Target, Range("C:C, F:F, J:J, K:K, M:M, N:N, O:O, Q:Q")) Is Nothing Then
Application.EnableEvents = False
Target = StrConv(Target, vbProperCase)
Application.EnableEvents = True
End If
On Error GoTo 0
End Sub

Thanks,

Gibs

 

[Richard Schollar]

In Excel, with this workbook visible on screen, on the sheet where you want this code to operate, right click the tab name and select View Code. Paste any of the code options into the module window that will open.

I should have made it clear originally - you want the code in the sheet module (otherwise the code won't repsond to events).

 

[Trevor G]

I am testing out the other suggestions and they seem to work ok

Can I suggest you look to test this out on a new workbook and then on the first sheet in the workbook select the sheet name and use right mouse button, then View Code, the select the General Drop Down and change to Worksheet. Next select the other drop down to Change. Then copy this code in which is working well thanks to kpark91

If Target.Column = 3 Or Target.Column = 6 Or Target.Column = 10 Or Target.Column = 11 Or Target.Column = 13 Or Target.Column = 14 Or Target.Column = 15 Or Target.Column = 17 Then
Target.Value = WorksheetFunction.Proper(Target.Value)
ElseIf Target.Column = 4 Or Target.Column = 12 Or Target.Column = 18 Then
Target.Value = UCase(Target.Value)
End If

 

[gibsongk55]

Richard,

Thanks so much. I really learned something now. Didn't know to put there at all. One more thing. In columns J, K and L I have a validation List with some words that are only allowed such as a list of colors. All the words in the list are proper case.

All the columns work with the case except these three (J, K and L). I guess something to do with the validation. Is there a work around?

Thanks again,

Gibs