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
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
9,964
Office Version
2019, 2016, 2013
Platform
Windows
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
 

Andrzej1984

New Member
Joined
Jun 6, 2019
Messages
6
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?
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
9,964
Office Version
2019, 2016, 2013
Platform
Windows
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
 

Andrzej1984

New Member
Joined
Jun 6, 2019
Messages
6
Re: How to keep a text in TextBox(on UserForm) after i close the Workbook?VBA

Ok, thanks for advise ;)
 

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,562
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......
 

Andrzej1984

New Member
Joined
Jun 6, 2019
Messages
6
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!
 

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,562
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.
 

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,562
Re: How to keep a text in TextBox(on UserForm) after i close the Workbook?VBA

Did this work for you?
 

Forum statistics

Threads
1,086,238
Messages
5,388,641
Members
402,128
Latest member
ghostthing

Some videos you may like

This Week's Hot Topics

Top