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
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? If not, what would be a good alternative?
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.
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
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
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? If not, what would be a good alternative?
EDIT 1:
EXAMPLE EDI 850 DOCUMENT
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.
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*>
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.
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
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
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? If not, what would be a good alternative?
EDIT 1:
EXAMPLE EDI 850 DOCUMENT
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.
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
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
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? If not, what would be a good alternative?
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
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
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? If not, what would be a good alternative?
EDIT 1:
EXAMPLE EDI 850 DOCUMENT
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.
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
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
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? If not, what would be a good alternative?