Data Entry on a Form

[Chrissy]

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.

 

[John Wilson]

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]

John Wilson wrote

Chrissy,

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.

 

[Tom Ogilvy]

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]

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

 

[Tom Ogilvy]

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

Regards,
Tom Ogilvy