Tag Archives: macro

Posted on

Page Breaks in Excel

Turn them off

When you set your print area or use the Page Break Preview View, Excel will show you the page breaks on the grid. If this annoys or distracts you, here is how to remove them.

It’s a one-line macro that turns off the page breaks in the current sheet.

Sub TurnOffPageBreaks()
 
ActiveSheet.DisplayPageBreaks = False
 
End Sub

If you are new to macros then this blog post can take you through how to use them.

The above code can be copied and pasted into the code window.

If you want to turn page breaks off for all the sheets in the file use the code below.

Sub TurnOffAllPageBreaks()
 
Dim ws
 
For Each ws In Worksheets
 
  ws.DisplayPageBreaks = False
 
Next ws
 
End Sub

 

I hope these code snippets are useful.

Related Posts

Posted on

Save and Close in Excel

Let's create a macro

I started using Excel in the late 80’s on a Mac. It had a Save and Close button. When I discovered VBA in Excel on the PC, the very first macro I ever made was save and close.

I thought this would be a good example to take you through creating a macro from scratch and sharing a technique to make it easy to use.

Posted on

Increase Font Size in VBA Window

Easy change

Yes, you can make the text in the VBA window easier to read.

It is also great for training.

(This tip may not work on 4K monitors.)

To open the VBA Window press Alt + F11.

Click the Tools menu and then Options  – see image below.

Click the Editor Format tab and change the Size drop down to 14, or whatever you want – see image below.

Click OK and the font size will now be increased in the code window.

See below for a comparison between 10 and 14 point.

Much easier to read!

Related Posts

Posted on

Free Webinar Recording – Recorded Macros Tips Tricks and Traps

Getting started with recorded macros

It is easy to create a recorded macro. It is not so easy to create a flexible and re-usable recorded macro. Click the materials Button below to download the pdf manual and example file.

Learn the techniques that can allow you to record effective macros that can handle different ranges and changes to sheet names.

Macros can speed up your work and reduce the time taken for tedious tasks, as well as adding functionality to Excel.

This is the first in a series of webinars dedicated to macros. Future paid sessions this month (August 2019) will expand on the techniques taught in this session. Click here to register.

CPD note – if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances.

Download Materials

Related Posts

Posted on

Range Names and Macros

Range names rule

When you record a macro that refers to a particular cell or range on a particular sheet in Excel the range reference is hard coded into VBA (macro) code. Unfortunately this means if rows or columns are inserted or deleted in the reference range the code is not updated. There is an easy way to get around this.

Posted on

Creating a Book Index in Excel

A macro to simplify and speed up the process

My book was published just over four years ago and part of the writing process was creating an Index. To make the task easier I wrote a macro to assist me. Adding an Index to a large document can improve its usefulness. I am sharing the file I used in this blog post.

Posted on

Stopping the Clipboard from being cleared in Excel VBA

How to check if the clipboard is empty

It is common knowledge (or it should be) that running a macro clears the undo list. In general you can’t undo a macro. However some macros also clear the clipboard which can stop you copying and pasting. I have found a workaround for the clipboard problem.

Posted on

Free Excel Webinar – recording Macros Tips Tricks and Traps

Getting started with macros

It is easy to create a recorded macro. It is not so easy to create a flexible and re-usable recorded macro. Click the materials Button below to download the pdf manual and example file.

Learn the techniques that can allow you to record effective macros that can handle different ranges and changes to sheet names.

Macros can speed up your work and reduce the time taken for tedious tasks, as well as adding functionality to Excel.

This is the first in a series of webinars dedicated to macros. Future paid sessions this month will expand on the techniques taught in this session.

CPD note – if you are claiming CPD for watching this recording you need to keep your own records. People who attend the live sessions receive an annual listing of attendances.

Macro Webinar Materials

 

Posted on

Excel Cell Comments and Documentation

Macro to Create a Comment Report for a File

I was watching a video a while back and some Excel experts were lamenting the lack of a documentation standard in Excel. They mentioned that the cell comments system could be used for documentation, but there was no way to centralise all the comments. Well, I have written a macro to do just that.

Posted on

Japanese Yen Format in Excel

Applying it quickly

To apply the Japanese Yen format can take quite a few mouse clicks.

The macro that does it, on the other hand, is quite simple. Select the range, then run the macro.

Sub JapaneseYen()

Selection.NumberFormat = "[$¥-411]#,##0.00"

End Sub

If you are unsure how to use macros, see the link below.

How to use macros

Related Posts

 

Posted on

Activating a sheet in VBA

Here’s the problem. You have a number of sheets that are named after Department codes. Those sheets contain the details for each department. You have reports throughout the model that refer to these department codes. You want to be able to select a cell that contains a department code and click a button that will take you to that department’s sheet.