Solved: Can I use VBA or DataVal with symbols?

[Gingertrees]

Hi all,
We're switching all our clients to a new software tracking system, in which all their IDs change from nice little numeric strings (e.g. 20045, 1232234, etc) into new "encrypted" IDs. These encrypted IDs are 9 characters long, and include a mix of uppercase and lowercase letters, numbers, and the "+" symbol. Examples:
+4AB+aiO0
RT0+33aAw

Now, I knew about this transition ahead of time, so I put data validation on everyone's client spreadsheets that read those nice little numeric strings at 1-10 character Text Lengths. Sadly, I did not know about the cursed "+" in those new IDs.

Question: how can I limit values that are entered in here so they:
- are exactly 9 characters in length
- have no spaces
- can start with a number, letter, or "+" symbol

Is there some way I could create a formula to place an apostrophe in front of the ID if it started off with a "+"?

Or do I just have to tell everyone to type that apostrophe themselves and hope against hope they do it?

[Benzadeus]

Private Sub Worksheet_Change(ByVal Target As Range)

Dim sLocation As String

sLocation = "A1" '<-- change to suit

If Not Intersect(Target, Range(sLocation)) Is Nothing Then
Target.NumberFormat = "@"
If Len(Target) = 9 Then
If InStr(1, Target, " ") = 0 Then
'Cell is OK.
Else
MsgBox "This cell mustn't have any spaces!", vbCritical, "Error!"
Target.Select
End If
Else
MsgBox "This cell must be 9 characters lenght!", vbCritical, "Error!"
Target.Select
End If
End If
End Sub

[Gingertrees]

Benzadeus,
That's what I was looking for! Two more things:
- can I make it impossible (or very hard) for the user to override the error message ? (BTW conditional formatting doesn't work, my users ignore it)
- I need an "IsNull" expression in there somewhere - if I double click the cell, and then move to another cell, my blank cell still triggers the "9 characters" error.
Thanks,
~Ginger

[GTO]

Hi Ariel,

In case spaces are not the only thing we'd like to eliminate, you might wish to check against all allowed characters - thus automatically disallowing weird stuff like linefeeds.

Anyways, not sure if I caught all characters, but maybe (my "thinking cap" seems mighty loosely attached today)...

In the Worksheet's Module:

Option Explicit

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

'// Change range to suit //
If Not Application.Intersect(Target, Range("A1:A10")) Is Nothing _
And Not Target.Count > 1 Then
'// Skip if we are just clearing a cell. //
If Not Target.Value = vbNullString Then

strInput = Target.Text

'// Prevent recurse //
Application.EnableEvents = False

'// See functions //
If Not LegalString(strInput) Then
MsgBox strInput
Target.ClearContents
'// case a goof, pass by other code, but catch turning //
'// events back on //
GoTo QuickOut
End If
If Not PWDLen(strInput) Then
MsgBox strInput, vbCritical, vbNullString
Target.ClearContents
GoTo QuickOut
End If
End If
End If

'any other code....

QuickOut:
Application.EnableEvents = True
End Sub


In a Standard Module:

Option Explicit

Function PWDLen(s As String) As Boolean

Select Case Len(s)
Case Is < 9
s = "Password """ & s & """ is too short."
Exit Function
Case Is > 9
s = "Password """ & s & """ is too long."
Exit Function
End Select
PWDLen = True
End Function

Function LegalString(s As String) As Boolean
Dim tmp As String
Dim i As Long

'// Loop thru ea character in password, checking against OK characters //
For i = 1 To Len(s)
'Note: The right bracket (]) can't be used within a group to match itself.
If Mid(s, i, 1) Like "[A-Za-z0-9?~`!@#$%^&*()_=[+{}|\';:<>,./]" _
Or Mid(s, i, 1) Like "]" _
Or Mid(s, i, 1) Like "-" _
Or Mid(s, i, 1) Like """" Then

tmp = tmp & Mid(s, i, 1)
Else
s = "Password character """ & Mid(s, i, 1) & """ is not allowed."
LegalString = False
Exit Function
End If
Next
LegalString = True
End Function


This does not take care of one issue, that I cannot think past at the moment. If the user starts the password with an apostrophe, YIKES, as it will not be counted in the length, nor returned in the cells Value or Text properties.

Assuming apostrophes are alloweed, hopefuly someone will show us the way :-)

Mark

[Paul_Hossler]

At least in 2007, this returns the apostrophe



strInput = Target.PrefixCharacter & Target.Text


Paul

[Gingertrees]

Hmm...by combining the codes above, it ALMOST does all that I want. I've attached a copy of a simplified workbook with some sample IDs. I say this ALMOST works because:
- I'm occasionally getting compile errors with Benzadeus' code
- I still get the error message if I clear the cell, or double click but don't type anything
- You can override the error message by just going to a different cell
- sometimes it still dislikes the plus signs.
:dunno
Help please?

[Benzadeus]

First, mannualy format P5:P204 as Text. This will avoid '+' errors.

Use the code below to avoid error when clearing contents in cell:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim sRange As String

sRange = "P5:P204" '<-- change to suit

If Target.Count > 1 Then Exit Sub
If Target = vbNullString Then Exit Sub

If Not Intersect(Target, Range(sRange)) Is Nothing Then
If Len(Target) = 9 Then
If InStr(1, Target, " ") = 0 Then
'Cell is OK.
Else
MsgBox "This cell mustn't have any spaces!", vbCritical, "Error!"
Target.Select
End If
Else

MsgBox "This cell must be 9 characters length!", vbCritical, "Error!"
Target.Select
End If
End If
End Sub

I didn't understand how you are overriding errors.

This macro has a weakness... if you select more than one cell at once, you will be able to input any value on the cell... does this have to be avoided too?

[Gingertrees]

Ah, ok now there's no error when the cell is cleared. Thank you!
And by adding a text-length data validation, the user cannot override the length any longer. Thanks a lot!