Can you put a formula on a field that requires user input?
 

Here is the scenario that I need help on:
Cell_1 is a cell that doesn't contain any formulas or conditions.
Checkbox_A is a checkbox from the form tool.

If Checkbox_A is 'FALSE'(not checked), I want Cell_1 to change/default to
the number 0. If Checkbox_A is 'TRUE', I want the user to still be able to
enter a number into Cell_1, let's say 10,000. Normally, I would just put an
'if statement' on Cell_1 but since I also am expecting the user to input data
in this cell, it will overwrite the formula. Am I overlooking something
simple here? Is there VB code that would take care of this?

Can you put a formula on a field that requires user input?
 

Hi
In a code module copy/paste the following sub which sets the cell A1 to 0
when the user will check the checkbox:
'------------------------------------------------
Sub SetCell1()
Dim cell As Range
Set cell = ActiveSheet.Range("A1")
If ActiveSheet.CheckBoxes(1).Value = 1 Then ' when Checked
'(could use checboxes("name") too)
cell.Value = 0
End If
End Sub
'-----------------------------------------------
Now, right-click on the checkbox in the sheet, and from the pop-up menu,
choose Assign Macro. In the Assign Macro Dialog, select the SetCell1 sub.
Now the SetCell1 is rtun everytime the checkbox is clicked.
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Nan" wrote:

Here is the scenario that I need help on:
Cell_1 is a cell that doesn't contain any formulas or conditions.
Checkbox_A is a checkbox from the form tool.

If Checkbox_A is 'FALSE'(not checked), I want Cell_1 to change/default to
the number 0. If Checkbox_A is 'TRUE', I want the user to still be able to
enter a number into Cell_1, let's say 10,000. Normally, I would just put an
'if statement' on Cell_1 but since I also am expecting the user to input data
in this cell, it will overwrite the formula. Am I overlooking something
simple here? Is there VB code that would take care of this?

Can you put a formula on a field that requires user input?
 

You can assign a macro to the checkbox to perform those actions

Right click on the checkbox and select assign macro, then select your macro
from the dialog - a macro such as:

Sub Checkbox_A_Click()
Dim sname as String
Dim rng as Range
Dim cbox as CheckBox
sname = application.Caller
set cbox = Activesheet.CheckBoxes(sName)
set rng = Range("Cell_1")
if cbox.Value = xlOn then
if isempty(rng) or rng.value = 0 then
rng.clearcontents
else
rng.value = 0
end if
End if
End Sub

--
Regards,
Tom Ogilvy


"Nan" wrote in message
...
Here is the scenario that I need help on:
Cell_1 is a cell that doesn't contain any formulas or conditions.
Checkbox_A is a checkbox from the form tool.

If Checkbox_A is 'FALSE'(not checked), I want Cell_1 to change/default to
the number 0. If Checkbox_A is 'TRUE', I want the user to still be able
to
enter a number into Cell_1, let's say 10,000. Normally, I would just put
an
'if statement' on Cell_1 but since I also am expecting the user to input
data
in this cell, it will overwrite the formula. Am I overlooking something
simple here? Is there VB code that would take care of this?