I’m hoping someone will have the patience and be kind enough to help me with a small VBA Select Case issue I’m having.
I have a spreadsheet where I will be asking the user to enter their (UK) address including their Postcode / Zip code.
I want to use a Case Statement to check and validate the post code entered by the user. There are 6 (postcode) format types (between 5 and 7 digits long) that I need to be checked and compared - then IF the correct format is entered the postcode (entry) is capitalised and set out in one of the below postcode format types:
A9 9AA,
A99 9AA,
AA9 9AA,
AA99 9AA,
A9A 9AA,
AA9A 9AA,
The code I’m using has been modified from an existing one in my worksheet 1 and it works fine – but for only 1 “Postcode format type”.
I’m using the “LEN” function to first check the inputted data matches the length of one of the (postcode) format types. I’m then using, “IF and LIKE” to check if the APLHA and Numeric entry also matches one of the 6 formats types. All criteria being met then the UCase code is executed, or else the appropriate Msgbox should pop up.
The unfortunate problem is having limited VBA knowledge I cannot work out how to include the other 5 (postcode) formats in my code to be tested. I’m using the following code for Cell: D11:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim PostCode As String
Target.Cells(11, 4) = PostCode
PostCode = Range("D11").Value
If Len(PostCode) = 0 Then Exit Sub
'remove all spaces for common starting point
PostCode = Trim(Replace(PostCode, " ", ""))
Select Case Len(PostCode)
Case Is < 5
MsgBox "The Postcode entered does not appear to be " & _
"a valid UK Postcode." & _
Chr(13) & "Please check you've entered the correct numbers. You do not need to enter the dashes (-).", vbApplicationModal + _
vbInformation + vbOKOnly, "Invalid Postcode"
Cancel = True
With Range("D11")
End With
On Error GoTo 0
Exit Sub
Case Is = 5
PostCode = UCase(PostCode)
If PostCode Like "[A-Z][0-9][0-9][A-Z][A-Z]" Then
PostCode = Left(PostCode, 2) & " " & Mid(PostCode, 3, 2) & "" & _
Mid(PostCode, 5, 2)
Else
MsgBox "The Postcode entered does not appear to be " & _
"a valid Postcode. " & _
Chr(13) & "Please check you've entered the correct numbers. You do not need to enter the spaces.", vbApplicationModal + _
vbInformation + vbOKOnly, "Invalid Postcode"
Cancel = True
With Range("D11")
End With
Exit Sub
End If
Case Is > 7
MsgBox "The Postode entered does not appear to be " & _
"a valid Postcode. You have entered too many digits " & _
Chr(13) & "Please check you've entered the correct numbers. You do not need to enter the dashes (-).", vbApplicationModal + _
vbInformation + vbOKOnly, "Invalid Postcode"
Cancel = True
With Range("D11")
End With
Exit Sub
End Select
Range("D11").Value = PostCode
End Sub
I’ve tried looking online and at tutorials for a fix, so after a number of attempts with no success this may be my last hope. As usual any help is always appreciated.
Many thanks in advance
.
I have a spreadsheet where I will be asking the user to enter their (UK) address including their Postcode / Zip code.
I want to use a Case Statement to check and validate the post code entered by the user. There are 6 (postcode) format types (between 5 and 7 digits long) that I need to be checked and compared - then IF the correct format is entered the postcode (entry) is capitalised and set out in one of the below postcode format types:
A9 9AA,
A99 9AA,
AA9 9AA,
AA99 9AA,
A9A 9AA,
AA9A 9AA,
The code I’m using has been modified from an existing one in my worksheet 1 and it works fine – but for only 1 “Postcode format type”.
I’m using the “LEN” function to first check the inputted data matches the length of one of the (postcode) format types. I’m then using, “IF and LIKE” to check if the APLHA and Numeric entry also matches one of the 6 formats types. All criteria being met then the UCase code is executed, or else the appropriate Msgbox should pop up.
The unfortunate problem is having limited VBA knowledge I cannot work out how to include the other 5 (postcode) formats in my code to be tested. I’m using the following code for Cell: D11:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim PostCode As String
Target.Cells(11, 4) = PostCode
PostCode = Range("D11").Value
If Len(PostCode) = 0 Then Exit Sub
'remove all spaces for common starting point
PostCode = Trim(Replace(PostCode, " ", ""))
Select Case Len(PostCode)
Case Is < 5
MsgBox "The Postcode entered does not appear to be " & _
"a valid UK Postcode." & _
Chr(13) & "Please check you've entered the correct numbers. You do not need to enter the dashes (-).", vbApplicationModal + _
vbInformation + vbOKOnly, "Invalid Postcode"
Cancel = True
With Range("D11")
End With
On Error GoTo 0
Exit Sub
Case Is = 5
PostCode = UCase(PostCode)
If PostCode Like "[A-Z][0-9][0-9][A-Z][A-Z]" Then
PostCode = Left(PostCode, 2) & " " & Mid(PostCode, 3, 2) & "" & _
Mid(PostCode, 5, 2)
Else
MsgBox "The Postcode entered does not appear to be " & _
"a valid Postcode. " & _
Chr(13) & "Please check you've entered the correct numbers. You do not need to enter the spaces.", vbApplicationModal + _
vbInformation + vbOKOnly, "Invalid Postcode"
Cancel = True
With Range("D11")
End With
Exit Sub
End If
Case Is > 7
MsgBox "The Postode entered does not appear to be " & _
"a valid Postcode. You have entered too many digits " & _
Chr(13) & "Please check you've entered the correct numbers. You do not need to enter the dashes (-).", vbApplicationModal + _
vbInformation + vbOKOnly, "Invalid Postcode"
Cancel = True
With Range("D11")
End With
Exit Sub
End Select
Range("D11").Value = PostCode
End Sub
I’ve tried looking online and at tutorials for a fix, so after a number of attempts with no success this may be my last hope. As usual any help is always appreciated.
Many thanks in advance
.
