Cell Validation using List and Variable

B

Bryan Whitaker

I have a situation in creating a template where I want
limit the user to 1 of 8 different ways of formatting the
data they put into a cell but also want them to have the
ability to replace a "symbolic" value with a literal
whole number.
First, I've tried and failed to achieve this using cell
validation. Perhaps I set the CV up incorrectly, so can
anyone shed any light on how I can achieve this using CV?
Second, If cell validation isn't applicable here, is
there a solution that doesn't involve building and
embedding a macro in the template?
Finally, if the only solution is to build and embed a
macro, does anyone know if there is something like that
already posted to this forum somewhere or have a quick
and dirty solution that will work with the set of values
I have below:

X(&)
Z(&)
S9(&) C3
S9(&)V9(&) C3
S9(&) C
S9(&)V9(&) C
S9(&) C4
S9(&)V9(&) C4

Once the user selects the correct format, I want them to
be able to replace the symbolic '&' with a whole number.
I would like to have some sort of cell validation
involved that only allows 1 of these formats with the
requirement that the symbolic becomes a number and a STOP
event if the user doesn't meet all the requirements.
Also, I need for an empty cell to pass validation as well.

Any takers? Thanks in advance for your time.
 
M

Mark Graesser

Bryan
You could have your user enter thier number in one column, and select the format in a seperate column(using validation). Then in a third colum you can use

=SUBSTITUTE(B1,"&",A1

To put the two together. So if they enter "1234" in A1, and select "S9(&)V9(&) C3" in B1, the formula will return "S9(1234)V9(1234) C3"

Good Luck
Mark Graesse
[email protected]

----- Bryan Whitaker wrote: ----

I have a situation in creating a template where I want
limit the user to 1 of 8 different ways of formatting the
data they put into a cell but also want them to have the
ability to replace a "symbolic" value with a literal
whole number
First, I've tried and failed to achieve this using cell
validation. Perhaps I set the CV up incorrectly, so can
anyone shed any light on how I can achieve this using CV
Second, If cell validation isn't applicable here, is
there a solution that doesn't involve building and
embedding a macro in the template
Finally, if the only solution is to build and embed a
macro, does anyone know if there is something like that
already posted to this forum somewhere or have a quick
and dirty solution that will work with the set of values
I have below

X(&
Z(&
S9(&) C
S9(&)V9(&) C
S9(&)
S9(&)V9(&)
S9(&) C
S9(&)V9(&) C

Once the user selects the correct format, I want them to
be able to replace the symbolic '&' with a whole number.
I would like to have some sort of cell validation
involved that only allows 1 of these formats with the
requirement that the symbolic becomes a number and a STOP
event if the user doesn't meet all the requirements.
Also, I need for an empty cell to pass validation as well

Any takers? Thanks in advance for your time
 
G

Guest

Anybody want to take a stab at this one? The suggestion
of using SUBSTITUTE won't work with this application. I
appreciate the suggestion though.
 
S

Stephen Bullen

Hi Bryan,
Finally, if the only solution is to build and embed a
macro, does anyone know if there is something like that
already posted to this forum somewhere or have a quick
and dirty solution that will work with the set of values
I have below:

X(&)
Z(&)
S9(&) C3
S9(&)V9(&) C3
S9(&) C
S9(&)V9(&) C
S9(&) C4
S9(&)V9(&) C4

Once the user selects the correct format, I want them to
be able to replace the symbolic '&' with a whole number.
I would like to have some sort of cell validation
involved that only allows 1 of these formats with the
requirement that the symbolic becomes a number and a STOP
event if the user doesn't meet all the requirements.
Also, I need for an empty cell to pass validation as well.
Click to expand...

Unfortunately, this is beyond the capabilities of the data validation, primarily because we don't have a worksheet function that can
perform pattern-matching (i.e. similar to VB's Like function). If you use '#' instead of '&' in your list of allowed entries, the
following VBA user-defined-function will do the validation:

Public Function IsInList(StringToCheck As String, List As Range) As Boolean

Dim oCell As Range

If StringToCheck = "" Then
IsInList = True
Else
For Each oCell In List
If StringToCheck Like oCell.Value Then
IsInList = True
Exit For
End If
Next
End If

End Function

Unfortunately, Data Validation doesn't allow us to use VBA UDFs within the validation formula, so there are two choices:

1. In a separate column alongside the cell where the user is entering the data, you could have a formula like (where B2 is the cell
that the user is typing into and H2:H8 contain the list of allowed formats with #'s instead of &'s):

=IF(IsInList(B2,H2:H9),"","Entry does not match one of the required formats.")

2. In the Worksheet_Change event, check the entry a pop up a message box.

Private Sub Worksheet_Change(ByVal Target As Range)

'Check anything in column B
If Target.Column = 2 Then
If Not IsInList(Target.Value, Range("H2:H9")) Then
MsgBox "Entry is not in one of the required formats."

Application.EnableEvents = False
Target.Value = Empty
Application.EnableEvents = True

End If
End If
End Sub

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.co.uk
 
D

Dave Peterson

One more choice may be to put the UDF formula in an adjacent cell (and hide
it???). Then use that cell for the custom data|validation formula.

If the cell that contained the formula were in C2, then use Data|validation on
B2 with a custom formula of: =C2
 
Top