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

 

 

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

 

 

 


Copyright © 2020 G.D.G. Software