Programmatically restore/save custom values with VBA code

With the Save defined cell values only mode on, XLS Padlock lets you save and restore custom values in addition to pre-defined cell values.

To do this, XLS Padlock relies on two VBA events as well as two VBA API functions allowing you to read and write values when creating or reading customer's save files.

The two VBA events must be placed in a module of your Excel workbook:

Sub XLSPadlock_RestoreCustomValues()
 MsgBox ("Restoring values...")
End Sub
 
Sub XLSPadlock_SaveCustomValues()
 MsgBox ("Storing values")
End Sub

 To write custom values, use the VBA API function WriteCustomCellValue.

Dim XLSPadlock1 As Object
On Error Resume Next
Set XLSPadlock1 = Application.COMAddIns("GXLS.GXLSPLock").Object
XLSPadlock1.WriteCustomCellValue("Unique ID", "Value you want to store")

To read custom values, use the VBA API function ReadCustomCellValue.

Dim XLSPadlock1 As Object
On Error Resume Next
Set XLSPadlock1 = Application.COMAddIns("GXLS.GXLSPLock").Object
Dim Val As String
Val = XLSPadlock1.ReadCustomCellValue("Unique ID", "Default value if no entry")

If you want a dictionnary, it is also possible if you pass an empty unique ID to ReadCustomCellValue.

⚠ Be sure to add “Microsoft Scripting Runtime” in that case (using Tools->References from the VBE menu).

Here is the code to do so:

Sub XLSPadlock_RestoreCustomValues()

Dim XLSPadlock1 As Object
On Error Resume Next
Set XLSPadlock1 = Application.COMAddIns("GXLS.GXLSPLock").Object

Dim Dict As Object 'Scripting.Dictionary

Set Dict = XLSPadlock1.ReadCustomCellValue("", "")
 
For Each Key In Dict.Keys
        MsgBox "Key: " & Key & ", Value: " & Dict(Key)
Next Key

End Sub 

Full example with comments

This sub restores the entire column A from a string with different values separated by the comma character.

Sub RestoreFromValue(value As String)
    Dim r As Range, i As Long, ar
    Set r = Worksheets(2).Range("A:A")
    r.ClearContents
    
    ar = Split(value, ",")
    For i = 1 To UBound(ar) + 1
     r.Cells(i).value = ar(i - 1)
    Next
End Sub

The following event will be invoked by the compiled workbook when customers load a save file:

Sub XLSPadlock_RestoreCustomValues()
 
Dim XLSPadlock1 As Object
On Error Resume Next
Set XLSPadlock1 = Application.COMAddIns("GXLS.GXLSPLock").Object
 
Dim Val As String
Val = XLSPadlock1.ReadCustomCellValue("MyEntireRow", "")
 
RestoreFromValue (Val)
 
End Sub

This function generates a comma-separated string from values of a given cell range.

Function csvRange(myRange As Range)
    Dim csvRangeOutput
    Dim entry As Variant
    For Each entry In myRange
        If Not IsEmpty(entry.value) Then
            csvRangeOutput = csvRangeOutput & entry.value & ","
        End If
    Next
    csvRange = Left(csvRangeOutput, Len(csvRangeOutput) - 1)
End Function

The following event will be invoked by the compiled workbook when customers save their changes to a file:

Sub XLSPadlock_SaveCustomValues()
 
Dim XLSPadlock1 As Object
On Error Resume Next
Set XLSPadlock1 = Application.COMAddIns("GXLS.GXLSPLock").Object
 
Dim rng As Range
Set rng = Worksheets(2).Range("A:A")
 
Dim myString As String
myString = csvRange(rng)
 
p = XLSPadlock1.WriteCustomCellValue("MyEntireRow", myString)
End Sub