Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change states of some checkboxes from drop-down list?
Hi folks, long time lurker here using Excel 2010...
I have a drop-down list in cell H8 that contains 4 values (A thru D). Their TRUE/FALSE results are stored in unlocked cells on another protected sheet in the workbook. If A or B are selected, I'd like for Check Box 96 and 97 (on the original page) to check automatically, but still be de-selectable if required. If C or D are selected, I'd like for 96 and 97 to uncheck automatically, but still be selectable if required. I've toyed around with VB code but TBH I don't know what I'm doing. Admission is the first step, right? haha Many thanks, Craig |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change states of some checkboxes from drop-down list?
TheMilkGuy presented the following explanation :
Hi folks, long time lurker here using Excel 2010... I have a drop-down list in cell H8 that contains 4 values (A thru D). Their TRUE/FALSE results are stored in unlocked cells on another protected sheet in the workbook. If these are 'linked' to your Forms controls then just change their values to whatever you want the control to display. (See sample below) If A or B are selected, I'd like for Check Box 96 and 97 (on the original page) to check automatically, but still be de-selectable if required. If C or D are selected, I'd like for 96 and 97 to uncheck automatically, but still be selectable if required. I've toyed around with VB code but TBH I don't know what I'm doing. Admission is the first step, right? haha Many thanks, Craig This code goes in the code module for the sheet containing the check boxes you want to toggle. (Right-click the sheet tab and select 'View code') I put 4 ActiveX checkboxes (1:4) and 4 Forms check boxes on a sheet. (Your post suggests you're using the latter but I'll show code for both types) A dropdown list (A,B,C,D) is in H8. The linked cells for the Forms controls are P1:P4; -change this location in the code to match your model. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$H$8" Then '//BETTER TO USE NAMED RANGE HERE With Me Select Case Target.Value Case "A", "B" '//Forms control links With Sheets("Sheet1") '//edit to suit .Range("P1:P2") = True: .Range("P3:P4") = False End With '//ActiveX controls .CheckBox1.Value = True: .CheckBox2.Value = True .CheckBox3.Value = False: .CheckBox4.Value = False Case "C", "D" '//Forms control links With Sheets("Sheet1") '//edit to suit .Range("P1:P2") = False: .Range("P3:P4") = True End With '//ActiveX controls .CheckBox1.Value = False: .CheckBox2.Value = False .CheckBox3.Value = True: .CheckBox4.Value = True End Select End With End If End Sub -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change states of some checkboxes from drop-down list?
Gary,
Thanks for the quick reply. You were correct, they are forms checkboxes. How would the code change if P1:P3 were on another worksheet? Thanks, Craig On Monday, April 30, 2012 11:28:40 AM UTC-3, TheMilkGuy wrote: Hi folks, long time lurker here using Excel 2010... I have a drop-down list in cell H8 that contains 4 values (A thru D). Their TRUE/FALSE results are stored in unlocked cells on another protected sheet in the workbook. If A or B are selected, I'd like for Check Box 96 and 97 (on the original page) to check automatically, but still be de-selectable if required. If C or D are selected, I'd like for 96 and 97 to uncheck automatically, but still be selectable if required. I've toyed around with VB code but TBH I don't know what I'm doing. Admission is the first step, right? haha Many thanks, Craig |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change states of some checkboxes from drop-down list?
TheMilkGuy brought next idea :
Gary, Thanks for the quick reply. You were correct, they are forms checkboxes. How would the code change if P1:P3 were on another worksheet? Thanks, Craig The tests I did with code code had everything on Sheets("Sheet1"). I commented the lines to edit to suit your model, and so all you need to do is substitute your sheetname for "Sheet1" in the code. Obviously, the range addresses also need to be edited to your linked cells. (Both Case scenarios) Also, the procedure can be revised as follows: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$H$8" Then '//BETTER TO USE NAMED RANGE HERE Select Case Target.Value Case "A", "B" With Sheets("Sheet1") '//edit to suit .Range("P1:P2") = True: .Range("P3:P4") = False End With Case "C", "D" With Sheets("Sheet1") '//edit to suit .Range("P1:P2") = False: .Range("P3:P4") = True End With End Select End With End If End Sub Take note of my comment to name "$H$8" so you're not hard-coding its address. This will make your code maintenance free if you add/remove cols/rows that affect its location. After naming it revise the line of code as follows... If Target = Range(<MyName) Then ...where you substitute <MyName with a string value containing the name you gave $H$8. I recommend using local scope for the name. To do this via the NameBox left of the FormulaBar, wrap the sheetname of $H$8 in apostrophes followed by the exclamation character and the name. Example 'Sheet Name'!MyName -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change states of some checkboxes from drop-down list?
Gary,
Worked like a charm! Thanks for your help (and patience). Cheers, Craig On Tuesday, May 1, 2012 12:27:42 AM UTC-3, GS wrote: TheMilkGuy brought next idea : Gary, Thanks for the quick reply. You were correct, they are forms checkboxes. How would the code change if P1:P3 were on another worksheet? Thanks, Craig The tests I did with code code had everything on Sheets("Sheet1"). I commented the lines to edit to suit your model, and so all you need to do is substitute your sheetname for "Sheet1" in the code. Obviously, the range addresses also need to be edited to your linked cells. (Both Case scenarios) Also, the procedure can be revised as follows: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$H$8" Then '//BETTER TO USE NAMED RANGE HERE Select Case Target.Value Case "A", "B" With Sheets("Sheet1") '//edit to suit .Range("P1:P2") = True: .Range("P3:P4") = False End With Case "C", "D" With Sheets("Sheet1") '//edit to suit .Range("P1:P2") = False: .Range("P3:P4") = True End With End Select End With End If End Sub Take note of my comment to name "$H$8" so you're not hard-coding its address. This will make your code maintenance free if you add/remove cols/rows that affect its location. After naming it revise the line of code as follows... If Target = Range(<MyName) Then ..where you substitute <MyName with a string value containing the name you gave $H$8. I recommend using local scope for the name. To do this via the NameBox left of the FormulaBar, wrap the sheetname of $H$8 in apostrophes followed by the exclamation character and the name. Example 'Sheet Name'!MyName -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Change states of some checkboxes from drop-down list?
You're very welcome! ..always glad to help! I appreciate the
feedback... -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I change a drop down list? | Excel Discussion (Misc queries) | |||
Refining a list of states into simple territories | Excel Discussion (Misc queries) | |||
Display Excel 2007 data by states using United States (US) map | Excel Discussion (Misc queries) | |||
How to Sort Customer List with Specific States | New Users to Excel | |||
change info in other cells when i change a number in a drop list? | Excel Discussion (Misc queries) |