Data Validation and drop-down list using IF, AND and what else?

  • Thread starter Thread starter alinpsy10
  • Start date Start date
A

alinpsy10

Hi,

I would very much appreciate your help with the following:
- in the same sheet I have a column named "A" with cells A1, A2, A3, A4, one named "B" having cells B1, B2, B3, B4 and one named "C" which is populated with checkboxes.
I need to create a drop-down list which should show the records from "A" but only when 2 conditions are met, one from "B" column and one from "C" (so both conditions should be met).
For instance, when I have "B1" in the column B and checkbox selected in "C1", "A1" should be visible in the drop-down list. For "B2" and checkbox checked in "C2" it should also add "A2" in the drop-down list and so on. If allthe checkboxes are checked in the C column and all the cells populated in B column, the the drop-down list should show "A1", "A2", "A3", "A4". If, inthe example above, the checkbox in "C1" is unchecked, then the value in A1should not be visible in the drop-down list. If the "C1" is checked but novalue in "B1", then the "A1" will not be visible in the drop-down list.

I hope someone can suggest a solution to this.
Thank you very much for help.

Regards,
Alin
 
hello Alin,

Dim Obj As OLEObject
With ActiveSheet
For Each Obj In .OLEObjects
If TypeOf Obj.Object Is MSForms.CheckBox Then
If Obj.Object.Value And .Cells(Obj.TopLeftCell.Row, 2) = "xx"
Then '---->adapt "xx"
y = .Cells(Obj.TopLeftCell.Row, 1) 'add "y" value in the
drop-down list.
End If
End If
Next Obj
End With

isabelle
 
hello Alin,



Dim Obj As OLEObject

With ActiveSheet

For Each Obj In .OLEObjects

If TypeOf Obj.Object Is MSForms.CheckBox Then

If Obj.Object.Value And .Cells(Obj.TopLeftCell.Row, 2) = "xx"

Then '---->adapt "xx"

y = .Cells(Obj.TopLeftCell.Row, 1) 'add "y" value in the

drop-down list.

End If

End If

Next Obj

End With



isabelle
Click to expand...


Hello Isabelle,

Thank you very much for your answer. I'm not so familiar with code and programming so I don't know how the code you've sent me should be properly used.. I understood the "logic" of the code but I don't know what and how shouldI adapt it to my worksheet. In case you could enlighten me and explain me what exactly should I change on that code to match it to my worksheet then I would be grateful to you.

Thank you very much anyway.

Regards,
Alin
 
hello Alin,

what is your drop-down list,
is it
a list of validation
or
an ActiveX combobox
or
a Form combobox

isabelle

Le 2013-08-10 02:18, (e-mail address removed) a écrit :
Hello Isabelle,

Thank you very much for your answer. I'm not so familiar with code and programming so
Click to expand...

I don't know how the code you've sent me should be properly used.

I understood the "logic" of the code but I don't know what and how
should I adapt it to my worksheet.

In case you could enlighten me and explain me what exactly should I
change on that code to match it to my worksheet then I would be grateful
to you.
 
hello Alin,

one last question before changing the macro, your CheckBox was created
from ActiveX or Form?

isabelle

Le 2013-08-10 17:58, (e-mail address removed) a écrit :
 
Hello again Isabelle,

The CheckBox I use is created from Form.
Thank you very much.

Alin
 
hello Alin,

to help you get started with VBA, follow the next link everything is
described very well

http://msdn.microsoft.com/en-us/library/office/ee814737(v=office.14).aspx

Sub Test_MyValidateList()
Dim sh As Shape
Dim MyValidateList As String

With ActiveSheet
For Each sh In .Shapes
If sh.Type = msoFormControl And Left(sh.Name, 5) = "Check" Then
If sh.ControlFormat.Value = 1 And .Cells(sh.TopLeftCell.Row, 2)
= "xx" Then '---->adapt "xx"
MyValidateList = MyValidateList & .Cells(sh.TopLeftCell.Row, 1)
& ", "
End If
End If
Next sh
End With

With Range("G1").Validation '---->adapt range address
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:=Left(MyValidateList, Len(MyValidateList) - 1)
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub

isabelle

Le 2013-08-12 04:25, (e-mail address removed) a écrit :
 
Hi Isabelle,

Thank you very much for your help with this. I will go through that website and hopefully I will learn how to use the formula you've sent me. :-)

Wish you all the best.

Regards,
Alin
 
Back
Top