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

[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

 

[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

 

[alinpsy10]

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

 

[isabelle]

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

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.

 

[alinpsy10]

Hello again,

My drop-down list is a list of validation.

 

[isabelle]

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 :

 

[alinpsy10]

Hello again Isabelle,

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

Alin

 

[isabelle]

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 :

 

[alinpsy10]

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