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

[Andrzej1984]

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]

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]

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]

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]

Re: How to keep a text in TextBox(on UserForm) after i close the Workbook?VBA

Ok, thanks for advise

 

[sykes]

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]

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]

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:

 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:

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]

Re: How to keep a text in TextBox(on UserForm) after i close the Workbook?VBA

Did this work for you?