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

 

[Von Pookie]

Use the Target variable. It is your friend in an Event macro such as Worksheet_Change.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub

Application.EnableEvents = False

'run code depending on what column the changed cell is in
Select Case Target.Column
    Case 5 'column E
        'code for col E goes here
    Case 6 'column F
        'if changed cell is within the range F5:F100
        If Not Intersect(Target, [F5:F100]) Is Nothing Then
            'if cell is NOT empty and length is <>8
            If Target <> "" And Len(Target) <> 8 Then
                'display msg
                MsgBox "There is an error in cell " & Target.Address(0, 0) & "."
            End If
        End If
    Case 7 'column G
        'code for col G goes here
End Select

Application.EnableEvents = True

End Sub

Using a Select Case statement as I have in the above example allows you to easily add more code to check other columns.

 

[jostj]

Hi Kristy, i am little lost still on this code. could you please explain a little more. it could be that im still fairly novice on VBA.

thanks for your help!

 

[hmk]

Hi

Based on your explaination, you need not a VBA code as the Data>Validation does all what you need exactely.

 

[jostj]

it does, but the problem is that i need two types of validations for one cell. this doesnt appear possible.

 

[hmk]

you can use validation and VBA code.

Could you pls tell me clearly what are the 2 types validation you need ?

 

[jostj]

yes, lets say i am working on a tab titled "sheet1" and the cell i wanted to work on is "f5".

what i am attempting to do is have a cell that allows an entry of 8 digits into the cell, or a validation list to be entered (the validation list has entries that are longer than 8 digits). i want to be able to choose from either one of those.

when something else is entered into the cell (more than 8 characters in length) i need a pop up message to appear to let the person know there is an error and in which cell.
this will notify the user to go back and correct this entry.

in case its important to note, the validation list is located on cells N110:N127.


thanks for your help!

 

[mikerickson]

A custom Validation formula like
=OR(LEN(A1)=8,ISNUMBER(MATCH(A1,SheetName!$N$110:$N$127,0)))

should work

 

[jostj]

This would work out great, but the only problem is that in changing the validation to a custom, i lose the ability to have a drop down list available for this cell. I still want to be able to have a drop down to show the cells in N110:N127.

I think that this problem is why i am needing VBA for the solution.

Thanks!

 

[hmk]

you said that you need a pop up msg tells that more than 8 charters are entered and your validation does allow more than 8 charcters.

you need to correct the validation Text Lenth into the numbers of charecters to be allowed. Go to validation>Error Alert and write the msg you want to be appeared to the users if they enter more than 8 digits.