Macro styling without messing up undo

[mike_papa]

I have a macro that highlights row and column of the selected cell in certain area of spread sheet, by changing it’s background to gray. It is attached to Sheet Events / Selection Changed event. Unfortunately it is being registered in history, and undo (ctrl+z) feature is practically unusable.

Is it possible to apply such styling without it being registered in operation history, or remove it from that history as last step of macro?

Function Highlight()
    Dim oDoc As Object
    Dim oSheet As Object
    Dim oCells As Object
	Dim oCell As Object
    Dim sCell As String
	Dim firstCol as String
    Dim lastCol as String
    Dim firstRow as String
    Dim lastRow as String
    Dim allCells as String

    firstCol = "D"
    lastCol = "BL"
    firstRow = "7"
    lastRow = "70"

    allCells = firstCol+firstRow+":"+lastCol+lastRow

    oDoc = ThisComponent
    oSheet = oDoc.Sheets(0)

    oCells = oSheet.getCellRangeByName(allCells)
    oCells.IsCellBackgroundTransparent = true
    sCell = oDoc.CurrentSelection.AbsoluteName
    sCol = Split(sCell, "$")(2)
    sRow = Replace(Split(sCell, "$")(3),":","")

    HighlightRow(oSheet, sRow, firstCol, lastCol)
    HighlightCol(oSheet, sCol, firstRow, lastRow)
End Function

Sub HighlightRow(oSheet as Object, sRow as String, firstCol as String, lastCol as String)
    Dim sCells as String
    Dim oCells as Object

    sCells = firstCol+sRow+":"+lastCol+sRow
    oCells = oSheet.getCellRangeByName(sCells)
    oCells.CellBackColor = RGB(235, 235, 235)
End Sub

Sub HighlightCol(oSheet as Object, sCol as String, firstRow as String, lastRow as String)
    Dim sCells as String
    Dim oCells as Object

    sCells = sCol+firstRow+":"+sCol+lastRow
    oCells = oSheet.getCellRangeByName(sCells)
    oCells.CellBackColor = RGB(235, 235, 235)
End Sub

[mikekaganski]

See Inserting image in Macro is not undoable

[Ratslinger]

Hello,

You can try dealing with the undo manager but care must be taken. The undo manager is available using:

oUndoManager = ThisComponent.getUndoManager()

To stop the recording of an action the manager must be locked:

oUndoManager.lock()

Immediately after the action it must be unlocked:

oUndoManager.unlock()

If the process is not correctly done you may not be able to correctly set/unset the manager. See this bug → Bug #99975

There are other actions available such as checking if the manager is locked:

isLocked = oUndoManager.isLocked()

True if locked and False if not.

A fast and sure way to empty the undo contents is:

oUndoManager.clear()

but be sure that is what is wanted as the undo is completely wiped out. Have found no method to remove specific items such as current or last 5 items.

[PJTraill]

oUndoManager.enterUndoContext () and oUndoManager.leaveUndoContext are useful for making your (macro) operations atomically undoable.