In Hiding data in Excel Objects I introduced the concept of hiding data in shapes by encoding it into jSon, then hiding the serialized result in a property of a hidden shape. One of the common uses of this to enable data persistence between calls to a userForm.
How to use
I have introduced a new class, cPersistence, which is used to store and load what was in a form from one session to the next. Typically you would populate the form on activation , and store the current values on deactivation like this. This example is in the cDataSet.xlsm.
Private Sub UserForm_Activate() ' this will fill in any default values from the last time this form was used Dim cp As New cPersistence cp.getForm Me End Sub Private Sub UserForm_Deactivate() ' this will store default values for the next time Dim cp As New cPersistence cp.saveForm Me End Sub Private Sub UserForm_Terminate() UserForm_Deactivate End Sub
and that’s all there is to it.
The details
Lets take a simple form like this
We’d like to use as default whatever was previously entered the last time the form was used. So after filling in this data, and closing the form, the next time the form comes up we want to see those previous values as default like this
How does this work
The cPersistence class will take a form, serialize the data on each field in the form, store it in an invisible shape in the workbook somewhere on exit, and do the reverse when you open the form. Here is what gets stored when closing the above form
{ "ramblings_mcpher_com_persistent_shape":{ "Label1":"Label1", "TextBox1":"first time", "TextBox2":"i filled this and checked that", "CheckBox1":"True", "CommandButton1":"False" } }
How to clear
You just need to delete the container shape
Option Explicit Private Sub deleteShape() Dim c As New cPersistence c.deleteShape End Sub
The code
Here is the code for the cPersistence class
' saves persistent data hidden in a workbook Public Function shapeItem() As shape ' this retrieves the shape thats being used as a storage area or creates it Set shapeItem = findOrCreate() End Function Public Function deleteShape() Dim s As shape ' get rid of the whole thing Set s = findShape If Not s Is Nothing Then s.Delete End If End Function Public Property Get shapeName() As String ' the name to use for the persistent shape shapeName = "ramblings_mcpher_com_persistent_shape" End Property Private Function findOrCreate() ' either find an existing shape or make a new one Dim s As shape Set s = findShape() If s Is Nothing Then Set s = createShape() End If Set findOrCreate = s End Function Private Function createShape() As shape ' create an empty shape Dim s As shape With ActiveSheet Set s = .Shapes.AddShape(msoShapeRectangle, 1, 1, 1, 1) s.Visible = False s.name = shapeName() End With Set createShape = s End Function Public Function findShape() As shape ' could be anywhere in the workbook Dim ws As Object, s As shape For Each ws In Sheets With ws For Each s In .Shapes If s.name = shapeName Then Set findShape = s Exit Function End If Next s End With Next ws Set findShape = Nothing End Function Public Function getForm(f As UserForm) As cJobject ' get the persistent form data, and populate the form with it Dim cj As cJobject, o As Control, jo As cJobject Set cj = getJob If Not cj Is Nothing Then For Each o In f.Controls Set jo = cj.child(o.name) If (Not jo Is Nothing) Then setControlValue o, jo.Value End If Next o End If End Function Public Function saveForm(f As UserForm) As cJobject 'save the persistent form data to the hidden shape shapeItem.AlternativeText = makeJob(f).serialize() End Function Public Function getJob() As cJobject ' get the persistent form data from the hidden shape Dim cj As cJobject, s As shape Set s = shapeItem() If (s.AlternativeText <> vbNullString) Then Set cj = New cJobject Set cj = cj.init(Nothing).deSerialize(s.AlternativeText).Children(1) End If Set getJob = cj End Function Public Function makeJob(f As UserForm) As cJobject ' save all the current values of every control in a form Dim o As Control, cj As cJobject Set cj = New cJobject cj.init Nothing, shapeName() For Each o In f.Controls cj.add o.name, getControlValue(o) Next o Set makeJob = cj End Function
Take a look at Get Started Snippets for more tips like this. In the meantime why not join our forum,follow the blog or follow me on twitter to ensure you get updates when they are available.