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")

 

 

WriteCustomCellValue and ReadCustomCellValue will only work if invoked in the appropriate events XLSPadlock_XYZCustomValues.

 

 

 

Full example with comments

 

 

 

The entire following code must be placed into a module.

 

 

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