Need help with data validation

H

HammerJoe

Hi,

I need help with data validation.

I understand the basics of data validation but need help using
formulas to allow user to choose YES as option.

I have three columns ABC and right now I have them with data
validation as list with two options YES and NO.

I need to change it to do this:

In cell D1 there is a code that changes how the columns interact with
each other:

Any column can have NO regardless it’s the YES that has conditions.

If cell D1 contains:

1 - only one of the columns can have YES (ie A - Yes, B and C would
have NO).
3 - Any column can have YES
5 - Either column A or column B can have Yes but not both, it doesn
matter for column C
7 - Either column A or column C can have Yes but not both, it doesn't
matter for column B
9 - Either column B or Column C can have Yes but not both, it doesn’t
matter for column A

Of course I want an in cell drop down with the options for YES or NO
the same way as I have it right now as a LIST.
How can I use this in Data validation?
Thanks so much for any help.

I have a user form that will control this, but the user has the chance
to go directly to the sheet and make changes/select it.

Cheers.
 
R

Rick Rothstein \(MVP - VB\)

What if D1 has 2, 4, 6, or 8 (or higher?)?

Rick


Hi,

I need help with data validation.

I understand the basics of data validation but need help using
formulas to allow user to choose YES as option.

I have three columns ABC and right now I have them with data
validation as list with two options YES and NO.

I need to change it to do this:

In cell D1 there is a code that changes how the columns interact with
each other:

Any column can have NO regardless it’s the YES that has conditions.

If cell D1 contains:

1 - only one of the columns can have YES (ie A - Yes, B and C would
have NO).
3 - Any column can have YES
5 - Either column A or column B can have Yes but not both, it doesn
matter for column C
7 - Either column A or column C can have Yes but not both, it doesn't
matter for column B
9 - Either column B or Column C can have Yes but not both, it doesn’t
matter for column A

Of course I want an in cell drop down with the options for YES or NO
the same way as I have it right now as a LIST.
How can I use this in Data validation?
Thanks so much for any help.

I have a user form that will control this, but the user has the chance
to go directly to the sheet and make changes/select it.

Cheers.
 
H

HammerJoe

Hi,

Good qustion. :)
They are supposed to be code to determine what combination is allowed
between the columns.
It can be anything really, I just thought of using odd numbers. :)
 
H

HammerJoe

Oh I think I understand now what you are asking.
On cell D1 there will only be one of those 'codes' nothing else,
theres no need to check if the value of cell D1 is one of those odd
numbers.
That is done elsewhere.
 
H

HammerJoe

Just eliminate confusion I would like it to check on a row by row
basis, not the whole column range.

So A1 would check for B1 and C1, etc...
I am not sure if this is feasible with Data Validation, otherwise VBA
it is.
Need some ideas please.

Cheers/
 
P

Phillip

Hi,

I need help with data validation.

I understand the basics of data validation but need help using
formulas to allow user to choose YES as option.

I have three columns ABC and right now I have them with data
validation as list with two options YES and NO.

I need to change it to do this:

In cell D1 there is a code that changes how the columns interact with
each other:

Any column can have NO regardless it’s the YES that has conditions.

If cell D1 contains:

1 - only one of the columns can have YES (ie A - Yes, B and C would
have NO).
3 - Any column can have YES
5 - Either column A or column B can have Yes but not both, it doesn
matter for column C
7 - Either column A or column C can have Yes but not both, it doesn't
matter for column B
9 - Either column B or Column C can have Yes but not both, it doesn’t
matter for column A

Of course I want an in cell drop down with the options for YES or NO
the same way as I have it right now as a LIST.
How can I use this in Data validation?
Thanks so much for any help.

I have a user form that will control this, but the user has the chance
to go directly to the sheet and make changes/select it.

Cheers.
Click to expand...

Phillip London UK

This is a VBA solution which works on my test data
using Excel 2000
It does not use Data validation but a command bar dropdown
accessed by a right click in a valid cell, that is cells in column A
B or C
which have a valid code entry in column D.
To test this open a new blank workbook
and in sheet1 cell D1 enter your codes 1 3 5 7 9 down to D5

to enter the code

Right click the sheet1 tab and select view code

Copy and paste the following code

