Change states of some checkboxes from drop-down list?

[TheMilkGuy]

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

 

[GS]

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 P14; -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("P12") = True: .Range("P34") = 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("P12") = False: .Range("P34") = 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

 

[TheMilkGuy]

Gary,

Thanks for the quick reply. You were correct, they are forms checkboxes.

How would the code change if P13 were on another worksheet?

Thanks,
Craig

 

[GS]

TheMilkGuy brought next idea :

Gary,

Thanks for the quick reply. You were correct, they are forms checkboxes.

How would the code change if P13 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("P12") = True: .Range("P34") = False
End With

Case "C", "D"
With Sheets("Sheet1") '//edit to suit
.Range("P12") = False: .Range("P34") = 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

 

[TheMilkGuy]

Gary,

Worked like a charm! Thanks for your help (and patience).

Cheers,
Craig

 

[GS]

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