How to =Proper() in conditional format

GoKu

GoKu

Board Regular
Joined
Dec 18, 2007
Messages
244
Hi All,
When the data capturers are entering data, they type with the caps on, afterward I use the Proper function to correct this, is there a way to have them typing it correctly from the start? I was thinking about conditional format, but I cant seem to get it right with the Proper function.
Thank you.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
Does this do what you want



Private Sub Worksheet_change(ByVal target As Range)

target.Value = WorksheetFunction.Proper(target)

End Sub
 
Upvote 0
Excatly like
Private Sub Worksheet_change(ByVal target As Range)

target.Value = WorksheetFunction.Proper(target)

End Sub
Click to expand...

or (target) = the range that I specify (C10:C60000)

2nd question, is there a way without VBA?

Tnx
 
Upvote 0
This will work but it's a bit slow, sure someone may know code (I'm just learning !)



Private Sub Worksheet_selectionchange(ByVal target As Excel.Range)

Dim VRANGE As Range

Set VRANGE = Range("c10:c60000")

If Union(target, VRANGE).Address = VRANGE.Address Then target.Value = WorksheetFunction.Proper(target)

End Sub


Sorry, don't know a way to do it without VBA

HTH

Jim
 
Upvote 0
You'd better control the event, otherwise execute endless.
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range
If Intersect(Target, Range("c10:c60000")) Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each r In Intersect(Target, Range("c10:c60000"))
    r.Value = StrConv(r.Value, 3)
Next
Application.EnableEvents = True
End Sub
 
Upvote 0
You could do it with Data>Validation:

1. Select cells you want it to apply to (assume C10:C1000 for the timebeing)
2. Go Data>Validation>Allow:Custom and type in the formula:

=EXACT(PROPER(C10),C10)

and hit OK.

I personally like the VBA route because this way you don't interrupt people entering data - you just correct it for them. You could re-write the original sub so that it only works on C10:C60000 if you wish:

Code: 
Private Sub Worksheet_change(ByVal target As Range)
 
Dim rng As Range
Set rng = Application.Intersect(Target,Range(C10:C60000))
 
if Not rng Is Nothing Then
  Application.EnableEvents = False
  On Error Goto Exit_Here
    rng.Value = Application.Proper(rng.Value)
End If
Exit_Here:
  Application.EnableEvents = True
 
End Sub
 
Last edited:
Upvote 0
Thnx Richard, yes you are right,the code way is much beter, it is working wonderfully. Thank you very much.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
name manager
Replies
0
Views
201
kelvin_9
K
nosajnid89
  • Solved
Can 'Conditionally Formatted' cells be use as a logic in IF statements etc?
2
Replies
14
Views
2K
RoryA
RoryA
D
  • Question
VBA Control Of Page Breaks
Replies
2
Views
871
DannyDont
D
O
  • Question
How to stop a procedure with Application.Ontime
Replies
2
Views
256
oldfisher
O
M
  • Question
Converting Spanish character symbols into UTF8 Format
Replies
2
Views
584
Jaafar Tribak
Jaafar Tribak

Forum statistics

Threads
1,214,592
Messages
6,120,433
Members
448,961
Latest member
nzskater

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top