I have a form I am using for data entry. There are many rows
and many columns and I want to save the data in a spreadsheet.
Right now I am saving the values when the user presses a
button.

I can save the values more easily when the user enters them by
using the ControlSource property. What I am getting stuck on is
how can I have totals for rows and columns on the form? If I
link the control, through the ControlSource property to a
worksheet cell which contains a formula then I loose the formula.

Is there a way to do this with just properties of the controls without
having to write code to transfer the values? If there is not then what
is the simplest way to have a form display updated totals when a
user changes one of about 50 dependant values?

Chrissy.

Chrissy,

The other way would be to stop events running - then I could stop the
standard events running - is there a way to do this?
I asked this onece before myself.
The answer is no.
About the only way around it is to set a flag and test for it in the first
line of code under the event you want to bypass.

John

Chrissy wrote:

Tom Ogilvy wrote
There is no automatic connection between cells and controls other than the
controlsource. So code is really the only option for harvesting values from
cells with formulas.

I think you would have to key off the calculate event in the worksheet -
however, if the only thing that would make the sums change is user entries
in your userform, then perhaps you could key off that.

I would be nice if there was a control which had a write control source property
and a read control source property. That way you could have three controls
where one was the total of the other two and the values are automatically
displayed and saved just because the user entered a value in a cell.

The other way would be to stop events running - then I could stop the
standard events running - is there a way to do this?

When talking about user, it is generally more helpful/clearer to say
userform rather than just form as form could refer to simulation of a paper
form on a worksheet or you could be talking about a form in VB.

Ok - will do from now on.

Chrissy.

John Wilson wrote
Chrissy,

The other way would be to stop events running - then I could stop the
standard events running - is there a way to do this?
I asked this onece before myself.
The answer is no.
About the only way around it is to set a flag and test for it in the first
line of code under the event you want to bypass.

I have set up a test userform and can do it. The prob is that the userform I
want it on has 9 columns of 16 rows of data and some header totals for
some columns. I was trying to find a way that required fewer events
happening. Someone who is not a programmer will be taking on this
system once I write it and I don't what to scare them to much.

Chrissy.

For excel objects, you can use
Application.EnableEvents = False

Application.EnableEvents = True

this would include worksheet, workbook and application level events.

MSforms controls are not Excel objects, so they are not affected by this
setting, nor do they have an equivalent setting. You can, of course
establish a test at the start of the event to look at a flag variable to
signify whether to execute the event code or bail out.

It was unclear to me which events you wanted to halt.

Regards,
Tom Ogilvy

Chrissy wrote in message
...
Tom Ogilvy wrote
There is no automatic connection between cells and controls other than
the
controlsource. So code is really the only option for harvesting values
from
cells with formulas.

I think you would have to key off the calculate event in the worksheet -
however, if the only thing that would make the sums change is user
entries
in your userform, then perhaps you could key off that.

I would be nice if there was a control which had a write control source
property
and a read control source property. That way you could have three
controls
where one was the total of the other two and the values are automatically
displayed and saved just because the user entered a value in a cell.

The other way would be to stop events running - then I could stop the
standard events running - is there a way to do this?


When talking about user, it is generally more helpful/clearer to say
userform rather than just form as form could refer to simulation of a
paper
form on a worksheet or you could be talking about a form in VB.

Ok - will do from now on.

Chrissy.

I want to halt the event which writes the value in a
text box to the range specified by the ControlSource
property of that object. I want the object to read the
value from the cell but not to update it.

Chrissy.




Tom Ogilvy wrote
For excel objects, you can use
Application.EnableEvents = False

Application.EnableEvents = True

this would include worksheet, workbook and application level events.

MSforms controls are not Excel objects, so they are not affected by this
setting, nor do they have an equivalent setting. You can, of course
establish a test at the start of the event to look at a flag variable to
signify whether to execute the event code or bail out.

It was unclear to me which events you wanted to halt.

Regards,
Tom Ogilvy

Chrissy wrote in message
...
Tom Ogilvy wrote
There is no automatic connection between cells and controls other than
the
controlsource. So code is really the only option for harvesting values
from
cells with formulas.

I think you would have to key off the calculate event in the worksheet -
however, if the only thing that would make the sums change is user
entries
in your userform, then perhaps you could key off that.

I would be nice if there was a control which had a write control source
property
and a read control source property. That way you could have three
controls
where one was the total of the other two and the values are automatically
displayed and saved just because the user entered a value in a cell.

The other way would be to stop events running - then I could stop the
standard events running - is there a way to do this?


When talking about user, it is generally more helpful/clearer to say
userform rather than just form as form could refer to simulation of a
paper
form on a worksheet or you could be talking about a form in VB.

Ok - will do from now on.

Chrissy.

That is internal to the control - not an event - not controllable.
Transmission is two way.

Regards,
Tom Ogilvy


Chrissy wrote in message
...
I want to halt the event which writes the value in a
text box to the range specified by the ControlSource
property of that object. I want the object to read the
value from the cell but not to update it.

Chrissy.




Tom Ogilvy wrote
For excel objects, you can use
Application.EnableEvents = False

Application.EnableEvents = True

this would include worksheet, workbook and application level events.

MSforms controls are not Excel objects, so they are not affected by this
setting, nor do they have an equivalent setting. You can, of course
establish a test at the start of the event to look at a flag variable to
signify whether to execute the event code or bail out.

It was unclear to me which events you wanted to halt.

Regards,
Tom Ogilvy

Chrissy wrote in message
...
Tom Ogilvy wrote
There is no automatic connection between cells and controls other
than
the
controlsource. So code is really the only option for harvesting
values
from
cells with formulas.

I think you would have to key off the calculate event in the
worksheet -
however, if the only thing that would make the sums change is user
entries
in your userform, then perhaps you could key off that.

I would be nice if there was a control which had a write control
source
property
and a read control source property. That way you could have three
controls
where one was the total of the other two and the values are
automatically
displayed and saved just because the user entered a value in a cell.

The other way would be to stop events running - then I could stop the
standard events running - is there a way to do this?


When talking about user, it is generally more helpful/clearer to say
userform rather than just form as form could refer to simulation of
a
paper
form on a worksheet or you could be talking about a form in VB.

Ok - will do from now on.

Chrissy.