Please help with conditional Validation in MS Excel

MI man

Registered User.
Local time
Today, 08:01
Joined
Nov 23, 2008
Messages
59
Hi everyone,

I'm working on an excel file in which I have to validate one cell when it satisfies a condition in the other cell.
For instance, if cell A is 1, then cell B should be validated to a, b, c (Incelldropdown).
I have tried it through wizard (in excel menu bar "data > validation>custom"), but the limitation is upto 7 IF loops, where as I need at least 16 loops to get my work done.
Trying through VBA, but not working out properly (probably because of my lack of experience in it).
Can anybody please help me out with this..!!!

A lot of thanks in advance..!!
 
You can make a function which returns "a,b,c" when entered 1 in Cel A1.
Code:
public function Doit(intValue as integer) as string
   select case intvalue
   case 1
      doit="a,b,c"
   case else
      doit=""
   end select
end function
Enjoy!
 
Thanks for the reply..!!

One clarification is required. How to insert cell dropdowns into this code.
I have a general code of validation in VBA which somewhat start like:

with range("e10") .validation
.add xlValidateList, xlValidateAlertStop, xlBetween _
"=$a$377:$a$378"
incelldropdown = true
endwith

But I'm unable to frame this into the conditions like IF, Case, While, For, etc.

Help required.

Many Thanks
 
Try this:

Code:
Sub Validation_Example()

Dim Choices As String

' ******************This code tests the value of cell A1******************

Select Case Cells(1, 1)

    Case 1: Choices = "a,b,c"
    Case 2: Choices = "c,d,e"
    Case "yourText": Choices = "a,c,e"
    Case Else: Choices = "e,f,g,h,i,j"

End Select

' ******************Setup validation rules for cell B1******************

With Cells(1, 2).Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:=Choices
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
End With

End Sub
 
Thank you very much Roli001..your code seems to work.!!

However, a small problem, though the code's working properly, with each change, F5 has to be pressed in the enviroment (or option Run in Excel environment) to get those displayed in the dropdown.

For instance,

Condition: A in cell 1 validates the cell 2 with B, and C with D.

I have entered A in cell 1 and the cell 2 sucessfully shows B, but if I delete A and enter C in cell 1, cell 2 must show D (as per the condition), but still the dropdown in cell 2 reflects B instead of D.

Query: is there any way by which celldropdown changes as per cell 1.

I have tried autorun.xlautoactivate, but it doesn't seem to work..!!

Please provide solution...!!!

Many Thanks.!!
 
Put the following code in the VBA SHEET (not the module!) that you are changing the data for cell A1.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

'******If a change happens to cell A1 then run sub Validation_Example******
  
If Target.Address = "$A$1" Then Validation_Example

End sub
Merry Christmas!
 

Users who are viewing this thread

Back
Top Bottom