Default Value formatting... UK Postcodes
 

I want to know how to format a cell so it only takes the following
information...

Char.Char,Int,Int <space Int,Char, Char

So for example it would look like this...

NR01 2RJ

I also want the characters changed to capitals if they were entered in
lowercase...

I'm sure this is an easy one... but I cant find the solution anywhere...

Scott

Default Value formatting... UK Postcodes
 

Hi Scott,

Probably better to do it with event code as I don't think any format will
upshift

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then
With Target
.Value = UCase(.Value)
.Value = Left(.Value, 4) & " " & Right(.Value, 3)
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--

HTH

RP
(remove nothere from the email address if mailing direct)


"Scott" wrote in message
...
I want to know how to format a cell so it only takes the following
information...

Char.Char,Int,Int <space Int,Char, Char

So for example it would look like this...

NR01 2RJ

I also want the characters changed to capitals if they were entered in
lowercase...

I'm sure this is an easy one... but I cant find the solution anywhere...

Scott

Default Value formatting... UK Postcodes
 

Scott wrote:
Char.Char,Int,Int <space Int,Char, Char

Remember the Tiswas postcode, B1 2JP?

You format is wrong, as I may have mentioned befo

http://www.evoxfacilities.co.u*k/evoxps.htm

Jamie.

--

Default Value formatting... UK Postcodes
 

Do you mean you want to impose data validation as well as formatting and caps?
Bob's code will do that latter, but not the former.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As String

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then
With Target
X = UCase$(.Value)
If X Like "[A-Z][A-Z]## #[A-Z][A-Z]" Then
'it's OK as is
ElseIf X Like "[A-Z][A-Z]###[A-Z][A-Z]" Then
X = Left$(X, 4) & " " & Right$(X, 3)
Else
Msgbox "Incorrect format: AA## #AA", vbokonly, "Error!"
'leave the string as-is so they can correct without
'retyping the whole thing
End If
.Value = X
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub




On Tue, 15 Mar 2005 09:58:22 GMT, "Scott" wrote:

I want to know how to format a cell so it only takes the following
information...

Char.Char,Int,Int <space Int,Char, Char

So for example it would look like this...

NR01 2RJ

I also want the characters changed to capitals if they were entered in
lowercase...

I'm sure this is an easy one... but I cant find the solution anywhere...

Scott