Hi ,

Center Across Selection Macro with Chat GPT

Annoyingly, there’s a shortcut for Merge & Center on the Ribbon, but nothing for Center Across Selection which is far superior.

Center Across Selection achieves the same result but doesn’t cause the rude interruptions to selecting ranges that Merge & Center does.

To remedy this, I consulted ChatGPT to write a Center Across Selection shortcut macro that I can attach to an icon and place on my Quick Access Toolbar, and I’m happy to say it did a pretty good job.

Watch the Video

Download the PDF and Macro Code

Get step by step instructions here in a handy PDF.

Get the text file with Center Across Selection macro code.

ChatGPT Prompt and Response

It’s important that you write your prompt as clearly and with as much information as possible.

The more detailed and precise you can be, the better the result. Although, there’s no guarantee it’ll do everything you ask, as you’ll see.

Below is my prompt:

Write a VBA macro that will remove merged cell format from the selected cells and replace it with center across selection alignment. Make it so I can assign the macro to a button that I put on the Quick Access Toolbar.

The response was this code, nicely commented so I could follow what each section was doing:

It also gave me some instructions on what to do with the code:

Corrections to ChatGPT Code

Unfortunately, ChatGPT assumed I wanted to remove all formatting. I didn’t. And by putting the macro in the worksheet (step 2 above) I will only be able to use it in the current file, not any file I have open.

In the image below you can see where it has assumed I want to clear formatting and has added a line of code to do so: clear.CellFormats

All I need to do is remove the sections in orange boxes and I’m good to go.

The assumption that I wanted to put the macro in the current workbook is an easy fix…if you know how.

I asked ChatGPT several times and it eventually told me to save it in a module in my Personal Macro Workbook.

Inserting Center Across Selection Shortcut Code in Excel

To make the Center Across Selection shortcut macro available in any workbook you open, you can save it in your personal macro workbook.

The personal macro workbook is a hidden workbook that opens automatically whenever you start Excel and is available in all workbooks.

Here's how you can save the macro in your personal macro workbook:

1. Press `Alt+F11` to open the Visual Basic Editor in Excel.
2. In the Project Explorer pane, right-click on the VBAProject (PERSONAL.XLSB) and select Insert > Module. This will insert a new module in your personal macro workbook.

If you don’t see your personal macro workbook, check out this post: Easy way to create a Personal Macro Workbook.

1. In the code window of the new module, paste the VBA code for the macro.
2. Save and close the Visual Basic Editor.

Now the macro will be saved in your personal macro workbook and will be available in any workbook you open.

Assigning the Macro to an Icon on the QAT

To add the macro to the Quick Access Toolbar:

1. Right-click on the Quick Access Toolbar and choose "Customize Quick Access Toolbar".

1. In the Excel Options window, under the "Choose commands from" drop-down list, select "Macros."
2. In the left pane, select "PERSONAL.XLSB!RemoveMergeFormatAndApplyAlignment" (replace "RemoveMergeFormatAndApplyAlignment" with the actual name of your macro if you’ve changed it).
3. Click the "Add" button to add it to the Quick Access Toolbar.

Bonus tip: if you prefer a different icon, click ‘Modify’ and choose from the list.

1. Click "OK" to close the Excel Options window.

Now the macro button will be available on the Quick Access Toolbar in any workbook you open.

See the Macro in Action

This is an animated image. If you don't see anything moving, your email doesn't support animated images. 

To see the animation on the blog post, Click here.

Other Excel ChatGPT Automations

Now you see how easily you can create shortcuts like this, please share your ideas in the comments for other macros you might like ChatGPT to write for you.

Please Share

If you liked this please share this tutorial with your friends and colleagues.

Have a great day,

Mynda Treacy

Co-founder My Online Training Hub