Keep a text in TextBox(on UserForm) after i close the Workbook?VBA

Andrzej1984

New Member
Joined
Jun 6, 2019
Messages
6
Hello Everyone,

What can i do to keep the text in TextBox after i close the workbook?

Im preparing a table where one column is describe as "See comment",
When i click on on each cell belong on this column: UserForm will be show with the Textbox on it,
That was easy to do,
But each time when i close the workbook: all data from textbox were lost :(
Maybe someone have any idea how to solve it?

Best regards,
Andrzej
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Re: How to keep a text in TextBox(on UserForm) after i close the Workbook?VBA

you can save the text on a sheet and have the box updated to view that, data isn't kept in forms
 
Upvote 0
Re: How to keep a text in TextBox(on UserForm) after i close the Workbook?VBA

you can save the text on a sheet and have the box updated to view that, data isn't kept in forms

Thank you mole999,
sounds interesing,
do you know how to do it in vba?
 
Upvote 0
Re: How to keep a text in TextBox(on UserForm) after i close the Workbook?VBA

i would have to spend a lot of time trying various things to make a hash of it
 
Upvote 0
Re: How to keep a text in TextBox(on UserForm) after i close the Workbook?VBA

Post your code which refers to the column/textbox.
Is it just one textbox, or a box for each cell in that column?

Effectively, you need to write the contents of the textbox to a string, and store it - probably to a spare cell in the workbook. Each time the Userform is opened, the contents of the cell need to be loaded back into the textbox.

By the way, what you're describing is easily achieved by Excel's built-in "Comments" feature - which has now (in Office 2016) been split into 2 different features - "Comments" (to be used/updated/syncronised by members of a group) and "Notes" which behave like the old "Comments" feature.
I assume you're aware of these, so I don't understand why they're not what you need; I have a feeling you're going to say that there's more to your question than that......
 
Upvote 0
Re: How to keep a text in TextBox(on UserForm) after i close the Workbook?VBA

Effectively, you need to write the contents of the textbox to a string, and store it - probably to a spare cell in the workbook. Each time the Userform is opened, the contents of the cell need to be loaded back into the textbox.- ok but how to do it?

Here is my "code":

Private Sub CommandButton2_Click()
Image1.Picture = LoadPicture(Application.GetOpenFilename())'inserting image to userform


End Sub


Private Sub CommandButton1_Click()
TextBox1.Text = Format(TextBox1.Text, ">")'this working only until workbook is open , after close textbox is empty




ActiveWorkbook.Save


UserForm1.Hide


End Sub








Private Sub TextBox1_Change()
TextBox1.Text = Format(TextBox1.Text, ">")


End Sub

"By the way, what you're describing is easily achieved by Excel's built-in "Comments" feature - which has now (in Office 2016) been split into 2 different features - "Comments" (to be used/updated/syncronised by members of a group) and "Notes" which behave like the old "Comments" feature.
I assume you're aware of these, so I don't understand why they're not what you need; I have a feeling you're going to say that there's more to your question than that......" - did not know it :( need to check it,
Thanks!
 
Upvote 0
Re: How to keep a text in TextBox(on UserForm) after i close the Workbook?VBA

I'm not sure where all your controls are - I know your textbox is on your userform, but, not sure where your buttons are - it would help if you fully describe the situation, in your initial post. However ....

If you put this in the Userform's "Initialize" event, it'll fire whenever the userform's initialized:
Code:
 Private Sub UserForm_Initialize()
    Me.TextBox1.Value = Sheets("Sheet1").Range("F20").Value
End Sub
Change the sheet name & cell reference to those of your choice.

Put this in the Textbox's change event:
Code:
Private Sub TextBox1_Change()
Sheets("Sheet1").Range("F20").Value = Me.TextBox1.Value
ActiveWorkbook.Save
End Sub
Once again, substitute the sheetname & range, to the ones you've used above.

Now, when you open the userform, the textbox will be loaded with the value in the worksheet cell, and when you change the textbox contents, it'll automatically update the contents of the cell, and save the workbook.
 
Upvote 0
Re: How to keep a text in TextBox(on UserForm) after i close the Workbook?VBA

Did this work for you?
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top