' note next 2 lines should be on one line
Private Sub Worksheet_BeforeRightClick(ByVal Target As Excel.Range,
Cancel As Boolean)
Dim numcode As Long
Dim col As Long
If Target.Rows.Count > 1 Or Target.Columns.Count > 1 Then
Exit Sub
Else
col = Target.Column
End If
numcode = Target.EntireRow.Cells(4)
Select Case col
Case 1, 2, 3
Select Case numcode
Case 1, 3, 5, 7, 9
DoValidation numcode, col, Target
Cancel = True
End Select
End Select
End Sub

Sub DoValidation(nc As Long, cl As Long, T As Range)
Select Case nc
Case 1
If cl = 1 Then
If T.Offset(0, 1) = "Yes" Or T.Offset(0, 2) = "Yes" Then
CreateNo
CommandBars("MyValidation").ShowPopup
Else
CreateYesNo
CommandBars("MyValidation").ShowPopup
End If
ElseIf cl = 2 Then
If T.Offset(0, -1) = "Yes" Or T.Offset(0, 1) = "Yes" Then
CreateNo
CommandBars("MyValidation").ShowPopup
Else
CreateYesNo
CommandBars("MyValidation").ShowPopup
End If
ElseIf cl = 3 Then
If T.Offset(0, -1) = "Yes" Or T.Offset(0, -2) = "Yes" Then
CreateNo
CommandBars("MyValidation").ShowPopup
Else
CreateYesNo
CommandBars("MyValidation").ShowPopup
End If
End If
Case 3
CreateYesNo
CommandBars("MyValidation").ShowPopup
Case 5
If cl = 1 Then
If T.Offset(0, 1) = "Yes" Then
CreateNo
CommandBars("MyValidation").ShowPopup
Else
CreateYesNo
CommandBars("MyValidation").ShowPopup
End If
ElseIf cl = 2 Then
If T.Offset(0, -1) = "Yes" Then
CreateNo
CommandBars("MyValidation").ShowPopup
Else
CreateYesNo
CommandBars("MyValidation").ShowPopup
End If
ElseIf cl = 3 Then
CreateYesNo
CommandBars("MyValidation").ShowPopup
End If


Case 7
If cl = 1 Then
If T.Offset(0, 2) = "Yes" Then
CreateNo
CommandBars("MyValidation").ShowPopup
Else
CreateYesNo
CommandBars("MyValidation").ShowPopup
End If
ElseIf cl = 2 Then
CreateYesNo
CommandBars("MyValidation").ShowPopup

ElseIf cl = 3 Then
If T.Offset(0, -2) = "Yes" Then
CreateNo
CommandBars("MyValidation").ShowPopup
Else
CreateYesNo
CommandBars("MyValidation").ShowPopup
End If
End If
Case 9
If cl = 1 Then
CreateYesNo
CommandBars("MyValidation").ShowPopup
ElseIf cl = 2 Then
If T.Offset(0, 1) = "Yes" Then
CreateNo
CommandBars("MyValidation").ShowPopup
Else
CreateYesNo
CommandBars("MyValidation").ShowPopup
End If

ElseIf cl = 3 Then
If T.Offset(0, -1) = "Yes" Then
CreateNo
CommandBars("MyValidation").ShowPopup
Else
CreateYesNo
CommandBars("MyValidation").ShowPopup
End If
End If
Case Else
End Select
End Sub
Sub CreateNo()
On Error Resume Next
CommandBars("MyValidation").Controls(1).Delete
On Error GoTo 0
'next 2 lines should be on one line
Set cbo =
CommandBars("MyValidation").Controls.Add(msoControlDropdown)
cbo.AddItem "No", 1
cbo.ListIndex = 1 ' select first item
cbo.DropDownLines = 1
cbo.DropDownWidth = 75
cbo_OnAction = "CheckSelection"
End Sub

Sub CreateYesNo()
On Error Resume Next
CommandBars("MyValidation").Controls(1).Delete
On Error GoTo 0
'next 2 lines should be on one line
Set cbo =
CommandBars("MyValidation").Controls.Add(msoControlDropdown)
cbo.AddItem "No", 1
cbo.AddItem "Yes", 1
cbo.ListIndex = 0
cbo.DropDownLines = 2
cbo.DropDownWidth = 75
cbo_OnAction = "CheckSelection"
End Sub



