UPPERCASE, lowercase issue
 

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?
--
tia

Jock

UPPERCASE, lowercase issue
 

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" wrote:

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?
--
tia

Jock

UPPERCASE, lowercase issue
 

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

Jock


"Mike H" wrote:

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" wrote:

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?
--
tia

Jock

UPPERCASE, lowercase issue
 

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" wrote:

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

Jock


"Mike H" wrote:

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" wrote:

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?
--
tia

Jock

UPPERCASE, lowercase issue
 

Cool.
Thanks very much


Jock


"Mike H" wrote:

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" wrote:

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

Jock


"Mike H" wrote:

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" wrote:

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?
--
tia

Jock

UPPERCASE, lowercase issue
 

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

--
Regards,
Tom Ogilvy

"Jock" wrote:

Cool.
Thanks very much


Jock


"Mike H" wrote:

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" wrote:

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

Jock


"Mike H" wrote:

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" wrote:

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?
--
tia

Jock

UPPERCASE, lowercase issue
 

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

Jock


"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy

"Jock" wrote:

Cool.
Thanks very much


Jock


"Mike H" wrote:

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" wrote:

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

Jock


"Mike H" wrote:

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" wrote:

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?
--
tia

Jock