Can someone help me modify this code to Allow
Operator:=xlEqual, Formula1:="8" Or Operator:=xlEqual, Formula1:="12"
Range("B5,B7,B12,B14,B19,B21,B26,B28").Select
Range("B28").Activate
With Selection.Validation
.Add Type:=xlValidateTextLength, AlertStyle:=xlValidAlertStop, _
Operator:=xlEqual, Formula1:="8"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub

I'm not sure what you are lookinf for Mike. Maybe
Operator:=xlBetween, Formula1:="8", Formula2:="12"

Maybe if you clarified what you are trying to achieve, we could figure
something out.

"Mike" wrote:

Can someone help me modify this code to Allow
Operator:=xlEqual, Formula1:="8" Or Operator:=xlEqual, Formula1:="12"
Range("B5,B7,B12,B14,B19,B21,B26,B28").Select
Range("B28").Activate
With Selection.Validation
.Add Type:=xlValidateTextLength, AlertStyle:=xlValidAlertStop, _
Operator:=xlEqual, Formula1:="8"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub

JLGWhiz,
Here it goes
I have an Item Sheet that our Managers use to fill out and e-mail into our
Corp Office Admin. The item sheet is for New Items meaning the items UPC
Number needs to be added to our database. I would like a msgbox to display If
the cells value is Not = to 8 Or 12

12345678 - No msg
1111111 - msgbox upc number must be 8 0r 12 digits long
123456781234 - No msg

Does this help
"JLGWhiz" wrote:

I'm not sure what you are lookinf for Mike. Maybe
Operator:=xlBetween, Formula1:="8", Formula2:="12"

Maybe if you clarified what you are trying to achieve, we could figure
something out.

"Mike" wrote:

Can someone help me modify this code to Allow
Operator:=xlEqual, Formula1:="8" Or Operator:=xlEqual, Formula1:="12"
Range("B5,B7,B12,B14,B19,B21,B26,B28").Select
Range("B28").Activate
With Selection.Validation
.Add Type:=xlValidateTextLength, AlertStyle:=xlValidAlertStop, _
Operator:=xlEqual, Formula1:="8"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub

I have just come up with this. Maybe you could offer some alternative or
away to make shorter
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const purItem1 = "B5"
Const subItem1 = "B7"
Const purItem2 = "B12"
Const subItem2 = "B14"
Const purItem3 = "B19"
Const subItem3 = "B21"

If Len(Range(purItem1).Text) = 0 _
Or Len(Range(purItem1).Text) = 8 _
Or Len(Range(purItem1).Text) = 12 Then
'Do nothing if = to
Else
MsgBox "false"
Range(purItem1).Value = ""
End If
If Len(Range(subItem1).Text) = 0 _
Or Len(Range(subItem1).Text) = 8 _
Or Len(Range(subItem1).Text) = 12 Then
Else
MsgBox "false"
Range(subItem1).Value = ""
End If
If Len(Range(purItem2).Text) = 0 _
Or Len(Range(purItem2).Text) = 8 _
Or Len(Range(purItem2).Text) = 12 Then
Else
MsgBox "false"
Range(purItem2).Value = ""
End If
If Len(Range(subItem2).Text) = 0 _
Or Len(Range(subItem2).Text) = 8 _
Or Len(Range(subItem2).Text) = 12 Then
Else
MsgBox "false"
Range(subItem2).Value = ""
End If
If Len(Range(purItem3).Text) = 0 _
Or Len(Range(purItem3).Text) = 8 _
Or Len(Range(purItem3).Text) = 12 Then
Else
MsgBox "false"
Range(purItem3).Value = ""
End If
If Len(Range(subItem3).Text) = 0 _
Or Len(Range(subItem3).Text) = 8 _
Or Len(Range(subItem3).Text) = 12 Then
Else
MsgBox "false"
Range(subItem3).Value = ""
End If
End Sub

"JLGWhiz" wrote:

I'm not sure what you are lookinf for Mike. Maybe
Operator:=xlBetween, Formula1:="8", Formula2:="12"

Maybe if you clarified what you are trying to achieve, we could figure
something out.

"Mike" wrote:

Can someone help me modify this code to Allow
Operator:=xlEqual, Formula1:="8" Or Operator:=xlEqual, Formula1:="12"
Range("B5,B7,B12,B14,B19,B21,B26,B28").Select
Range("B28").Activate
With Selection.Validation
.Add Type:=xlValidateTextLength, AlertStyle:=xlValidAlertStop, _
Operator:=xlEqual, Formula1:="8"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub

The Len() function would seem to be best for what you want to do. You could
probably shorten your code if you used an array for your list of cells, then
you could do something like:

myArray = Array("B5", "B7", "B12"...etc.)
For i = LBound(myArray) To UBound(myArray)
If Len(Range(myArray(i))) < "8" Or Len(Range(myArray(i))) < "12" Then
'Take some action
End If
Next

"Mike" wrote:

I have just come up with this. Maybe you could offer some alternative or
away to make shorter
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const purItem1 = "B5"
Const subItem1 = "B7"
Const purItem2 = "B12"
Const subItem2 = "B14"
Const purItem3 = "B19"
Const subItem3 = "B21"

If Len(Range(purItem1).Text) = 0 _
Or Len(Range(purItem1).Text) = 8 _
Or Len(Range(purItem1).Text) = 12 Then
'Do nothing if = to
Else
MsgBox "false"
Range(purItem1).Value = ""
End If
If Len(Range(subItem1).Text) = 0 _
Or Len(Range(subItem1).Text) = 8 _
Or Len(Range(subItem1).Text) = 12 Then
Else
MsgBox "false"
Range(subItem1).Value = ""
End If
If Len(Range(purItem2).Text) = 0 _
Or Len(Range(purItem2).Text) = 8 _
Or Len(Range(purItem2).Text) = 12 Then
Else
MsgBox "false"
Range(purItem2).Value = ""
End If
If Len(Range(subItem2).Text) = 0 _
Or Len(Range(subItem2).Text) = 8 _
Or Len(Range(subItem2).Text) = 12 Then
Else
MsgBox "false"
Range(subItem2).Value = ""
End If
If Len(Range(purItem3).Text) = 0 _
Or Len(Range(purItem3).Text) = 8 _
Or Len(Range(purItem3).Text) = 12 Then
Else
MsgBox "false"
Range(purItem3).Value = ""
End If
If Len(Range(subItem3).Text) = 0 _
Or Len(Range(subItem3).Text) = 8 _
Or Len(Range(subItem3).Text) = 12 Then
Else
MsgBox "false"
Range(subItem3).Value = ""
End If
End Sub

"JLGWhiz" wrote:

I'm not sure what you are lookinf for Mike. Maybe
Operator:=xlBetween, Formula1:="8", Formula2:="12"

Maybe if you clarified what you are trying to achieve, we could figure
something out.

"Mike" wrote:

Can someone help me modify this code to Allow
Operator:=xlEqual, Formula1:="8" Or Operator:=xlEqual, Formula1:="12"
Range("B5,B7,B12,B14,B19,B21,B26,B28").Select
Range("B28").Activate
With Selection.Validation
.Add Type:=xlValidateTextLength, AlertStyle:=xlValidAlertStop, _
Operator:=xlEqual, Formula1:="8"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub