newspaint

Documenting Problems That Were Difficult To Find The Answer To

Category Archives: Visual Basic

A Microsoft Word Macro to Neaten Paragraphs

When I’m constructing a list of items I often want to keep the lines together so that they are not split across a page.

By default a paragraph in Microsoft Word has trailing vertical whitespace. That means there’s a space below the last line of the paragraph that pushes the top of the next paragraph down the page, leaving a gap between paragraphs.

So when I have a list of items they look like the following when first imported:

- line 1
gap
- line 2
gap
- line 3
gap

But what I want is:

- line 1
- line 2
- line 3
gap

… and I want to ensure those three lines are not broken up over a page break.

To achieve this I highlight all the lines except the last line and run the following macro:

Sub ParagraphCutAndKeepWithNext()
'
' ParagraphCutAndKeepWithNext Macro
'
'
    With Selection.ParagraphFormat
        .SpaceAfter = 0
        .KeepWithNext = True
    End With
End Sub

Visual Basic Macro to Change Selected Text to Courier New 9pt

Before any macros can be created in Microsoft Office 2010 and later (with the utterly ridiculous, dysfunctional, and pointless “ribbon” interface – seriously, Microsoft couldn’t get anything more wrong than removing menus from applications the world had come to depend upon) a “developer” toolbar has to be enabled.

  • choose the File toolbar from the utterly ridiculous and dysfunctional ribbon
  • on the left hand side choose “Options” – this will bring up a dialog box of options
  • choose the awfully and ignorantly spelt Americanised option “Customize Ribbon”
  • on the right hand ride where it says, in ridiculous American spelling, “Customize the Ribbon”, check the “Developer” Main Tab, then click OK on the dialog box

Now macros can be created by choosing the “Developer” toolbar from the utterly ridiculous and dysfunctional ribbon, and the “Macros” button can be clicked from there.

A macro name can be given, such as “selection_to_fixed_width“, with the following code (thanks to this post for guidance):

Sub selection_to_fixed_width()
  Dim oInspector As Inspector
  Dim oSelection
  Dim iRet As Integer
   
  Set oInspector = Application.ActiveInspector
   
  If oInspector.EditorType = olEditorWord Then
    Dim oApplication
       
    Set oApplication = oInspector.WordEditor.Application
    Set oSelection = oApplication.Selection
  End If

  If oSelection Is Nothing Then
    iRet = MsgBox("Script does not know how to find a selection",vbOKOnly,"Error")
    Exit Sub
  End If
  
  Dim nLength As Integer
  nLength = oSelection.End - oSelection.Start
  
  If nLength < 1 Then
    iRet = MsgBox("No text selected", vbOKOnly, "Error")
    Exit Sub
  End If

  With oSelection
    .Font.Name = "Courier New"
    .Font.Size = 9
  End With
End Sub

For Microsoft Word the following Macro can be used:

Sub selection_to_fixed_width()
  Dim oInspector
  Dim oSelection As Selection
  Dim iRet As Integer

  ' see https://docs.microsoft.com/en-us/office/vba/api/word.window
  ' see https://docs.microsoft.com/en-us/office/vba/api/word.window.selection
  ' see https://docs.microsoft.com/en-us/office/vba/api/word.selection

  Set oSelection = Application.ActiveDocument.ActiveWindow.Selection
  If oSelection Is Nothing Then
    iRet = MsgBox("Script does not know how to find a selection",vbOKOnly,"Error")
    Exit Sub
  End If

  Dim nLength As Integer
  nLength = oSelection.End - oSelection.Start

  If nLength < 1 Then
    iRet = MsgBox("No text selected", vbOKOnly, "Error")
    Exit Sub
  End If

  With oSelection
    .Font.Name = "Courier New"
    .Font.Size = 9
  End With
End Sub

You can now add a small button to your "quick access toolbar" which is the thin strip at the very top of every window (above the window's title). Where you see a small drop down arrow with a horizontal line above it – click this upside-down looking eject icon. From the menu choose "More Commands…" – this opens a dialog box. In the "Choose commands from:" dropdown select Macros – then drag your macro from the left-hand list to the right-hand list titled "Customize Quick Access Toolbar". Click OK and you now have an icon you can press to run the macro.

If this is Microsoft Outlook you'll only want to do this on a "new message" window (as you're only wanting to change text you're writing/creating).

At some point you'll restart your Microsoft Office application and find your macro no longer does anything. This is because Microsoft Office products, by default, disable macros from running. To enable them visit your Developer ribbon toolbar, click on "Macro Security", and choose "Notifications for all macros" from the dialog box. Then click OK and restart your Microsoft Office application.

The next time you try and run your macro you should get a warning that macros are disabled and you should be asked whether you want to enable macros.