Hi all,

How can I Eval cell value before allowing the user to move to the next cell.
I.E.

In A1:A6 the user enters a 5-6 Digit reference code (Mixure of
Text&Number), if one of my reserved codes is used (I.E. A1234 or A5678 or
B9999) then a warning pop-up and not allow the user to move to the next cell
on the right.

Either some solution as a formula or VBA will be fine.

Thanks

Laddie.

I didn't test this but it should work. If not post back.

Private Sub Worksheet_Change(ByVal Target As Range)
resRng = Range("A1:A6")
If Target = Intersect(Target, resRng) Then
If Target.Value = A1234 Or Target.Value = A5678 _
Or Target.Value = B9999 Then
goBack = MsgBox("You cannot use a reserved number." _
,vbInformation, "TRY AGAIN")
Target.Value = ""
End If
End If
End Sub

"LaDdIe" wrote:

Hi all,

How can I Eval cell value before allowing the user to move to the next cell.
I.E.

In A1:A6 the user enters a 5-6 Digit reference code (Mixure of
Text&Number), if one of my reserved codes is used (I.E. A1234 or A5678 or
B9999) then a warning pop-up and not allow the user to move to the next cell
on the right.

Either some solution as a formula or VBA will be fine.

Thanks

Laddie.

Forgot to mention that the code goes in the sheet module.

"LaDdIe" wrote:

Hi all,

How can I Eval cell value before allowing the user to move to the next cell.
I.E.

In A1:A6 the user enters a 5-6 Digit reference code (Mixure of
Text&Number), if one of my reserved codes is used (I.E. A1234 or A5678 or
B9999) then a warning pop-up and not allow the user to move to the next cell
on the right.

Either some solution as a formula or VBA will be fine.

Thanks

Laddie.

1. Select the cell range A1:A6
2. Select DataValidationSettings tab
3. In the Allow box, select Custom
4. In the formula box paste this:
=NOT(OR(A1 = "B9999", A1 = "A1234", A1 = "A5678"))

Note that you can optionally add an Input Message and Error Alert. These
respectively display a popup window (tool tip) with a custom message when a
cell in the range is selected and a custom error message if/when one of your
reserved codes is entered.

Greg

"LaDdIe" wrote:

Hi all,

How can I Eval cell value before allowing the user to move to the next cell.
I.E.

In A1:A6 the user enters a 5-6 Digit reference code (Mixure of
Text&Number), if one of my reserved codes is used (I.E. A1234 or A5678 or
B9999) then a warning pop-up and not allow the user to move to the next cell
on the right.

Either some solution as a formula or VBA will be fine.

Thanks

Laddie.

Thanks for looking into it , but it did'nt work, any after thoughts?

"JLGWhiz" wrote:

I didn't test this but it should work. If not post back.

Private Sub Worksheet_Change(ByVal Target As Range)
resRng = Range("A1:A6")
If Target = Intersect(Target, resRng) Then
If Target.Value = A1234 Or Target.Value = A5678 _
Or Target.Value = B9999 Then
goBack = MsgBox("You cannot use a reserved number." _
,vbInformation, "TRY AGAIN")
Target.Value = ""
End If
End If
End Sub

"LaDdIe" wrote:

Hi all,

How can I Eval cell value before allowing the user to move to the next cell.
I.E.

In A1:A6 the user enters a 5-6 Digit reference code (Mixure of
Text&Number), if one of my reserved codes is used (I.E. A1234 or A5678 or
B9999) then a warning pop-up and not allow the user to move to the next cell
on the right.

Either some solution as a formula or VBA will be fine.

Thanks

Laddie.

Thanks Greg, but it did'nt work for me

"Greg Wilson" wrote:

1. Select the cell range A1:A6
2. Select DataValidationSettings tab
3. In the Allow box, select Custom
4. In the formula box paste this:
=NOT(OR(A1 = "B9999", A1 = "A1234", A1 = "A5678"))

Note that you can optionally add an Input Message and Error Alert. These
respectively display a popup window (tool tip) with a custom message when a
cell in the range is selected and a custom error message if/when one of your
reserved codes is entered.

Greg

"LaDdIe" wrote:

Hi all,

How can I Eval cell value before allowing the user to move to the next cell.
I.E.

In A1:A6 the user enters a 5-6 Digit reference code (Mixure of
Text&Number), if one of my reserved codes is used (I.E. A1234 or A5678 or
B9999) then a warning pop-up and not allow the user to move to the next cell
on the right.

Either some solution as a formula or VBA will be fine.

Thanks

Laddie.

Sorry Greg My fault in entering formula, It does work!
Thanks

"Greg Wilson" wrote:

1. Select the cell range A1:A6
2. Select DataValidationSettings tab
3. In the Allow box, select Custom
4. In the formula box paste this:
=NOT(OR(A1 = "B9999", A1 = "A1234", A1 = "A5678"))

Note that you can optionally add an Input Message and Error Alert. These
respectively display a popup window (tool tip) with a custom message when a
cell in the range is selected and a custom error message if/when one of your
reserved codes is entered.

Greg

"LaDdIe" wrote:

Hi all,

How can I Eval cell value before allowing the user to move to the next cell.
I.E.

In A1:A6 the user enters a 5-6 Digit reference code (Mixure of
Text&Number), if one of my reserved codes is used (I.E. A1234 or A5678 or
B9999) then a warning pop-up and not allow the user to move to the next cell
on the right.

Either some solution as a formula or VBA will be fine.

Thanks

Laddie.