CODEWITHSUNDEEP
excelvba
Dameli
Dameli

Reputation: 1

Excel cell shall contain required characters

I would appreciate it if you could help to solve following issue: When external User fulfil the defined cell in Excel, he/she is allowed to put predefined values within the one cell. Like I want to populate "Country" field and options available for me are: "Moscow" or "Moscow;New-York".

So that acceptable symbols are Latin letters in any case and Semicolon.

If the result is false (for some reasons I used comma after Country name), then the Error message shall be popped up, like "This symbol is not acceptable".

Thank you for your time and help in advance!

Upvotes: 0

Views: 90

Answers (2)

T.M.
T.M.

Reputation: 9948

Seems to me that OP just wants to prevent the input of certain characters.

These might be detected e.g. by a negative Like comparison via

    Dim SearchString As String
    SearchString = "Moscow;New-York"        
    If SearchString Like "*[! ;A-Za-z-]*" Then 
        MsgBox "Found not accepted symbol."
        '... do other stuff
    Else
        MsgBox "Correct entry."
    Endif

Hints to pattern search via "*[! ;A-Za-z-]*"

The Like operator has only some rudimentary ressemblance with pattern searches in Regular Expressions, but can lead to helpful efficient code, too.

Especially "a group of one or more characters ( charlist ) enclosed in brackets ([ ]) can be used to match any single character in string and can include almost any character code, including digits."

An exclamation point (!) at the beginning of such a charlist means that a match is made if any character except the characters in charlist is found in string.

The trick

Therefore it suffices to find just one single character other than the wanted character sequences (i.e. a blank , a semicolon ;, letters within A-Z or a-z or a hyphen -) by a negative search pattern prefixed by ! to find out any intruder (the surrounding wild cards * allow to identify the intruder at any position).

Note that I positioned the hyphen - as last element in the pattern search, as within the char list the hyphen is used to identify a range of characters. - (Contrary to MS Help positioning - immediately after ! doesn't seem to work)

C.f. Ms Help Like operator

The above code splitter doesn't pretend in any way to be a complete solution, but intends to give a starting idea. It's up to the asker to develop further steps in code of his own.

Upvotes: 1

Maya
Maya

Reputation: 134

You could use the INSTR function to check the cell after editing for specific characters you don't want.

for example:

If INSTR(1,cellreference,",")<>”0” then 'a comma was found in the cell
    msgbox "This symbol is not acceptable" 
End if

Upvotes: 0

Related Questions