Selection of dropdown list value with a checkbox

[iasiddiqui]

I need help trying to implement a check box to automatically select a dropdown list value when checked, but when it is unchecked i would like to be able to select the value from the dropdown list. So, I have:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD][/TD]
[TD]B[/TD]
[TD][/TD]
[TD]C[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]left[/TD]
[TD]right[/TD]
[TD]left[/TD]
[TD]right[/TD]
[TD]left[/TD]
[TD]right[/TD]
[/TR]
[TR]
[TD]upper[/TD]
[TD]?[/TD]
[TD]?[/TD]
[TD]?[/TD]
[TD]?[/TD]
[TD]?[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]lower[/TD]
[TD]?[/TD]
[TD]?[/TD]
[TD]?[/TD]
[TD]?[/TD]
[TD]?[/TD]
[TD]?
[/TD]
[/TR]
</tbody>[/TABLE]

so the cells with "?" have a dropdown list in them, the values for all the dropdown lists are the same (ie "present", "variable", "absent"). I want to place a check box on the side of this table that when selected, it will automatically select "present" in all the dropdown boxes, but if I want to manually change one of them it still allows me to - HOW DO I DO THIS?

 

[iasiddiqui]

ill try that, lets see if it works....another question for you - the checkbox works great now, this is the code i put in

Sub CheckBox22_Click()
Dim Rng As Range, Dn As Range
Set Rng = Range("B4:C5, E4:F5, H4:I5")
For Each Dn In Rng
If Dn.SpecialCells(xlCellTypeAllValidation).Count > 0 Then
Dn.Value = "present and repeatable"
End If
Next Dn
End Sub

how do i get the cells to go blank again if i uncheck the box? would it be an "if" statement in the code?

 

[MickG]

This should do the job:-
I don't really see why you need to check that the range is full of Validation cells, because you would already have set them.

Private Sub CheckBox1_Click()
If CheckBox1 = True Then
    Range("B4:C5, E4:F5, H4:I5").Value = "Present & Presentable"
Else
    Range("B4:C5, E4:F5, H4:I5").Value = vbNullString
End If
End Sub

 

[iasiddiqui]

doesnt seem to work at all when i put that code in, the box checks and unchecks but values dont populate in the validation cells - is there anyway to put the "if...then" statment into the original code?

 

[MickG]

Does this example file work for you ????
https://app.box.com/s/uct1clmp06ml7478cihmesze1jjnekou

NB:- If I placed it in the other code I would still have to check The "True" "False" statement !!!!

 

[iasiddiqui]

this works well, just not sure how to incorporate it into my worksheet - any help would be greatly appreciated (as you maybe able to tell, im a novice in excel - took a training course but that's it. also, ive never coded in my life so learning as i go).

 

[MickG]

If my example code worked for you, then your sheet should also work, check the codes and sheets data for dis-similarities.

 

[iasiddiqui]

did you use a form control check box or an activex one? i used the form control one

 

[MickG]

Good shout!!!
Try this forms code:-
Nb:- Change Checkbox name to suit !!

Sub CheckBox10_Click()
If ActiveSheet.Shapes(Application.Caller).OLEFormat.Object.Value > 0 Then
    Range("B4:C5, E4:F5, H4:I5").Value = "Present & Presentable"
Else
    Range("B4:C5, E4:F5, H4:I5").Value = vbNullString
End If
End Sub

 

[iasiddiqui]

that works...thanks!!!