Storing and Retrieving data with User Forms in Excel 2010 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



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



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*&gt;
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.
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.