VBA to do 2 Validations on a cell

jostj

New Member
Joined
Nov 6, 2006
Messages
45
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).

Code:
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
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686
Use the Target variable. It is your friend in an Event macro such as Worksheet_Change.

Code:
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

New Member
Joined
Nov 6, 2006
Messages
45
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

Active Member
Joined
Jun 8, 2004
Messages
423
Hi

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

jostj

New Member
Joined
Nov 6, 2006
Messages
45

ADVERTISEMENT

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

hmk

Active Member
Joined
Jun 8, 2004
Messages
423
you can use validation and VBA code.

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

jostj

New Member
Joined
Nov 6, 2006
Messages
45

ADVERTISEMENT

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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,901
A custom Validation formula like
=OR(LEN(A1)=8,ISNUMBER(MATCH(A1,SheetName!$N$110:$N$127,0)))

should work
 

jostj

New Member
Joined
Nov 6, 2006
Messages
45
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

Active Member
Joined
Jun 8, 2004
Messages
423
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,387
Messages
5,595,884
Members
414,029
Latest member
mrwilker

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top