Macro to choose custom format in Google Sheets

I'd like a macro to choose a custom format in Google Sheets.

I've already created the custom format. It is accessed by choosing Format > Number > Custom Format I've created. In the attached screenshot, the custom format is shown surrounded by a red box. FWIW, this simply indents the text in a cell 5 spaces. Indenting is built into MS Excel but not Google Sheets.

I tried recording but that didn't seem to work.

ScottC

1 Like

Hi @sccardais,
I've just had a quick look at this and have two approaches for you to try.

When I run Google Sheets and bring up the Format menu this is what I see:

KM 0 2022-08-19_14-18-28

so the first approach is based on this.

The red steps that I've added show that I need to press a keyboard shortcut. (This looks different to yours insofar as my custom format at step 3 has beside it a shortcut key but we'll come to that in the second approach in a minute.) So, in this case I can apply the custom format by using the following shortcut keys:

o
N
1

The KM macro to do this is here:
Test Google Sheets Formatting (1).kmmacros (3.0 KB)

Click to see macro

The second approach is based on the screenshot you posted, where there is no shortcut key indicated for the custom format. So the key presses that need to be applied in this case are as follows:

o
N
down-arrow 13 times
Return

here is the macro to do that:

Test Google Sheets Formatting (2).kmmacros (4.0 KB)

Click to see macro

In both of these macros I've provided, you'll see I've inserted Pause actions. They are there to make sure that KM types at a slower rate than usual to help your computer "keep up"!

If you try these macros, let us know how you get on.

1 Like

Tiffle:

First --- thank you very much for sending both approaches.

I got the first version working after some fiddling. Thank you.

I had to make the changes described below to my Keyboard Shortcuts for Google Sheets -- not your macro.

Initially, f5 was opening a side window called "Outliners." I had never seen this before. After digging around in Sheets, I found a toggle switch "Enable compatible spreadsheet shortcuts" at the bottom of Help > Keyboard Shortcuts. If enabled, f5 activates "Navigation > Go To Range." This opened the side window called, "Outliners." I didn't see any way to change this shortcut or disable this particular shortcut which is too bad because there were some others in this hidden area that I thought would be handy.

Once I disabled "Enable compatible spreadsheet shortcuts," the macro worked fine. Note: this doesn't deactivate all the normal keyboard shortcuts in Google Sheets -- just the separate group that's enabled as described above.

Thank you again.

This is going to save me some time and I learned something.

ScottC
Saluda, NC

Tiffle:

CORRECTION ... f5 is still opening the "Outliner" window. I'd swear I turned this off as described in my earlier email but I just double-checked. Even after unchecking "Enable compatible spreadsheet shortcuts," f5 still opens the Outliner window.

No matter, I just changed the shortcut that activates the KM macro.

Just FYI.

ScottC

I normally don’t set a trigger for the macros I post up here so I should have said “change F5 to what ever trigger you prefer”! I’m glad you got it working anyway. If you need to learn/ask more the KM forum is the best place to do it!