Record a Macro in Excel

To automate the manual tasks you do repeatedly, you can use a simple macro. Below is an simple example of how we can use a macro to automate tasks

Consider for example a table of data as shown. And you need to repeatedly format a cell with a particular format (bold, bordered, background yellowed, font in red color).

Basic example of a macro

Step 1: Click the Record Macro button on the bottom left of excel window

Record button in Excel Window

Step 2: A dialogue box opens as shown below. Change the Macro name (if needed), put a shortcut key, scope of the macro and description. If you intend to use a lot of macros, it is a good practice to fill the details. Click Ok after all the details are filled.

Macro record dialogue box

Step 3: The macro continues recording until you manual stop the recording. Be careful what you do after the macro is recording. Do not change the selected cell. Change formatting of the cell (selected before recording a macro). Go to the Record Macro button (now the logo will be different and looks like a stop button) and click it. This stops the recording of the Macro.

Step 4: The macro is now ready to do manual tasks automatically. Select any cell and click the shortcut key chosen in Step 2. The cell will redo all the actions recorded by the macro.

Note: You will not be able to undo the changes you made using a Macro. Be careful with a Macro

Hope that helps,

Ramada

PS: please feel free to drop you excel/ word/ power point queries to mad.exceltips@gmail.com. I will try and respond as soon as I can. The more curious your question, the faster I respond.

Microsoft Word 'Document Map' Bug and work around

When I am working with a huge word document, I find using a 'Document Map' very convenient to navigate the whole document. However, there is one bug, which is persistent and annoying as hell. Thankfully there is a work around which I will explain in this post.

If you are unfamiliar with Document Map, please read my earlier post on Document Map (http://mad-exceltips.blogspot.com/2014/04/microsoft-word-document-map.html). 

With a document filled with content and Document Map enabled, originally looks like this

Document Map in Microsoft Word

However, when you close and open the document, the Document Map jumbles up as below. 

Jumbled Document Map after re-opening the document

Before knowing the workaround, I have reorganized the Document Map numerous times, before I got annoyed and looked for a workaround. The work around doesn't really fix the bug, but if you follow few practices, the Document Map will remain the same. 

The issues is, Word re-formats the document when you re-open the document. Keep an eye on the bottom right (left of zoom options), when you open the document. First, Word says 'Opening' then jumps to 'Word is formatting the document'. If it says, 'Word is formatting the document' that is when the bug is introduced. And after the document is opened, the Document Map will look all jumbled up.

The workaround is

1. The moment, you see 'Word is formatting the document'; do not save the document. If you save it, the new format will be saved. 
2. Instead, go to View -> Un-check Document Map
3. Close the document without saving it.
4. Re-open the document. Now, it shouldn't say 'Word is formatting the document'. If it still does, do mail me. We will figure out a workaround.
5. After you open the document, go to View -> Check Document Map. Bingo !! The map is intact. 

Any deviations, from what I described above; or better workarounds, please do mail me. Would be happy to listen.

Hope that helps,

Ramada

PS: please feel free to drop you excel/ word/ power point queries to mad.exceltips@gmail.com. I will try and respond as soon as I can. The more curious your question, the faster I respond.