Copy AND add background colour each time I hit ctrl+c

[phineasgaged]

Hi,

I would like the background of each cell I copy in calc to change colour (preferably yellow).

In Excel I simply recorded a macro but am not sure this is possible, or the easiest way to do this in LibreOffice.

I also tried customize → keyboard. But could not see how to add a background colour AND the ‘copy’ command to ctrl+c.

I really don’t understand a lot of programming jargon, please note you’re speaking to a bit of a dummy who hasn’t yet had a moment to study Libre. Thanks so much!

[hjek]

You can record macros in LibreOffice, too. First go to Tools → Macros → Record Macro, then record your actions (copy the selection, set yellow background). Save your macro, and assign your macro to a key.

[phineasgaged]

Thanks hjek!

I did try this but not sure it’s possible to record a macro for the background colour when I’m using the mouse pointer to select it.

[Ratslinger]

Hello,

Not certain you thought out what all may happen. While it can be done using a macro, recording a macro is not going to help in this case. When recording a macro you would be dealing with a specific cell or cells. In your case it is random (that is, dependent upon the current selection).

Also what happens if you never actually paste what was copied or cancel the copy? If the background color is set when Ctrl+c is detected how do you reverse it if the operation is cancelled? What if you are copying to another Calc or other document? Still set background?

Just a lot of unanswered questions.

To actually set a background dependent upon a key combination can be done using a key handler to detect this combination. The attached sample demonstrates this but does not answer the previously mentioned questions. There are two items on a separate toolbar in this sample: sStartXKeyHandler and xStopXKeyHandler. By clicking on the start, Ctrl+c will place a yellow background in the selected cell(s). Selecting the stop tool will revert to no insertion of the background.

Sample ----- KeyHandlerSetBackgroundOnCopy.ods

Additionally, since you claim to know little on the subject of macros, it is best if you review the Macro sections in the LO documentation guides found here → Documentation/Publications

Edit:

While @jimk does present a valid method, it only displays further what I was attempting to explain. In both my above sample & the one by Jim K, if you use Ctrl + c in either case the cell background gets set even if the copy is cancelled. This is erroneous.

Have elaborated my code to only change the background color when Ctrl + v is used after Ctrl + c in the Calc document. So if something was copied elsewhere & Ctrl + v is used in Calc, nothing is highlighted. Likewise, there is no highlighted cell simply by pressing Ctrl + c.

Sample modified ----- KeyHandlerSetBackgroundOnCopy.ods - this now allows Enter key to be used as well as the Ctrl + v for pasting.

[phineasgaged]

Hey Ratslinger!

Problem solved, thanks! Using a key handler was in fact what I needed.

Much appreciated! I am learning the ins & outs of macros, and won’t need to bother this forum with questions again, but I did need this ASAP. Take care

[jimk]

The second version could also be done by customizing a hotkey, similar to my answer but with an additional routine for pasting. And probably a global variable to store the source selection. Anyway, the highlight can be canceled as noted in my edited answer.

[Ratslinger]

Thanks for your response @jimk. There is no question here about code or complexity - yours is much more simple. My real point in this entire process was the lack in original question of thinking through the consequences of the request as written. I still have about half a dozen possible problems with the use of this.

[jimk]

@Ratslinger gave a working solution but it is more complex than needed.

Sub CopyAndHighlight
    Dim frame As Object
    Dim dispatcher As Object
    Dim oCurrentSelection As Object
    Dim nCellBackColor As Variant
    frame = ThisComponent.CurrentController.Frame
    dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
    dispatcher.executeDispatch(frame, ".uno:Copy", "", 0, Array())
    oCurrentSelection = ThisComponent.getCurrentSelection()
    nCellBackColor = oCurrentSelection.CellBackColor
    oCurrentSelection.CellBackColor = 16773632
End Sub

Go to Tools → Customize → Keyboard, find Ctrl+C, expand LibreOffice Macros in the lower left pane to find the macro, and press Modify.

EDIT:

To cancel the highlight, for example if the cell does not need to be copied after all, press Ctrl+Z to undo.