Userform retain data after closing and reopening

[Joel Mills]

I have a tabbed user form that populates grouped text boxes. I have this
working properly.

The part I can't get to work is, I want the user to input text and have the
text remain in the userform "textbox". It works when I use "Hide" instead
of "Unload", but once I save and close the work book and reopen the userform
the textboxes have been cleared. Can someone tell me how to retain the text
until the user inputs something different?

Joel

[bhofsetz]

Joel,
The only way I know of to retain values in userform fields when the workbook is closed and then reopened is to have the textbox.value assigned to a worksheet cell and then when the userform is reloaded you have to use the initialize event procedure to read the values from the worksheet back into each textbox. You can use a hidded worksheet so the user won't have access to the sheet where you are temporarily storing the textbox values
If you only want the text boxes to be able to reain their values while the macro is running then you can assign the textbox values to public variables before unloading the userform. Then when the userform is reloaded use the initialize event procedure to fill the textboxes from their respective variables.

Hope this Helps

[Ken Macksey]

Hi

You would have to save the last entered data in an excel database or just
some cells on a worksheet or some hidden labels on the userform when you
close the userform and then read it back into the textboxes when the form is
inialized.

HTH

Ken

[Joel Mills]

Thanks for your reply. I have the following code on one of the textboxes.
Since reading your reply I have created a sheet called "Hidden" that is
filled by user input, it also inputs the text into the textboxes. I don't
know how to have the Userform retrieve the cell value in C1 upon initiation
of the form.

Sub TextBox1_Change()
Sheets("Curve").Activate
Worksheets("Curve").Shapes("Curve Line No. 1").Select
Selection.Characters.Text = TitlesForm.TextBox1.Text
Sheets("Hidden").Range("C1").Value = TitlesForm.TextBox1.Text
End Sub


"bhofsetz" <[email protected]> wrote in
message news:[email protected]...
>
> Joel,
> The only way I know of to retain values in userform fields when
> the workbook is closed and then reopened is to have the textbox.value
> assigned to a worksheet cell and then when the userform is reloaded you
> have to use the initialize event procedure to read the values from the
> worksheet back into each textbox. You can use a hidded worksheet so
> the user won't have access to the sheet where you are temporarily
> storing the textbox values
> If you only want the text boxes to be able to reain their values while
> the macro is running then you can assign the textbox values to public
> variables before unloading the userform. Then when the userform is
> reloaded use the initialize event procedure to fill the textboxes from
> their respective variables.
>
> Hope this Helps
>
>
> --
> bhofsetz
> ------------------------------------------------------------------------
> bhofsetz's Profile:
> http://www.excelforum.com/member.php...o&userid=18807
> View this thread: http://www.excelforum.com/showthread...hreadid=380602
>

[Joel Mills]

I got it to work. I added the following to Workbook open event. 1st
Ungroup, because the textboxes were grouped. This allowed changes to be
made by the userform. Next I stored the inputs into a hidden worksheet.
This was called by the TitleForm_Initialize macro. And finally I regrouped
the textboxes, so that when the user called up the Titles form it would run
through the coding I had already implemented.

Here's the code on the textbox at the UserForm level:

Sub TextBox1_Change()
Sheets("Curve").Activate
Worksheets("Curve").Shapes("Curve Line No. 1").Select
Selection.Characters.Text = TitlesForm.TextBox1.Text
Sheets("Hidden").Range("C1").Value = TitlesForm.TextBox1.Text
End Sub

And here's the code at the Workbook level.

Private Sub Workbook_Open()
Ungroup
TitlesForm_Initialize
Regroup
End Sub

bhofsetz and Ken, thanks for your response. Because of it I was able to
figure out a solution.









"bhofsetz" <[email protected]> wrote in
message news:[email protected]...
>
> Joel,
> The only way I know of to retain values in userform fields when
> the workbook is closed and then reopened is to have the textbox.value
> assigned to a worksheet cell and then when the userform is reloaded you
> have to use the initialize event procedure to read the values from the
> worksheet back into each textbox. You can use a hidded worksheet so
> the user won't have access to the sheet where you are temporarily
> storing the textbox values
> If you only want the text boxes to be able to reain their values while
> the macro is running then you can assign the textbox values to public
> variables before unloading the userform. Then when the userform is
> reloaded use the initialize event procedure to fill the textboxes from
> their respective variables.
>
> Hope this Helps
>
>
> --
> bhofsetz
> ------------------------------------------------------------------------
> bhofsetz's Profile:
> http://www.excelforum.com/member.php...o&userid=18807
> View this thread: http://www.excelforum.com/showthread...hreadid=380602
>