Cond Format & Proper
 

Trying to use the Proper Function in Cond Format so when people type names it
automatically corrects it to Title case.Please advice how i can acheive this.

Many thanks

laurie g

Cond Format & Proper
 

Conditional formatting doesn't affect cell content in any way. One way is to
use another column:

=PROPER(A1)

or another is a change event for column A:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Target.Value = WorksheetFunction.Proper(Target.Value)
End If
End Sub

Regards,
Stefi



€žlaurie g€� ezt Ã*rta:

Trying to use the Proper Function in Cond Format so when people type names it
automatically corrects it to Title case.Please advice how i can acheive this.

Many thanks

laurie g

Cond Format & Proper
 

Just a warning.

This will convert any formula in column A to a value, too.

Maybe:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
application.enableevents = false
Target.Formula = WorksheetFunction.Proper(Target.Formula)
application.enableevents = true
End If
End Sub

The "application.enableevents = false" stops the code from firing the event.

Stefi wrote:

Conditional formatting doesn't affect cell content in any way. One way is to
use another column:

=PROPER(A1)

or another is a change event for column A:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Target.Value = WorksheetFunction.Proper(Target.Value)
End If
End Sub

Regards,
Stefi

€žlaurie g€� ezt Ã*rta:

Trying to use the Proper Function in Cond Format so when people type names it
automatically corrects it to Title case.Please advice how i can acheive this.

Many thanks

laurie g

--

Dave Peterson

Cond Format & Proper
 

Hi Dave,

Thanks for the warning, it is generally true, in Laurie's case I supposed
that if column A is used for typing in names (that is Target contains a value
just typed in), there will be no formulae in the range affected by the
function!

Regards,
Stefi


€žDave Peterson€� ezt Ã*rta:

Just a warning.

This will convert any formula in column A to a value, too.

Maybe:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
application.enableevents = false
Target.Formula = WorksheetFunction.Proper(Target.Formula)
application.enableevents = true
End If
End Sub

The "application.enableevents = false" stops the code from firing the event.

Stefi wrote:

Conditional formatting doesn't affect cell content in any way. One way is to
use another column:

=PROPER(A1)

or another is a change event for column A:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Target.Value = WorksheetFunction.Proper(Target.Value)
End If
End Sub

Regards,
Stefi

€žlaurie g€Â� ezt ÃÂ*rta:

Trying to use the Proper Function in Cond Format so when people type names it
automatically corrects it to Title case.Please advice how i can acheive this.

Many thanks

laurie g

--

Dave Peterson

Cond Format & Proper
 

Hi Dave,

application.enableevents = false

is of course necessary, I forgot it!

Thanks,
Stefi

€žStefi€� ezt Ã*rta:

Hi Dave,

Thanks for the warning, it is generally true, in Laurie's case I supposed
that if column A is used for typing in names (that is Target contains a value
just typed in), there will be no formulae in the range affected by the
function!

Regards,
Stefi


€žDave Peterson€� ezt Ã*rta:

Just a warning.

This will convert any formula in column A to a value, too.

Maybe:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
application.enableevents = false
Target.Formula = WorksheetFunction.Proper(Target.Formula)
application.enableevents = true
End If
End Sub

The "application.enableevents = false" stops the code from firing the event.

Stefi wrote:

Conditional formatting doesn't affect cell content in any way. One way is to
use another column:

=PROPER(A1)

or another is a change event for column A:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Target.Value = WorksheetFunction.Proper(Target.Value)
End If
End Sub

Regards,
Stefi

€žlaurie g€Â� ezt ÃÂ*rta:

Trying to use the Proper Function in Cond Format so when people type names it
automatically corrects it to Title case.Please advice how i can acheive this.

Many thanks

laurie g

--

Dave Peterson