ListBox & Data Validation Question

[vzachin]

hi,
I have dialog boxes that asks the user to verify the entries in Cell A2 & A4.
In Cell A2, the user can choose to leave the cell blank, or enter a Type or type in the word "EXIT" to stop the macro.

I have 2 problems here. The field in A2 has Data Validation (Cells A19:A43). I want the user to only use these entries.I would try a combo-list but am not familiar with how to set it up.

The 2nd problem is if the user types in the word "EXIT" in lower case, I want the macro to stop.

The last question I have is:
The entry for Cell A4 is limited to a text length of 7 characters with Y and/or X. Is there a way to have Data Validation for this? Cell A4 cannot be blank.

The coding I have is functional, but clumsy at best.


Sub test2()

Dim Cell As Range

Sheets("Type").Activate
For Each Cell In Range("A2")
If Cell <> Empty Then
Cell = InputBox("If the Type Code in Cell A2 is correct, please Click OK, otherwise enter the Type Code below and then Click OK.To Stop the Macro, type in EXIT below and then Click OK ")
Else
If Cell = Empty Then
'Cell = InputBox("Please enter the Type Code or leave it Blank as is or the word EXIT to stop the Macro in " & Cell.Address(0, 0))
Cell = InputBox("The Type Code in Cell A2 is Blank. If this is what you want, Click OK. Otherwise enter the Type Code below and then Click Ok. To Stop the Macro, type in EXIT below and then Click OK")
If Range("A2").Value = "EXIT" Then
MsgBox "Transaction Cancelled"
Exit Sub
End If
If Cell <> Empty Then test2
End If
If Range("A2").Value = "EXIT" Then
MsgBox "Transaction Cancelled"
Exit Sub
End If
End If
Next
If Range("A2").Value = "EXIT" Then
MsgBox "Transaction Cancelled"
Exit Sub
End If


For Each Cell In Range("A4")
If Cell <> Empty Then
Cell = InputBox("If the Coding is correct, please Click OK, otherwise enter the data below and then Click OK")
Else
If Cell = Empty Then
Cell = InputBox("Please enter CODING or the word EXIT missing from " & Cell.Address(0, 0))
If Cell = Empty Then test2
End If
End If
Next

If Range("A4").Value = "EXIT" Then
MsgBox "Transaction Cancelled"
Exit Sub
End If

End Sub



thanks
zach

[austenr]

Not sure if this is what you are looking for or not.

If you go to the Validation in Excel, you can select under the settings tab, Text Length, Less than or equal to 7. You could record a macro to see what the code would be. If you want to test for 'X' or 'Y' as well maybe someone will be along that can help you with that. HTH

I ran a quick macro to see what the code looked like.

With Selection.Validation
.Delete
.Add Type:=xlValidateTextLength, AlertStyle:=xlValidAlertStop, _
Operator:=xlEqual, Formula1:="7"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub

[mdmackillop]

Hi Zach
Here's your worksheet using a combobox.
The combobox has ListRow "ty" and LinkedCell "A2" values set in its Properties sheet.
A4 uses a worksheet event to check for valid data.

[vzachin]

hi md & austenr

not exactly what i had in mind, but i'll see if i can work around it. thanks for your thoughts on this.
and thanks md for showing me how to do a combobox. i always wondered how that works.

on to my next question in a subsequent post

thanks again
zach