Verifying "free text"

[Rejje]

Hi again forum!

I'm in need of getting an event to work. It's a sort of verifying "free text". See attached file.

VerifyFreeText.xlsm

As you see bringing order into the characters work fine. However the verification malfunctions.

Is there a way to "hault" the Excel data verification until code has run? Or; does anyone have a good idea about how to verify these strings using vba script instead?

[Kenneth Hobs]

I see that you went to a lot of effort to make an example. Even so, it is not clear to me what you want. Your data validation from H11:H18 appears to work ok. Did you want some routine to check existing data in H4:H10?

[Rejje]

I kind of had to make a rigorous example, it took almost two hours. I didn't know how to explain this otherwise.

And you understood as well. I do want data validation for H4:H10. But because this must be a free text range the validation has got to order the valid characters "t,c,d,v,h,s" and in this order, all other characters must be sorted away.

So - let's say the user vants to give a cell in range "ORDER_CHARACTERS" a value by typing a string then since this is a free text range vailidation becomes disOrder("td";"dt";"dirty") = newOrder("td").

The excel validation however kicks at disOrder before code has run and "data invalid!" shows. CHECK THIS IN H11:H18! So; excel validation really doesn't work here, unless there is a workaround this problem.

The addresses for verification range are are shown in "VERIFICATION_RANGE_FOR_FREE_TEXT", relative row position.

So - either I need to workaround excel's own validation's bad timing or let the script validate using above mentioned addresses. If newOrder can't be found MsgBox("data invalid!") - try again or excape. Otherwise do nothing.

What do you think?

[Kenneth Hobs]

I can not follow you. Can you give a specific example? e.g. Type "x" in H4 and after Enter a MsgBox tells me that "x" is not in the =INDIRECT(INDEX(VERIFICATION_RANGE_FOR_FREE_TEXT,RELATIVE_POSITION_WORKSHEE T_A))

That is how H11 is working with data validation.

The purpose of data validation is to stop you from making a bad entry. It will show the Error Alert dialog and tell you.

[Rejje]

I will try to explain again. I hope you have observed there is a worksheet event that only allows certain characters to "stick" to cells in range "ORDER_CHARACTERS" (H4:H18). These characters are t, c, d, v, h and s. Any other character that you write in these cells are excluded and the valid letters left are sorted according to the order "tcdvhs". Each character represents a certain scope in a car insurance.

1. Let's say I want to make an entry in H10 where data validation i activated. Here I am of course restricted to the data validation reference. Let's say E10 = "item 2" which restricts data validation for E10 to range "RANGE_VALID_COMBINATIONS_FOR_FREE_TEXT_WITH_ITEM_2". Now I can either choose to write "v" or "vs". I write "vs" and data validation swallows this.

2. Let's now say I hand this to someone less familiar with the logics of the order in which the characters must follow each other. Instead of the valid order "vs" this person writes "sv" in cell H10 and of course data validation pops up saying this is a bad entry.

BUT: I would like for the data validation to hault for a moment until the worksheet change event has run and sorted the characters "sv" into "vs" and then for the data validation to check if it is a value among those in "RANGE_VALID_COMBINATIONS_FOR_FREE_TEXT_WITH_ITEM_2." In this way I would allow for an inexperienced user to either write "sv" or "vs" to represent the combinated scope "vs" as either really equals "vs".

Please compare this between H4 and H10.

These are the options:


A. To have the data validation to hault until the worksheet event has run and ordered the characters.

B. Skip the Excel data validation as in H4 and add validation by vba code. This should of course run after the worksheet change event has run.


How could it be done? Please tell me in case there still is some confusion!

[Kenneth Hobs]

If the users can not pick from the validation list, they are in dire need of training. In the past, I have just used the selection event to present a userform with a listbox or combobox. That method gives you lots of flexibility and holds your users hands more too.

In the worksheet code:
[VBA]Public Sub Worksheet_Change(ByVal Target As Range)
orderCharKen Target
End Sub[/VBA]
In a Module or the existing one:
[VBA]Sub orderCharKen(ByVal Target As Range)
Dim disOrder As String
Dim newOrder As String
Dim disT As String
Dim disC As String
Dim disD As String
Dim disV As String
Dim disH As String
Dim disS As String
Dim newT As String
Dim newC As String
Dim newD As String
Dim newV As String
Dim newH As String
Dim newS As String
Dim vRange As Range, fRange As Range

If Application.Intersect(Target, Range("ORDER_CHARACTERS")) Is Nothing _
Or Target.Count <> 1 Then Exit Sub

Application.EnableEvents = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

disOrder = LCase(Target.Value)

disT = "t"
disC = "c"
disD = "d"
disV = "v"
disH = "h"
disS = "s"

If Len(disOrder) <> Len(Application.Substitute(disOrder, disT, "")) Then
newT = disT
Else
newT = ""
End If
If Len(disOrder) <> Len(Application.Substitute(disOrder, disC, "")) Then
newC = disC
Else
newC = ""
End If
If Len(disOrder) <> Len(Application.Substitute(disOrder, disD, "")) Then
newD = disD
Else
newD = ""
End If
If Len(disOrder) <> Len(Application.Substitute(disOrder, disV, "")) Then
newV = disV
Else
newV = ""
End If
If Len(disOrder) <> Len(Application.Substitute(disOrder, disH, "")) Then
newH = disH
Else
newH = ""
End If
If Len(disOrder) <> Len(Application.Substitute(disOrder, disS, "")) Then
newS = disS
Else
newS = ""
End If

newOrder = newT & newC & newD & newV & newH & newS
'=INDIRECT(INDEX(VERIFICATION_RANGE_FOR_FREE_TEXT,RELATIVE_POSITION_WORKSHE ET_A))
Set vRange = Evaluate("=INDIRECT(INDEX(VERIFICATION_RANGE_FOR_FREE_TEXT,RELATIVE_POSITIO N_WORKSHEET_A))")
Set fRange = vRange.Find(newOrder)
If Not fRange Is Nothing Then Target.Value2 = newOrder

If Target.Value2 <> newOrder Then
MsgBox "Invalid Entry: " & Target.Value2 & vbLf & _
"Sorted Entry: " & newOrder, vbCritical
Target.Value2 = Empty
Target.Select
End If

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub[/VBA]

[Rejje]

I have tried this code in the example workbook i posted here but no msgbox appers with an invalid entry.

Could you please post the same book again with your code here so that I can see how it's done?

[Bob Phillips]

Remove the DV and use

[vba]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim disOrder As String
Dim newOrder As String
Dim disT As String
Dim disC As String
Dim disD As String
Dim disV As String
Dim disH As String
Dim disS As String
Dim newT As String
Dim newC As String
Dim newD As String
Dim newV As String
Dim newH As String
Dim newS As String
Dim rngTes As Range

With Application

.EnableEvents = False
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

If Target.Count = 1 Then

If Not Application.Intersect(Target, Range("ORDER_CHARACTERS")) Is Nothing Then

disOrder = LCase(Target.Value)

disT = "t"
disC = "c"
disD = "d"
disV = "v"
disH = "h"
disS = "s"

If InStr(disOrder, disT) > 0 Then newT = disT
If InStr(disOrder, disC) > 0 Then newC = disC
If InStr(disOrder, disD) > 0 Then newD = disD
If InStr(disOrder, disV) > 0 Then newV = disV
If InStr(disOrder, disH) > 0 Then newH = disH
If InStr(disOrder, disS) > 0 Then newS = disS

Target.Value2 = newT & newC & newD & newV & newH & newS

Select Case LCase(Target.Offset(0, -3).Value)

Case "item 1": Set Rng = Me.Range("RANGE_VALID_COMBINATIONS_FOR_FREE_TEXT_WITH_ITEM_1")

Case "item 2": Set Rng = Me.Range("RANGE_VALID_COMBINATIONS_FOR_FREE_TEXT_WITH_ITEM_2")

Case "item 3": Set Rng = Me.Range("RANGE_VALID_COMBINATIONS_FOR_FREE_TEXT_WITH_ITEM_3")

Case "no item": Set Rng = Me.Range("RANGE_VALID_COMBINATIONS_FOR_FREE_TEXT_WITH_NO_ITEM")

End Select

If IsError(Application.Match(Target.Value, Rng, 0)) Then MsgBox "Invalid Value"
End If
End If

With Application

.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub[/vba]

[Kenneth Hobs]

This is the same code only I added a 2nd MsgBox showing valid entries possible if they enter invalid results after it sorts it. Otherwise, it is the same code. Obviously, it only works in the range of H4:H10 in your range of order_characters. You have to remove the data validation for the other cells in the range for it to work in those.