Complicated Multiple Validation problem

  • Thread starter Thread starter Jagdip Singh Ajimal
  • Start date Start date
J

Jagdip Singh Ajimal

I have a cell, A1, with a date in.

I have a cell, B1, with a dropdownlist on. I put this on using the
data -> validation -> list method. The list is:
- simple
- complex
- complicated

Finally, I have another cell, C1, that also takes dates. When the user
chooses:
- "simple", then I add 15 days to the date in A1, and put this in C1
- "complex", then I add 20 days to the date in A1, and put this in C1
- "complicated", then I clear C1.

My problem is that I want to validate C1 according to above. i.e.
1. If the user has chosen "simple" or "complex", then I want to
disable C1 by using data -> validation -> custom -> =B1<>"complicated"
2. If the user chooses "complicated", then I want to enable C1 and
make sure that C1 is entered as a date.

The problem is with 2. above. I know how to validate C1 to make sure
it is a date. But I also need to make sure that the cell is only
editable when B1="complicated".

I hope I havent confused anyone. Basically, I need two validations on
one cell. How can I do this?
 
Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Address = "$B$1" Then
With Target
Range("C1").Validation.Delete
If .Value = "simple" Then
.Offset(0, 1).Value = .Offset(0, -1).Value + 15
.Offset(0, 1).NumberFormat = "dd mmm yyyy"
ElseIf .Value = "complex" Then
.Offset(0, 1).Value = .Offset(0, -1).Value + 20
.Offset(0, 1).NumberFormat = "dd mmm yyyy"
Else
.Offset(0, 1).ClearContents
.Offset(0, 1).Validation.Add Type:=xlValidateCustom, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, _
Formula1:="=B1<>""complicated"""
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.




--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Hi

Why do you want to enter dates into C1? The value in C1 is fully determined
by values in A1 and B1. So simply enter the formula into C1:
=IF(OR(A1="",B1="complicated",B1=""),"",IF(B1=""simple",1,2)*10+A1)


Arvi Laanemets
 
My VBA is not very good, so I cannot say whether your equation is
correct or not.

But the value in C1 is not always determined by the values in A1 and
B1. e.g.
When:
B1 = "simple", then C1 = A1 + 15 days
B1 = "complex", then C1 = A1 + 20 days
B1 = "complicated", then the user can type in C1.

I want to lock C1 unless B1 = "complicated".
I want to put date validation on C1 if B1 is "complicated".

Its actually slightly worse than this, because I need to catch whether
A1 or B1 has changed. The first time I tried this, I got into an
infinite loop.
 
Hi

This isn't VBA at all. I did give you a worksheet formula which you enter
into call C1. Whenever you change the entry in A1, or the selection in B1,
the function return value changes accordingly (so long as you have the
automatic calculation on). You are trying to travel from your couch to your
bookcase with a plane :-)))
 
Sorry, my mistake. But this doesnt solve my problem.

I could work out the dates quite easily. My problem is that I need two
types of validation on one cell.
1. If the user chooses "complicated", then I need date validation on
C1.
2. Else I need to prevent the user from changing the value in C1 (i.e.
a custom validation).

I am trying Bob Philips example above. But it is taking me a little
while to tweek it to what I need to do.

Thanks for the help.
 
Does my suggestion not work for you?

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Maybe you could help me with a little problem.
Every time I close and then reopen an Excel file, the code stops
working.
For example, I created a workbook with all the code I need. It works
perfectly. But then, as soon as I close and reopen it, it stops
working. It only seems to happen in this page that I am creating (I
have created many other workbooks that work fine with the code).

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
' Application.EnableEvents = False
' If Target.Address = "$B$1" Then
' With Target
' Range("C1").Validation.Delete
' If .Value = "simple" Then
' .Offset(0, 1).Value = .Offset(0, -1).Value + 15
' .Offset(0, 1).NumberFormat = "dd mmm yyyy"
' ElseIf .Value = "complex" Then
' .Offset(0, 1).Value = .Offset(0, -1).Value + 20
' .Offset(0, 1).NumberFormat = "dd mmm yyyy"
' Else
' .Offset(0, 1).ClearContents
' .Offset(0, 1).Validation.Add Type:=xlValidateCustom, _
'
AlertStyle:=xlValidAlertStop, _
' Operator:=xlBetween, _
'
Formula1:="=B1<>""complicated"""
' End If
' End With
' End If'

CalculateDates

ws_exit:
'Application.EnableEvents = True
End Sub

Private Sub CalculateDates()
Dim CalDate As Date

If Range("AM50").Value = "Simple" Then
If Range("AM15").Value <> "" Then
CalDate = DateAdd("d", 15, CDate(Range("AM15")))
If DateDiff("d", CalDate, CDate(Range("AM56"))) <> 0 Then
Range("AM56").Value = CalDate
End If
End If
ElseIf Range("AM50").Value = "Complex" Then
If Range("AM15").Value <> "" Then
CalDate = DateAdd("d", 20, CDate(Range("AM15")))
If DateDiff("d", CalDate, CDate(Range("AM56"))) <> 0 Then
Range("AM56").Value = CalDate
End If
End If
Else
If Range("AM56").Value <> "" Then
Range("AM56").Value = ""
End If
End If
End Sub
 
Back
Top