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

 

[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

 

[bhofsetz]

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

Hope this Help

 

[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

 

[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.