Ask Your Question
0

Macro styling without messing up undo

asked 2018-12-20 10:31:09 +0100

mike_papa gravatar image

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
edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2018-12-21 06:30:45 +0100

Ratslinger gravatar image

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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-12-20 10:31:09 +0100

Seen: 23 times

Last updated: Dec 21 '18