Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom Format Cell | Excel Discussion (Misc queries) | |||
Convert data into proper time format | Excel Worksheet Functions | |||
cond format | Excel Worksheet Functions | |||
Cond Format & helper-cell based "duplicate rec" tricked by content | Excel Discussion (Misc queries) | |||
auto date: cond. format Question | Excel Discussion (Misc queries) |