saving textbox captions

[birdman]

i created a very complex program that runs a lot of different macros. in the initial userform there is an options commandbutton that leads to an new userform. in this options userform there are many different textboxes with default information filled in. if the user wishes to change the default information, he/she can, but when excel is closed, and then reopened, the textboxes return to the default. i already have a commandbutton that returns all the textboxes to default. how can i add a commandbutton that saves the users input into the textboxes each time excel is opened and that macro is run? i was thinking maybe i can save the excel file with this commandbutton, but would this save the textbox inputs of the user?? thanks for any help in advance.

 

[Fat Cat]

Where are you getting the default information from ?

If you pick it up from a worksheet, you can simply save the new information over the top of the old default values.

Alternatively, if you don't want the default values stored in your workbook (even on a hidden sheet), you could store the default information in an external text file and simply read it in whenever you need it. This will also allow you to save any changes the user may make back out to the text file.

You could even have 2 default files, 1 called say "system.txt" that has your original default values and another called "user.txt" that is created to save the values if any changes were made. Then when you come to read in the default information the next time the sheet is opened, you simply check to see if there is a "user.txt" file, if there is get the values from there otherwise get them from the "system.txt" file

make any sense.

 

[birdman]

i understand what your saying about the two text files. seems as though it would work. i wanted to avoid having files as attachments but i may have to resort to that.

the default information is typed in as the caption of the textbox in VBA editor. it can be changed when the program is running, but once excel is closed and reopened and the program is run again, the textboxes are set back to what i originally have as the caption in the VBA editor. the save settings commandbutton needs to overwrite this caption in the VBA editor so that when that particular user runs this program, his/her personal inputs into the textboxes are saved, rather than have the default inputs appear everytime the user runs the program. its a problem because if the user wants each textbox to have a certain input, each time the program is run, he/she has to change the textbox input for the 30 or so textboxes i have. the save settings commandbutton can save what they want to input, and the restore defaults commandbutton can restore my original defaults. this may be confusing so i hope you understand.

maybe after clicking the save settings commandbutton i can set variables equal to the information in the textboxes, and then set those variables equal to the caption of the textboxes. then save and overwrite the excel document. not sure if that'll work or not but we'll see when i am back at work tomorrow. thanks for all your help.

 

[Fat Cat]

If you don't want an external file, sounds like you should think about a hidden sheet that stores the information you want. You can read and write to the cells on the hidden sheet from your VBA macros and UserForm.

eg; for a Hidden sheet called "myDedfaults"

Private Sub TextBox1_Change()
Sheets("myDefaults").Cells(1, 1).Value = TextBox1.Value
End Sub
Private Sub TextBox2_Change()
Sheets("myDefaults").Cells(2, 1).Value = TextBox2.Value
End Sub

Private Sub UserForm_Activate()

TextBox1.Text = Sheets("myDefaults").Cells(1, 1).Value
TextBox1.ControlTipText = Sheets("myDefaults").Cells(1, 2).Value

TextBox2.Text = Sheets("myDefaults").Cells(2, 1).Value
TextBox1.ControlTipText = Sheets("myDefaults").Cells(2, 2).Value

End Sub

 

[birdman]

awesome idea!!! i havent tried it yet but it seems it will work entirely. thanks

 

[Fat Cat]

If you really want to hide the default data, you could even read it into an array in memory on startup and delete it from the worksheet. Then on Closing the workbook you can write it back to the hidden sheet. That way there won't be anything on the sheet while the workbook is open even the users manage to find a way of unhiding the sheet.