Save textbox value, VBA, Excel

Question

I'm writing a code for excel, that will have a form witch include a textbox and button. Button selects a folder directory and writes it in textbox. Part of the form is this!

SCREENSHOT

The code for it is this

Private Sub CommandButton1_Click()

Dim diaFolder As FileDialog
On Error GoTo ErrorHandler
Set diaFolder = Application.FileDialog(msoFileDialogFolderPicker)
diaFolder.AllowMultiSelect = False
diaFolder.Title = "Select a folder then hit OK"
diaFolder.Show
TextBox1.Text = diaFolder.SelectedItems(1)
Set diaFolder = Nothing

Exit Sub

ErrorHandler:
Msg = "No folder selected, you must select a folder for program to run"
Style = vbError
Title = "Need to Select Folder"
Response = MsgBox(Msg, Style, Title)

End Sub

The problem is how to save the "textbox" value to show it every time I call the userform; it should't depend on a cell in workbook as it will be an add-in and will be called from the ribbon button.

Any ideas will be appreciated very much ! Thanks.

[this](http://stackoverflow.com/questions/23917977/alternatives-to-public-variables-in-vba) might help. — Ioannis, Jul 13 '14 at 08:23
The addin itself has an associated workbook and sheets. You can store the value there. Would this work for you? — chris neilsen, Jul 13 '14 at 08:53
Addin has it's sheet (for example sheet1), but how to link a cell in that sheet without "disturbing" the actual active workbook? — user2728523, Jul 13 '14 at 09:04
Use `Thisworkbook.Worksheets("SheetName").Range("SomeRange")` to store and access a value. `Thisworkbook` refers to the workbook containing the VBA code executing, in this instance the addin. — chris neilsen, Jul 13 '14 at 09:06
OK. Thanks. I'll try it and will write comment about the result. — user2728523, Jul 13 '14 at 09:10
chris neilsen it works, but the problem is how to save it ? )))) — user2728523, Jul 13 '14 at 09:14
Ok I found it. `ThisWorkbook.Saved = True` command will do that. Thanks you all very much!!! — user2728523, Jul 13 '14 at 09:21
You can save text strings up to 255 characters in a workbook's `CustomDocumentProperties` collection, then you can read from that collection, too. — David Zemens, Jul 13 '14 at 19:00

Save textbox value, VBA, Excel

0 Answers0