[Solved] Format Paintbrush by keyboard only

[Phillip]

I want to be able to use Format Paintbrush using the keyboard only as I find using the mouse to select the destination areas problematical.
I can start Format Painter by:
First allocating a shortcut key to Format Paintbrush by:
Tools/Customise/Edit/Format Paintbrush - I set "Ctrl+Alt+P"
Then select the cells containing the required formats, using shift+Arrows & Ctrl+Alt+P
Then select the cells which I want to have the same format (again using shift+Arrows)
But how do I "execute" this change?
Pressing the left button of the mouse will do it, so how can I set up a shortcut key to do the same action?

[RusselB]

A couple of options suggest themselves to me.
1) Create a macro (written or recorded), then assign the macro to a key/key combination.
2) Try using Paste Special (Ctrl+Shift+V) and have just Formats selected.
Note: For #2, you might need to use the Copy command (Ctrl+C) from a cell that is already formatted before using the step 2 option.

I am unable to test these, as I can't find the Paintbrush that you refer to, even following the steps you outlined.

[Zizi64]

I recommend it: use the Cell styles instead of the direct formatting and the paintbrush function.

[Phillip]

Hi Russell,
The "Format Paintbrush" is an icon on my toolbar that looks like a wide paintbrush. When you select a group of cells, then click it with the mouse, the cursor changes from an arrow to a paint tin with paint pouring out of it. You might know it as "Format Painter" in Excel.
I find this option very useful as once you have set up formats, you can then copy them onto other cells without changing the contents. As to your suggestions:
(1) Would a macro work when the cells to be selected / cells to be copied to can be any number of cells and can be anywhere?
(2). Select cells already formatted and Copy is the same as Select cells and Edit/Format Paint - same number of key-strokes.
Select cells to be formatted and Paste Special works, but as you say you have to switch off all the options you do not want and switch on "Formats".
Plus when you want to use Paste Special for other uses, you then find all your usual defaults are changed - a real pain!

What I was looking for was the keyboard equivalent of "do a mouse left click", which works fine.

Hi Zizi64,
How would Cell Styles help? Would I not need to select each cell individually and then choose a style by the shortcut key assigned to it. This seems a lot more work.

[Zizi64]

I find this option very useful as once you have set up formats, you can then copy them onto other cells without changing the contents.

My opinion: The Styles are more useful feature.


Otherwise you can copy/paste the direct format properties only by a macro. Use the Paste Special/Format only option in your macro. You can assign your macro to a hotkey.
Usage: select a range, Ctrl-C (it will copy the contents and format properties onto the Clipboard) and the select the target cell (or cell range) and hit the hotkey.

It is better to WRITE a macro (based on the API functions) instead of recording it, but this task is available by a recorded macro too:

Code: Select all

sub Paste_Format_only

rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(5) as new com.sun.star.beans.PropertyValue
args1(0).Name = "Flags"
args1(0).Value = "T"
args1(1).Name = "FormulaCommand"
args1(1).Value = 0
args1(2).Name = "SkipEmptyCells"
args1(2).Value = false
args1(3).Name = "Transpose"
args1(3).Value = false
args1(4).Name = "AsLink"
args1(4).Value = false
args1(5).Name = "MoveMode"
args1(5).Value = 4

dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args1())


end sub

And you can paste (by this macro) the applied styles of the source cell range into the target cell range too.
Tried in my LibreOffice 4.4.7

[robleyd]

How would Cell Styles help? Would I not need to select each cell individually and then choose a style by the shortcut key assigned to it.

You can select a contiguous or non-contiguous group of cells and apply a style to all of the selected cells. Use Ctrl and the mouse to select non-contiguous ranges.

[Phillip]

Thanks Zizi64,
I have set up a macro to do the Paste Special - Formats only.
While this does what I wanted, it has the annoying side effect of leaving the Paste Special options changed.
So how can I reset the Paste Special Options to Text/Numbers/Date?
I could create another macro to copy a cell, Paste Special it back on itself with these options only, but this has also has the other annoying side effect of deleting any comment, even though the "Comments" option is not set!
So I would not only have to remember to press the unnecessary hotkey to reset the Paste Special options, but also remember to not do it on a cell that has a comment! Oh
As I started off with:
I can select the source cells and start up Format Paintbrush, I can also select the destination cells (all just using the keyboard).
What I can't do is "Action" the Format Paintbrush on the destination cells by just using the keyboard - I have to press the left button of the mouse, while remembering to move the mouse cursor into the destination selection (it can be any of the cells)!
Note: At least using the Mouse to action the Format Paintbrush does not delete comments - so I suppose I should be grateful.
I also had fun using the smilies.

[Zizi64]

I have set up a macro to do the Paste Special - Formats only.
While this does what I wanted, it has the annoying side effect of leaving the Paste Special options changed.
So how can I reset the Paste Special Options to Text/Numbers/Date?

The macro code will store the settings what you adjusted at recording the macro. Then you can re-adjusted the settings of the manual "paste special" function as you want. (Make a new Paste Special action manually with some re-adjusted settings, and the Calc will store the wanted settings.) Running the macro will not modify the settings of the user interface of the Paste Special function.
...And you can write (or record) more macros with various Paste Special settings...

[Richarda44]

I am not sure why using the mouse might be difficult in selecting cells? I have just learnt a new way to use the paintbrush for multiple areas: by double clicking the icon which then remains permanently active formatting all over the place. But you have to remember to click the icon when finished formatting to turn it off and get back to normal control.

[Phillip]

Thanks Zizi64, you are right: running a macro does not change the Paste Special Options. What happened was that the options were changed when I recorded the macro, just as you said!
So very useful.
However running a macro does alter the user interface of the Search function:
Setting up the search to look backwards, then running a macro to find a specific value (mark point in the middle of a large spreadsheet), turns off the "Backwards option". Is this a bug?

[Phillip]

Richarda44:
Problem is shaking hand can result in mouse moving onto a different cell/row. Plus us old guys can use keyboard much quicker and precisely than using the mouse.
Double clicking Paintbrush is an alternative, but requires a lot more work. However it does allow the use of Undo as follows:
Select source cells with keyboard - move to mouse, double click Paintbrush icon
Select destination cells with keyboard - move mouse over any of the selected cells - left click to action
- oops! mouse on not in selected area, so undo CTRL+Z, then try again, and again until ok
- then click Paintbrush icon (or ESC).

So all in all the simplest option is:
Select source cells by keyboard -Copy with Ctrl+C
Select destination cells by keyboard - Paste Special by macro to change formats only by Ctrl+Alt+V (assigned hotkey).

A shame as it seems to be almost there:
Select source cells by keyboard - start Format Paintbrush by assigned hotkey
Select destination cells by keyboard - (? how to action Paintbrush)
Mouse left click works fine so long as mouse over ANY of the selected cells (Otherwise area of formats are pasted onto same size, STARTING on the cell the mouse is over).

During testing I found other problems using the mouse only:
If the destination area is smaller than the source area, you get a message warning that the clipboard area is larger. Moving the mouse to select "Yes" instead of the default "No" (default), results in another area being selected, which is then completely ignored! (The original selected area is extended to the source area size).

I guess the developer of Format Paintbrush thought only in terms of using a mouse, with the expectation that the user would always accurately swipe the same length area.
I have marked this as [SOLVED] as I can do what I wanted.

[Zizi64]

However running a macro does alter the user interface of the Search function:
Setting up the search to look backwards, then running a macro to find a specific value (mark point in the middle of a large spreadsheet), turns off the "Backwards option". Is this a bug?

Please upload your macro code and an ODF type example spreadsheet file here.

[Phillip]

Thanks Zizi64 for your interest.
Here is an example spreadsheet with instructions how to test and the macro code

Code: Select all

sub GoToESRT
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dim args1(17) as new com.sun.star.beans.PropertyValue
args1(0).Name = "SearchItem.StyleFamily"
args1(0).Value = 2
args1(1).Name = "SearchItem.CellType"
args1(1).Value = 1
args1(2).Name = "SearchItem.RowDirection"
args1(2).Value = true
args1(3).Name = "SearchItem.AllTables"
args1(3).Value = false
args1(4).Name = "SearchItem.Backward"
args1(4).Value = false
args1(5).Name = "SearchItem.Pattern"
args1(5).Value = false
args1(6).Name = "SearchItem.Content"
args1(6).Value = false
args1(7).Name = "SearchItem.AsianOptions"
args1(7).Value = false
args1(8).Name = "SearchItem.AlgorithmType"
args1(8).Value = 0
args1(9).Name = "SearchItem.SearchFlags"
args1(9).Value = 65536
args1(10).Name = "SearchItem.SearchString"
args1(10).Value = "ESRT"
args1(11).Name = "SearchItem.ReplaceString"
args1(11).Value = ""
args1(12).Name = "SearchItem.Locale"
args1(12).Value = 255
args1(13).Name = "SearchItem.ChangedChars"
args1(13).Value = 2
args1(14).Name = "SearchItem.DeletedChars"
args1(14).Value = 2
args1(15).Name = "SearchItem.InsertedChars"
args1(15).Value = 2
args1(16).Name = "SearchItem.TransliterateFlags"
args1(16).Value = 1280
args1(17).Name = "SearchItem.Command"
args1(17).Value = 0

dispatcher.executeDispatch(document, ".uno:ExecuteSearch", "", 0, args1())


end sub

[Zizi64]

The Ctrl-F function (the UI) is basicly different in the LibreOffice Calc. (I am using LibreOffice.) Therefore I tried it by the menu item
Edit - Find and Replace
but the behavior of that function is different too.