Excel 2016 Data Validation

Jeremy Gohman
Jeremy Gohman used Ask the Experts™
on
Hello this may be a long shot but I need some assistance with data validation.

We are currently using a new tool to automate firewall requests in our network which will pull the requested information from an spreadsheet

Currently for the protocol / ports the users are requesting it is formatted like this

Single Port Request:
Users may continue to add as many ports as they require as long as they are separated by a comma

TCP 80, 443

UDP 50

Port Range:
 Users may continue to add as many ports ranges as they require as long as they are separated by a comma

TCP 5000-5010

UDP 5000-5010, 5030-5040

 

The validation that I require is as follows

 

1.) All requests must start with either TCP or UDP not both

2.) All ports must be between 0 to 65535.

3.) No other characters or unnecessary spaces within the cell

 

Any help or insight would be greatly appreciated as I don't even know if this is possible to perform.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Top Expert 2016
Commented:
Hi,

pls try this UDF
Function test_cell(strEMail As String) As Boolean
Dim regex As Object

Set regex = CreateObject("VBScript.RegExp")

With regex
    .Global = True
    .MultiLine = True
    .IgnoreCase = True
    .Pattern = "^(TCP|UDP) (([1-9])|([1-9]\d)|([1-9]\d\d)|([1-9]\d{3})|([1-5]\d{4})|(6[0-4]\d{3})|(65[0-4]\d\d)|(655[0-2]\d)|(6553[0-5]))(\-(([1-9])|([1-9]\d)|([1-9]\d\d)|([1-9]\d{3})|([1-5]\d{4})|(6[0-4]\d{3})|(65[0-4]\d\d)|(655[0-2]\d)|(6553[0-5])))?\b" & _
            "(, (([1-9])|([1-9]\d)|([1-9]\d\d)|([1-9]\d{3})|([1-5]\d{4})|(6[0-4]\d{3})|(65[0-4]\d\d)|(655[0-2]\d)|(6553[0-5]))(\-(([1-9])|([1-9]\d)|([1-9]\d\d)|([1-9]\d{3})|([1-5]\d{4})|(6[0-4]\d{3})|(65[0-4]\d\d)|(655[0-2]\d)|(6553[0-5])))?\b)?$"
End With
test_cell = regex.Test(strEMail)

Set regex = Nothing
End Function

Open in new window


to use the UDF in a validation
Select A1, then in Name Manager define a name called ValidCell whose refersto is:

=test_cell(A1)
(Note: no $ signs in the cell reference)

Then in your data validation use =ValidCell and uncheck the 'Ignore blank' option.

REgards
EE20171101.xlsm
Rgonzo1971
Top Expert 2016

Commented:
Corrected regex 
Function test_cell(strEMail As String) As Boolean
Dim regex As Object

Set regex = CreateObject("VBScript.RegExp")

With regex
    .Global = True
    .MultiLine = True
    .IgnoreCase = True
    .Pattern = "^(TCP|UDP) (([1-9])|([1-9]\d)|([1-9]\d\d)|([1-9]\d{3})|([1-5]\d{4})|(6[0-4]\d{3})|(65[0-4]\d\d)|(655[0-2]\d)|(6553[0-5]))(\-(([1-9])|([1-9]\d)|([1-9]\d\d)|([1-9]\d{3})|([1-5]\d{4})|(6[0-4]\d{3})|(65[0-4]\d\d)|(655[0-2]\d)|(6553[0-5])))*\b" & _
            "(, (([1-9])|([1-9]\d)|([1-9]\d\d)|([1-9]\d{3})|([1-5]\d{4})|(6[0-4]\d{3})|(65[0-4]\d\d)|(655[0-2]\d)|(6553[0-5]))(\-(([1-9])|([1-9]\d)|([1-9]\d\d)|([1-9]\d{3})|([1-5]\d{4})|(6[0-4]\d{3})|(65[0-4]\d\d)|(655[0-2]\d)|(6553[0-5])))*\b)*$"
End With
test_cell = regex.Test(strEMail)

Set regex = Nothing
End Function

Open in new window

Jeremy GohmanNetwork Engineer

Author

Commented:
Thank you for the fast response and example.  After some initial testing this seemed to work flawlessly.  I appreciate the help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial