VBA to do 2 Validations on a cell

[jostj]

Hi all,

I am having issues with figuring out how to do more than one validation.
I have files that are shared with people to use, but i am trying to limit what can be entered.

On a tab, i have one column where information is entered. what should be entered is based on a dropdown list (which i have setup on a validation). the other thing that should be allowed to be entered is a an 8 digit number. when something else is entered, i need to have a popup message appear to tell the user to fix the error.
I have no idea how to effectively enter in the restriction in VBA for this.

Below is the VBA i have started:

1. The problem is that if one of the drop-downs is chosen, then i get this error message (because the dropdown list has text that is more than 8 characters in length).

2. The one other problem is that if the cell is left blank, i still receive the popup message. how do i avoid this from showing up.

3. I am also looking to do something like this for a range of cells (from F5:F100).

Private Sub Worksheet_Change()

Dim stue As String
stue = "f5"

Application.EnableEvents = True
If Len(Range("f5")) <> 8 Then
    MsgBox "there is an error in cell " & stue & " "
End If
End Sub

does anyone have any suggestions?

thanks in advance!

-Jason

 

[jostj]

sorry, i should be more clear. ultimately, i need to have this validation span from cell f5 to f100, but when there is an error that shows up, the popup directs the user to which cell the error was created in.

it would allow more than 8 characters in order to allow for the drop down list to be entered in the cell as well.

so, i wouldnt be able to directly enter a simple message into the validation> error msg section.

 

[charlie1105]

You should be able to do it by a combination of data validation (to get the list, but don't turn off the error message in data validation, so it will allow you to enter a different value) and then use the worksheet change event to do the validation - check whether target is a) one of the items in the list, or b) an 8 digit value - if it's not one of these then display an error message.

 

[jostj]

Thanks Charlie.
I think i'm getting somewhere.
Would you be able to help show me how to setup VBA for a validation check?