Cell Validation using List and Variable

  • Thread starter Thread starter Bryan Whitaker
  • Start date Start date
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.
 
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
(e-mail address removed)

----- 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
 
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.
 
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
 
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
 
Back
Top