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

 

[gibsongk55]

Hi Again Trevor,

Yes i tried kpark's code like you said and that works fine also. Just not on those three columns like I said. I guess I need a work around because of the validation on those three columns.


Thanks,

Gibs

 

[gibsongk55]

Okay I got it all to work now.

To get around the list problem I changed all the colors in the list to low case.
This way it will validate and change to proper case automatically.

Thanks everyone for your help. It was much appreciated.


Gibs

 

[Trevor G]

Pleased to read you have a working solution.

 

[gibsongk55]

Please I need some more help. I had everything working but now when I enter data using the form it does not convert column F to upper case. Also the first four columns B through E all cells are empty after I entered data through the form.

VBA code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("K:K, M:M, 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, D:D, F:F, G:G. H:H, I:I, J:J, 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

I have fields column B through R and one other vba routine to validate email.

Thanks for help.

Gibs

 

[gibsongk55]

I found that the problem is in my column F which has a list to validate only M,F allowable for entry. I have in vba to set it to upper case on that column. So when i type in "m" in that cell, the vba does not change it to upper case and it fails validation. After i manually retype the data in that cell to upper case "M" it clears out all the data entered in the four columns before it. But it still appeared in the data form before going to the next record.

Hope there is a way to fix this.

Thanks,

Gibs