Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing a cell from another cell's formula
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing a cell from another cell's formula
On Nov 16, 11:57*pm, magmike wrote:
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing a cell from another cell's formula
magmike wrote:
On Nov 16, 11:57*pm, magmike wrote: 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. 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".) -- This hurts my brain. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing a cell from another cell's formula
On Nov 17, 11:13*am, "Auric__" wrote:
magmikewrote: On Nov 16, wrote: 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. 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".) -- This hurts my brain.- Hide quoted text - - Show quoted text - 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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Changing a cell from another cell's formula
on 11/30/2011, magmike supposed :
On Nov 17, 11:13*am, "Auric__" wrote: magmikewrote: On Nov 16, wrote: 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. 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".) -- This hurts my brain.- Hide quoted text - - Show quoted text - 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! -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I test a cell's formula (ex: Is this cell and Avg or a sum? | Excel Worksheet Functions | |||
Dynamically changing cell's formula | Excel Worksheet Functions | |||
How do I enter a formula in a cell that changes per another cell's | Excel Worksheet Functions | |||
Append Selected Cell in another Cell's Formula | Excel Programming | |||
Returning a cell's formula in a different cell | Excel Worksheet Functions |