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?

Joel
I don't see
the Microsoft Excel 11.0 object library
OLE Automation
Microsoft Office 11 object Libary
in my computer I am using a different computer (lap top) are these updates?

"Joel" wrote:

> 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?

I would try two things
1) Make suure you have all the SP and updates from the microsoft webpage for
office.
2) Make sure you have all the addin install from the office installation disk.

You may have a different office library. Either 10.0 or 12.0. I don't
think any of the code I gave you wouldn't work on any of the 3 libraries.

"gbpg" wrote:

> Joel
> I don't see
> the Microsoft Excel 11.0 object library
> OLE Automation
> Microsoft Office 11 object Libary
> in my computer I am using a different computer (lap top) are these updates?
>
> "Joel" wrote:
>
> > 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?