I have a userform that contains five arrrarys of choices, These are captured
in rows of cells. I have a a buttom that adds the cells up and one that
clears the cells for the next time. The text box (TextBox10) that show the
score is linked via the row source property. If I clear the cell with all the
values added I loose the formula in the cell.

Is there a way to have the textbox simply add a range (i.e Sheet6 a2:aw5)?
and then save to another cell that can be cleared?

Not sure if this is what you want

sub test()
set addrange = sheets("Sheet6").range("a2:aw5")
total = worksheetfunction.sum(addrange)
end sub




"gbpg" wrote:

I have a userform that contains five arrrarys of choices, These are captured
in rows of cells. I have a a buttom that adds the cells up and one that
clears the cells for the next time. The text box (TextBox10) that show the
score is linked via the row source property. If I clear the cell with all the
values added I loose the formula in the cell.

Is there a way to have the textbox simply add a range (i.e Sheet6 a2:aw5)?
and then save to another cell that can be cleared?

That looks like part of it. Thanks. Now how would I get this into TExtBox10.
In Access I could put a label on a form and simply put=([a field]+[another
filed]) and the sum would appear in the label - it would them be an option to
save to a field or it would disappear when the fields in a2:AW5 are cleared
using the clearContrents method

"Joel" wrote:

Not sure if this is what you want

sub test()
set addrange = sheets("Sheet6").range("a2:aw5")
total = worksheetfunction.sum(addrange)
end sub




"gbpg" wrote:

I have a userform that contains five arrrarys of choices, These are captured
in rows of cells. I have a a buttom that adds the cells up and one that
clears the cells for the next time. The text box (TextBox10) that show the
score is linked via the row source property. If I clear the cell with all the
values added I loose the formula in the cell.

Is there a way to have the textbox simply add a range (i.e Sheet6 a2:aw5)?
and then save to another cell that can be cleared?

You can't add a formula to a testbox. You can do something like this

ActiveSheet.Shapes("TextBox10").Characters.Text = Total

"gbpg" wrote:

That looks like part of it. Thanks. Now how would I get this into TExtBox10.
In Access I could put a label on a form and simply put=([a field]+[another
filed]) and the sum would appear in the label - it would them be an option to
save to a field or it would disappear when the fields in a2:AW5 are cleared
using the clearContrents method

"Joel" wrote:

Not sure if this is what you want

sub test()
set addrange = sheets("Sheet6").range("a2:aw5")
total = worksheetfunction.sum(addrange)
end sub




"gbpg" wrote:

I have a userform that contains five arrrarys of choices, These are captured
in rows of cells. I have a a buttom that adds the cells up and one that
clears the cells for the next time. The text box (TextBox10) that show the
score is linked via the row source property. If I clear the cell with all the
values added I loose the formula in the cell.

Is there a way to have the textbox simply add a range (i.e Sheet6 a2:aw5)?
and then save to another cell that can be cleared?

Joel I tried the belwo and I get a compile error can't find project or
library on the addrange?

"Joel" wrote:

You can't add a formula to a testbox. You can do something like this

ActiveSheet.Shapes("TextBox10").Characters.Text = Total

"gbpg" wrote:

That looks like part of it. Thanks. Now how would I get this into TExtBox10.
In Access I could put a label on a form and simply put=([a field]+[another
filed]) and the sum would appear in the label - it would them be an option to
save to a field or it would disappear when the fields in a2:AW5 are cleared
using the clearContrents method

"Joel" wrote:

Not sure if this is what you want

sub test()
set addrange = sheets("Sheet6").range("a2:aw5")
total = worksheetfunction.sum(addrange)
end sub




"gbpg" wrote:

I have a userform that contains five arrrarys of choices, These are captured
in rows of cells. I have a a buttom that adds the cells up and one that
clears the cells for the next time. The text box (TextBox10) that show the
score is linked via the row source property. If I clear the cell with all the
values added I loose the formula in the cell.

Is there a way to have the textbox simply add a range (i.e Sheet6 a2:aw5)?
and then save to another cell that can be cleared?

In the VBA menu go to Tools References. Either something is not checked or
needs to be checked.

I have the following check in excell 2003
Visual Basic for Applications
Microsoft Excel 11.0 object library
OLE Automation
Microsoft Office 11 objext Library


The problem may also be you don't have the addin
Go to spreadsheet menu Tools - Addins
Check the following
Analysis Tool Pak
Analysis toolpak VBA

"gbpg" wrote:

Joel I tried the belwo and I get a compile error can't find project or
library on the addrange?

"Joel" wrote:

You can't add a formula to a testbox. You can do something like this

ActiveSheet.Shapes("TextBox10").Characters.Text = Total

"gbpg" wrote:

That looks like part of it. Thanks. Now how would I get this into TExtBox10.
In Access I could put a label on a form and simply put=([a field]+[another
filed]) and the sum would appear in the label - it would them be an option to
save to a field or it would disappear when the fields in a2:AW5 are cleared
using the clearContrents method

"Joel" wrote:

Not sure if this is what you want

sub test()
set addrange = sheets("Sheet6").range("a2:aw5")
total = worksheetfunction.sum(addrange)
end sub




"gbpg" wrote:

I have a userform that contains five arrrarys of choices, These are captured
in rows of cells. I have a a buttom that adds the cells up and one that
clears the cells for the next time. The text box (TextBox10) that show the
score is linked via the row source property. If I clear the cell with all the
values added I loose the formula in the cell.

Is there a way to have the textbox simply add a range (i.e Sheet6 a2:aw5)?
and then save to another cell that can be cleared?