Macro to check "option button" and "check box"

[John]

Hi Everyone,

I'm trying to write a macro to look one spreadsheet (i.e.
input worksheet) and then go to a "model" worksheet to
check a series of "option button" or "check box" depending
on the data in the input worksheet.

Can this be done? Any help (references) will be greatly
appreciated.

thanks in advance!
John

 

[jeff]

Hi,

the following take the value of cell A1 on sheet1
and accordingly checks appropriate checkboxes and
optionbuttons on sheet2. (You may have to adjust
the "Sheet2." to "Sheet1." or "..3" etc.

Remember Optionbuttons allow only one to have true value,
while checkboxes allow many.

jeff
------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
ChangeBoxes
End Sub

Sub ChangeBoxes()
A = Worksheets("Sheet1").Range("A1")
Select Case A
Case 1
Sheet2.OptionButton1.Value = True
Sheet2.CheckBox1.Value = True

Sheet2.CheckBox2.Value = False
Sheet2.CheckBox3.Value = False
Case 2
Sheet2.OptionButton2.Value = True
Sheet2.CheckBox2.Value = True

Sheet2.CheckBox1.Value = False
Sheet2.CheckBox3.Value = False
Case Else
Sheet2.OptionButton3.Value = True
Sheet2.CheckBox3.Value = True

Sheet2.CheckBox2.Value = False
Sheet2.CheckBox1.Value = False
End Select
End Sub

 

[John]

Hey Jeff,

How about if the sheet was on another workbook? I can
create the macro within a workbook, but I can't figure out
out to uncheck or check boxes in another workbook.

thanks again for help! I really appreciate it!

John