Changing a cell from another cell's formula

[magmike]

I am using Excel 2007. I am using four different radio buttons which
all link to cell L29, creating the values 0-4, depending on which is
checked. The default is that none of them are checked.

If I change the value of L29 to 0, then all of the radio buttons will
be unchecked, even if one of them was checked before I changed the
value.

I would like to be able to change the value of L29 to 0, when the
value of a different cell (L18) reads FALSE, so that the radio buttons
are reset. However, if I put the formula in L29, it is overwritten
anytime a radio button change is made. I do not want to use a reset
button - I need the value of L18 in effect to become the reset
trigger.

Any ideas?

Thanks in advance!

magmike

 

[magmike]

I am using Excel 2007. I am using four different radio buttons which
all link to cell L29, creating the values 0-4, depending on which is
checked. The default is that none of them are checked.

If I change the value of L29 to 0, then all of the radio buttons will
be unchecked, even if one of them was checked before I changed the
value.

I would like to be able to change the value of L29 to 0, when the
value of a different cell (L18) reads FALSE, so that the radio buttons
are reset. However, if I put the formula in L29, it is overwritten
anytime a radio button change is made. I do not want to use a reset
button - I need the value of L18 in effect to become the reset
trigger.

Any ideas?

Thanks in advance!

magmike

PS - I don't mind using VB, but if there is a way to do in a cell
formula somewhere, that would be preferable to having to save as a
Macro-Enabled excel document.

Thanks,
magmike

 

[Auric__]

PS - I don't mind using VB, but if there is a way to do in a cell
formula somewhere, that would be preferable to having to save as a
Macro-Enabled excel document.

I'm pretty sure that clearing the radio buttons cannot be done this way
*without* scipting it. (Also, are you sure it's radio (a.k.a. option)
buttons? I rather think checkboxes would be better if they need to be all
clear, or more than one turned on at the same time.)

Anyway, you can try something like this. Drop this into the sheet's class (in
the VBA editor):

Private Sub Worksheet_Change(ByVal Target As Range)
If "$L$18" = Target.Address Then
If Target.Value = False Then
Range("L29").Value = 0
button1.Value = False
button2.Value = False
button3.Value = False
button4.Value = False
End If
End If
End Sub

You'll need to change the names of the button placeholders, of course.

(Caveat: I'm using Excel 2000. Test before using "for real".)

 

[magmike]

magmikewrote:


I'm pretty sure that clearing the radio buttons cannot be done this way
*without* scipting it. (Also, are you sure it's radio (a.k.a. option)
buttons? I rather think checkboxes would be better if they need to be all
clear, or more than one turned on at the same time.)

Anyway, you can try something like this. Drop this into the sheet's class(in
the VBA editor):

    Private Sub Worksheet_Change(ByVal Target As Range)
        If "$L$18" = Target.Address Then
            If Target.Value = False Then
                Range("L29").Value = 0
                button1.Value = False
                button2.Value = False
                button3.Value = False
                button4.Value = False
            End If
        End If
    End Sub

You'll need to change the names of the button placeholders, of course.

(Caveat: I'm using Excel 2000. Test before using "for real".)

I'm not getting this to work. First thing I should mention, however,
is that I have switched from using radio/option buttons, and using
drop downs. Would this change the code?

 

[GS]

I'm not getting this to work. First thing I should mention, however,
is that I have switched from using radio/option buttons, and using
drop downs. Would this change the code?

Are these cell dropdowns (ie: a DataValidation list) or controls? If DV
dropdowns then where are they (cell address) and what is the criteria
if the chosen value is a specific value.

BTW, you state there WERE 4 option buttons ranging from 0 to 4 (total
of 5 options).-a bit confusing!