text boxes & control source

[Robert Dieckmann]

I have an Excel 2000 spreadsheet with a series of (5) input forms. On each
form I have previous and next buttons to navigate back and forth. In each
case I use a code such as:



frmInput01.Hide

frmInput02.Show



On my final form I have a finished button with this code:



Unload frmInput01

Unload frmInput02

Unload frmInput03

Unload frmInput04

Unload frmInput05



I also have a cancel button on each form that unloads all forms.



Within each form I use option buttons (with coding) or text boxes (using the
control source property) to input data into the appropriate cell in the
spreadsheet. Here is my dilemma. I've been using this spreadsheet for some
time now with no significant problems. I recently had to do some
modifications and now not all data from the text boxes is retained when I
finish the input. For example, if I input data into the text box txtA it
will show up on the spreadsheet as long as I have the form open. I can
navigate back & forth between forms with no problems. However, as soon as I
hit the finish button (or cancel) it changes the value in the spreadsheet
cells to (0) zero. What I can't figure out is why it only changes certain
text boxes. Here's the list



Form 1 - 8 text boxes - none are changed

Form 2 - 6 text boxes - 5 go back to zero

Form 3 - 2 text boxes - both go back to zero

Form 4 - 4 text boxes - none are changed



All text boxes have unique names. When I modified the program I made no
changes to forms 1 & 2. Form 3 had a minor change to a label and I made
significant changes to form 4. I tried changing the finish button to hide
all forms (rather than unload) and it still changes the above cells to zero.
I am totally baffled. Is something corrupt in my file? Is there something
I am missing?

 

[Brian]

I'm guessing that some of the problems are related to the load-unload
actions on the forms and what-ever initializations are occuring

But basically, your method seems pretty cumbersome...

Here's a thought...

Why don't you use a single "Master" form with (x) textboxes. You could
update the single form for each Next or Back click by hiding or unhiding the
correct textbox based on an index, and updating standardized labels and
other form components. This would give you a single form to work with and
all data would be stored on one form. All inits would occur at once and you
are making great use of the "re-use" capability of the tools.

Additionally, you wouldn't have to mess with unloading forms that were never
loaded... everything would be tied to the original "master" form. And any
specific code actions could easily be controlled via Select Case statements
by testing the current "index".

Let me know if you think this might help and I could provide an example
workbook.

Brian