On the VBE menu select Insert Module
and paste this code in

'this is to allow you to start afresh for testing purposes
Sub cleardata()
Sheet1.Range("A1:C5").ClearContents
End Sub


Sub DeleteDropDown()
On Error Resume Next
CommandBars("MyValidation").Delete
End Sub
Sub CreateDropDown()
DeleteDropDown
Set myBar = CommandBars.Add _
(Name:="MyValidation", Position:=msoBarPopup, Temporary:=True)
Set myItem = myBar.Controls.Add(Type:=msoControlDropdown)
With myItem
.AddItem "No", 1
.AddItem "Yes", 2
.DropDownLines = 2
.DropDownWidth = 75
.ListIndex = 0
.OnAction = "CheckSelection"
End With

End Sub
Sub checkselection()
Dim cbo As CommandBarControl
Set cbo = CommandBars.ActionControl
ActiveCell.Value = cbo.Text
End Sub



Return to Excel by pressing ALT F11
Next to the File menu there is a n Excel Icon
Right click the icon and select View Code
This is the ThisWorkbook code window

Paste the following code

Private Sub Workbook_Open()
Call CreateDropDown
Sheet1.Protect contents:=True, Userinterfaceonly:=True
End Sub

I
'The sheet 1 protect code allows the VBA code
to enter data but stops the user fron entering
anything

Finally you have to set up a reference to
Microsoft Office Object Library
Select Tools References in the VB menu and see if at the top of
the list box a refence to Microsoft Office 9,0
Object Library exists with a tick against it
If not scroll down the list until you find it and tick it
and clcik OK
This is a once off job



Save the excel file close it and reopen it and try
right clicking in columns A B and C

The overall result is similar to data validation
 
H

HammerJoe

Thanks for the help.

I studied your code closely and this is what I came up with and it
seems to be working fine, but maybe it can be improved?
I have created Validation List for colums A B and C

In Sheet1 code

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Set Target = Intersect(Target, Range("A1:C59"))
If Not Target Is Nothing Then
stMessage = ValidateYesNo(Target.Column, ActiveSheet.Range("A" &
Target.Row), ActiveSheet.Range("B" & Target.Row), _
ActiveSheet.Range("C" & Target.Row))
If stMessage <> "" Then
MsgBox stMessage
ActiveSheet.Cells(Target.Row, Target.Column).Value = "No"
End If
End If
End Sub

And in the module code :

Function ValidateYesNo(CheckColumn As Integer, ValueH As String,
ValueI As String, ValueJ As String) As String
Dim Check As Integer

ValidateYesNo = ""
If ActiveSheet.Range("D1").Value > 1 Then
Select Case ActiveSheet.Range("D1").Value
Case 3
Check = 0
If ValueH = "Yes" Then Check = Check + 1
If ValueI = "Yes" Then Check = Check + 1
If ValueJ = "Yes" Then Check = Check + 1
If Check > 1 Then
ValidateYesNo = "Only one Yes allowed"
Exit Function
End If

Case 5
Check = 0
If ValueH = "Yes" Then Check = Check + 1
If ValueI = "Yes" Then Check = Check + 1
If Check > 1 Then
ValidateYesNo = "Only one Yes allowed Between A and B"
Exit Function
End If

Case 7
Check = 0
If ValueH = "Yes" Then Check = Check + 1
If ValueJ = "Yes" Then Check = Check + 1
If Check > 1 Then
ValidateYesNo = "Only one Yes allowed Between A and C"
Exit Function
End If

Case 9
Check = 0
If ValueI = "Yes" Then Check = Check + 1
If ValueJ = "Yes" Then Check = Check + 1
If Check > 1 Then
ValidateYesNo = "Only one Yes allowed Between B and C"
Exit Function
End If

End Select
End If
End Function
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Data Validation List (conditional) 2
A data validation question 1
Remove specific cells in a column. 2
Need Help with a VBA subroutine 0
VBA for data validation in 3rd column does not run. I need help. 1
Cell value that changes previously entered value 9
Get Pivot data not reading some cells 0
Need to create an excel template with many dependencies between columns and some prog 0

Top