UPPERCASE, lowercase issue

[Jock]

Having created a pivot table, it has raised the issue of the same data
entered in uppercase by one person, lowercase by another and normal case by
yet another. Can I programme the whole workbook to be normal case regardless
of user input?

 

[Mike H]

Hi,

i way is worksheet change

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If Not .HasFormula Then
Target.Value = StrConv(Target.Value, vbProperCase)
End If
End With
End Sub

you coould use
vbUpperCase
vbLowerCase

Mike

 

[Jock]

Thanks Mike,
Can that be adapted to "workbook" or is that not a proper parameter?
Cheers,

Jock

 

[Mike H]

Jock,

Not tested but this should work for all sheets.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
With Target
If Not .HasFormula Then
Target.Value = StrConv(Target.Value, vbProperCase)
End If
End With
End Sub

Double click this workbook and paste in.

Mie

 

[Jock]

Cool.
Thanks very much


Jock

Jock,

Not tested but this should work for all sheets.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
With Target
If Not .HasFormula Then
Target.Value = StrConv(Target.Value, vbProperCase)
End If
End With
End Sub

Double click this workbook and paste in.

Mie

 

[Tom Ogilvy]

If you don't want your workbook to slow way down from recursive calls to the
SheetChange event and you don't want error messages when multiple cells are
changed (such as clearing a block of cells), you might modify your code along
these lines:


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error goto ErrHandler
Application.EnableEvents = False
for each cell in Target
With cell
If Not .HasFormula Then
cell.Value = StrConv(cell.Value, vbProperCase)
End If
End With
Next cell
ErrHandler:
Application.EnableEvents = True
End Sub

 

[Jock]

Thanks Tom.
I had added rows using 'Ctrl+" which brought up the code debug window.
Nice one.