custom data validation

merlin777

Well-known Member
Joined
Aug 29, 2009
Messages
1,397
Office Version
  1. 2007
I have a requirement for a cell to contain "PO123456" where (123456 is >= 100000 and <= 999999) and to be rejected by data validation if:
  • the first 2 characters are not 'PO'
  • the remaining is not a 6-digit number within range
Ive tried various approaches but the formula is getting very long and complex and i wondered if you could think of a simpler way to do this?
thanks IA
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
I had to do a bit of researching and testing for this! I believe this'll do what you want. Put it in the vba code for the Sheet object.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim POCell As Range

    Set POCell = Range("B5")

    If IsEmpty(POCell) = False Then
        If Not (Len(POCell) = 8) Then
            MsgBox "Entry must be in format PO######"
            Exit Sub
        ElseIf Not (Left(POCell, 2) = "PO") Then
            MsgBox "Entry must be in format PO######"
            Exit Sub
        ElseIf Not IsNumeric(Right(POCell, 6)) Then
            MsgBox "Entry must be in format PO######"
            Exit Sub
        ElseIf Not Right(POCell, 6) < 999999 And Right(POCell, 6) > 100000 Then
            MsgBox "Entry must be in format PO######"
            Exit Sub
        End If
    End If

End Sub
First it checks that the user has tried entering something in the cell. Then it applies four tests:
- is it exactly 8 characters long?
- does it begin with PO?
- does it end with a 6-digit number?
- is that number between 100000 and 999999?

Technically, it will allow a bad entry, but will keep pestering the user. You could use POCell.ClearContents after each MsgBox line to blank the cell again.
 
Upvote 0

Forum statistics

Threads
1,224,585
Messages
6,179,706
Members
452,939
Latest member
WCrawford

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