Selection of dropdown list value with a checkbox

iasiddiqui

New Member
Joined
May 21, 2014
Messages
22
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:

ABC
leftrightleftrightleftright
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?
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,768
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

New Member
Joined
May 21, 2014
Messages
22
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

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Perhaps:-
Change "Rng" address to suit
Code:
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

New Member
Joined
May 21, 2014
Messages
22

ADVERTISEMENT

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

iasiddiqui

New Member
Joined
May 21, 2014
Messages
22
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

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841

ADVERTISEMENT

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

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

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Try

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

iasiddiqui

New Member
Joined
May 21, 2014
Messages
22
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

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,810
Messages
5,525,017
Members
409,615
Latest member
papaluigi94

This Week's Hot Topics

Top