Storing and Retrieving data with User Forms in Excel 2010

Question

I'm currently designing a check list for my office using Excel that requires the user to save anywhere from 4-10 documents that need to be saved in the same folder as this check list. I thought it might be easier to consolidate everything into one Excel file by using User Forms. I noticed that it's not safe to use a User Form to store data, but instead using it as a means to display or manipulate data or cells from within the spreadsheet.

Unfortunately, I cannot store all the raw data from these files into a single cell as they can be quite large files. I thought maybe it'd be best to have an ActiveX Textbox to store this data into instead, but I'm wondering if I this might cause issues later down the road or if some one else's computer isn't compatible.

Visually what I've done...

This is the secondary spreadsheet (will be hidden) that stores the data in ActiveX Text Boxes

enter image description here

User form below that is displaying that data, stored from the secondary spreadsheet in the ActiveX Text Boxes

enter image description here

TL;DR

Everything is working as intended, but my primary question here is... Is this the best and safest (safest as in, lowest chance of losing data... I am not worried about security as everything is stored on a server) way to have all this data being stored in a single document by using embedded TextBoxes? If not, what would be a good alternative?

Code run when the User Form is closed

Private Sub btnSaveAndClose_Click()
 Sheets("Data").TextBox1.Value = PO850Data.Value
 Sheets("Data").TextBox2.Value = Invoice810Data.Value
 frmEDIData.Hide
End Sub

Code run when the User Form is opened

Private Sub UserForm_Initialize()
 btnSaveAndClose.SetFocus
 PO850Data.Value = Sheets("Data").TextBox1.Value
 Invoice810Data.Value = Sheets("Data").TextBox2.Value
End Sub

EDIT 1:

EXAMPLE EDI 850 DOCUMENT - (X12 Format)

ISA*01*0000000000*01*0000000000*ZZ*ABCDEFGHIJKLMNO*ZZ*123456789012345*101127*1719*U*00400*000003438*0*P*>
GS*PO*4405197800*999999999*20101127*1719*1421*X*004010VICS
ST*850*000000010
BEG*00*SA*08292233294**20101127*610385385
REF*DP*038
REF*PS*R
ITD*14*3*2**45**46
DTM*002*20101214
PKG*F*68***PALLETIZE SHIPMENT
PKG*F*66***REGULAR
TD5*A*92*P3**SEE XYZ RETAIL ROUTING GUIDE
N1*ST*XYZ RETAIL*9*0003947268292
N3*31875 SOLON RD
N4*SOLON*OH*44139
PO1*1*120*EA*9.25*TE*CB*065322-117*PR*RO*VN*AB3542
PID*F****SMALL WIDGET
PO4*4*4*EA*PLT94**3*LR*15*CT
PO1*2*220*EA*13.79*TE*CB*066850-116*PR*RO*VN*RD5322
PID*F****MEDIUM WIDGET
PO4*2*2*EA
PO1*3*126*EA*10.99*TE*CB*060733-110*PR*RO*VN*XY5266
PID*F****LARGE WIDGET
PO4*6*1*EA*PLT94**3*LR*12*CT
PO1*4*76*EA*4.35*TE*CB*065308-116*PR*RO*VN*VX2332
PID*F****NANO WIDGET
PO4*4*4*EA*PLT94**6*LR*19*CT
PO1*5*72*EA*7.5*TE*CB*065374-118*PR*RO*VN*RV0524
PID*F****BLUE WIDGET
PO4*4*4*EA
PO1*6*696*EA*9.55*TE*CB*067504-118*PR*RO*VN*DX1875
PID*F****ORANGE WIDGET
PO4*6*6*EA*PLT94**3*LR*10*CT
CTT*6
AMT*1*13045.94
SE*33*000000010
GE*1*1421
IEA*1*000003438

These can be much larger documents, I'd say we deal with 10x this size on a regular basis. Each TextBox will store one of these document's data.

• user109261: Your data is probably safe but this doesn't have a very checklist look or feel. It's really hard to give advice without a good description of the data and what you are actually trying to accomplish. Are you trying to store a document in each textbox? What do you consider quite large? Is 1kb quite large? 1MB??
• Maldred: Sorry, I should have been more specific... Raw EDI 850 Purchase Orders are text files that similarly to a .csv file. Where as a * or ~ would be similar to a ,. That being said... I want the user to copy this data from a website that displays it, and pasted into the TextBox from the User Form. These files may contain anywhere from 1000 to 50,000 characters or more long and it's hard to say what the file size may be as they can be stored in a variety of file formats (X12, CSV, XML). More info here on EDI 850s
• user109261: Are the Textboxes meant to replace the files stored in the directory?
• Maldred: Not replace, but used as a quick reference; as the original files are being stored by a separate service that we pull from
• user109261: There is no compatibility issues using the Active X TextBox. There is little chance of losing data the way that you are using it. I personally would not do things this way because it aesthetically ugly. Writing the data a worksheet gives you so many more options to work with the data.

Answer 1

See this related question: https://stackoverflow.com/questions/7589579/is-it-possible-to-store-elements-of-array-permanently-in-vba-in-excel

Summary: To store data persistently in the workbook using VBA, you have a few options:

• Store it in another worksheet. If you don't want the user to be able to see the worksheet, Sheets("ArrayValuesWorksheet").Visible = xlVeryHidden
• Create a defined name which has the desired data as its value. Names("MyArrayData") = myArray
• Store it in ThisWorkbook.CustomDocumentProperties -- see https://stackoverflow.com/questions/17406585/vba-set-custom-document-property
• Store it in a CommandBar, hopefully the user won't find it
• Use a helper XLL which can call Excel4(xlDefineBinaryName,...) and Excel4(xlGetBinaryName)

I believe storing in a hidden sheet is the most common.

• Maldred: I've taken into consideration about storing it in a hidden sheet, however I am unable to store it in a single cell as the data can be quite large and I'd rather not take that approach. Is it still safe to do so with what I have currently?
• Snowbody: It's definitely safe to store it in multiple cells in a hidden sheet. I am not aware of anyone recommending using ActiveX objects to store things; in previous versions of Excel this was crash-prone. Perhaps it's better now.
• Maldred: Thank you for your response! Very excellent resources you posted here :)