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:

	A		B		C
	left	right	left	right	left	right
upper	?	?	?	?	?	?
lower	?	?	?	?	?	?

<tbody>
</tbody>

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?

 

[Jeffrey Mahoney]

Well, if you started with a formula in the cells that checked the status of a checkbox and showed Present when True, that would work until you overwrote the formula and wanted to reset the row.

A macro is called for if you want to have the formula return. What would be the trigger for the cells to be blank again; unchecking the checkbox?

Jeff

 

[iasiddiqui]

the cells with the "?" have a dropdown list in them so i dont think i can enter a formula in those cells. and when i said i wanted to "manually change" them, if the checkbox works and "present" is selected from the list i want to be able to select that cell and select something else from the list. all i care about is for the checkbox to automatically populate the cells with the dropdown lists and also be able to manually select in those cells what i want even after checking the checkbox.

 

[MickG]

Perhaps:-
Change "Rng" address to suit

Private Sub CheckBox1_Click()
Dim Rng As Range, Dn As Range
Set Rng = Range("C2:G5")
For Each Dn In Rng
 If Dn.SpecialCells(xlCellTypeAllValidation).Count > 0 Then
    Dn.Value = "Present"
 End If
Next Dn
End Sub

 

[iasiddiqui]

sorry, can you explain what you mean by "rng"

 

[iasiddiqui]

can you help me in inputing the range which includes B4:C5, E4:F5 & H4:I5, all of which are on the same sheet, "Sheet1"?

 

[MickG]

"Rng" is a variable (Range Object) Based on the cells "C2:G5", but that's a code is a bit Overkill.

You can do the same thing with the code:-
NB:- make sure the range you want to change are "Validation cells" and are correctly shown in the code below.

Private Sub CheckBox1_Click()
If CheckBox1 = True Then Range("C2:G5").Value = "Present"
End Sub

 

[MickG]

Try

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

 

[iasiddiqui]

Thanks MickG, got it to work...however it doesnt work when i lock the sheet and share it - is there a way to get the macro to run in a locked and shared file? FYI, i didnt use the "Private" at the beginning of the code, does that make a difference?

 

[MickG]

In you unlock just the validation Range and then Protect the sheet, the validation and checkbox still work.
The "Private" is there to specify that the CheckBox code relates specifically to that sheet.