Capitalization

[TOM2RN]

I have a basic form with columns and rows that I will use for attendance. I will enter a letter into the cells individually, which in my case, a p to represent present. I want the lowercase p to automatically change to an uppercase P. I have tried for days to make this work without success. I've tried everything from formulas to VBA and beyond. It might work for one cell or maybe a few, but I don't get consistent results. It seems simple enough, but it just isn't working for me. A little help please.

 

[dreid1011]

Give this a try:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, Range("B2:H4")) Is Nothing Then
    If Target = "p" Then Target = "P"
End If
Application.EnableEvents = True
End Sub

Change the range to suit your data.

 

[Eric W]

Have you tried AutoCorrect? Click on File > Options > Proofing > AutoCorrect Options > Put a lowercase p in the Replace: box, put an uppercase P in the With: box > Click Add > Click OK > OK.

See if that works for you.

 

[TOM2RN]

Give this a try:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, Range("B2:H4")) Is Nothing Then
    If Target = "p" Then Target = "P"
End If
Application.EnableEvents = True
End Sub

Change the range to suit your data.

 

[TOM2RN]

Thanks, I will give it a try. I had tried some similar VBA I found on the web, but it was inconsistent. I think it may be something with the sheet itself. The sheet was created by someone else and I wonder if there is any corruption.

 

[TOM2RN]

Have you tried AutoCorrect? Click on File > Options > Proofing > AutoCorrect Options > Put a lowercase p in the Replace: box, put an uppercase P in the With: box > Click Add > Click OK > OK.

See if that works for you.

Thanks. I had tried that, but it didn't work consistently. It should have worked, but it makes me wonder if something about the sheet isn't right. I'm using a sheet created by someone else.

 

[bebo021999]

Suppose Input range was A2:Z100
Try below:

Private Sub Worksheet_Change(ByVal Target As Range)
With Application
    .EnableEvents = False
    If Not Intersect(Target, Range("A2:Z100")) Is Nothing Then Target.value = Ucase(target.value)
    Application.EnableEvents = True
End With
End Sub

 

[Peter_SSs]

@dreid1011 @bebo021999
Have you tested your suggestions with entering (or deleting might be more common) or copy/paste more than one cell at a time? Or inserting a new row/column that intersects the target range?

I think it may be something with the sheet itself.

If you think that may be an issue, why not start a completely new workbook to test each suggestion and see if you still get inconsistent results?

It is also not entirely clear if you want this to happen

• for the single letter p only
• for any single letter
• for any text
• in a particular cell or range of cells or columns or rows

My suggestion for testing is for any single letter entered in any cell(s) in columns E, F or G. Again, it might be best to first text in a new workbook.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range
  
  On Error Resume Next
  Set Changed = Intersect(Target, Columns("E:G")).SpecialCells(xlConstants, xlTextValues)
  On Error GoTo 0
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each c In Changed
      If Len(c.Value) = 1 Then c.Value = UCase(c.Value)
    Next c
    Application.EnableEvents = True
  End If
End Sub