Category: Uncategorized

Macro: Repetitive Replacing of Table Cells

Here is today’s time saver:
I had some tables (actually 1000 pages of tables!) where I needed to replace cells containing various text (denoted by comments from the SME) with a standard word (which was Reserved in this case). Normally, I would triple-click in the cell and then control-v to paste in the text (5 clicks/keys), but there were too many places that I needed to make this change.
Using the following macro this task was reduced to two clicks, once in the cell and then once on the button for the macro. There was one other requirement for this macro, which was that if the cell was in bold, it received slightly different text.

Sub makeCellReserved()
   Selection.SelectCell

   If Selection.Range.Bold = True Then
       Selection.Text = “RESERVED”
   Else
       Selection.Text = “Reserved”
   End If
End Sub

AIT: Highlighting Variants

This post describes how I use highlighting and release states in Author-It to make reviewing documents easier. This procedure assumes that you have a main book along with another book that uses variants of the objects in the main book. The variants are either variant topics or embedded variant topics. 
By using this procedure, the reviewer can review one complete document, and then just review the highlighted sections in the second document.
Setup

1. Open AIT Administrator
2. Select Security -> Release States
3. Add a new state:

a. Right click -> Add State
b. State name: HighlightVar
c. Right click over the new state, select Check Row
d. Click the box near the end of the row for HighlightVar / Review Color
e. Select yellow
f. Check that the boxes are checked for all relevant users

4. Do step 3 again to add the state DoNotHighlightVar, but skip steps d and e.

5. Close AIT Administrator
6. Open AIT
7. In your library, sort the objects by variant
8. Select (using Ctrl+click) all variants of the default object
9. Right mouse click, then select Change Release State -> HighlightVar

The variant of the main document can now be published with highlighted sections.

Print Without Highlighting

1. Go to the search tab
2. Select HighlightVar from In State
3. Click on Find Now
4. The search result shows all of the objects with highlighting
5. Select all of the objects
6. Right click -> Change Release State -> DoNoHighlightVar
7. Publish

Print with Highlighting
1. Go to the search tab
2. Select DoNoHighlightVar from In State
3. Click on Find Now
4. The search result shows all of the objects with highlighting
5. Select all of the objects
6. Right click -> Change Release State -> HighlightVar
7. Publish

Thanks to Miriam Lottner at Tech-Tav for pointing me in the right direction.

Split Excel Columns into Multiple Rows

I am always forgetting how to do this, so today I am writing it down. When you have one long column of data in Excel and you want to split it into multiple rows of data (such as rows with 5 columns), how do you do it?
Assuming the data is in column A, then put into B1:

=Indirect(Address((Row(A1)-1)*5+Column(A1),1))

Copy to the right four times and then down as far as you need to. If you want to change the number of columns in the result, then change the ‘5’ to the # of columns.

[Source OzGrid]

Author-It: Pasting Text with Formatting

For those of you using Author-It, there is an unfortunate feature that text that is pasted into a topic (from a Word doc), gets a “No Paragraph Style”. You then have to select the text and apply an AIT style. At the latest Author-It User Group meeting, the way to get around this was revealed.
Follow these steps:
  1. From the main window select: Import -> (Transformation) Profiles
  2. Select Word Documents
  3. Click Properties
  4. Check the box next to Use for Paste
Now, when you want to paste text into AIT, click:
Paste -> Paste Using Import Profile -> Word Documents

That’s it. Happy pasting.

Filename Field Code in Word

Many times I’ll use a field code to insert the filename into the footer of a document. The field code looks like this:

{ FILENAME \*MERGEFORMAT}
The result of the field code looks like this: foobar.doc

The filename has a “.doc” suffix. When I create the PDF, the filename in the footer still has the “.doc” suffix, and not “.pdf”.

What I found is that the field code uses the filename exactly as it appears in the Windows Explorer window. This may sound trivial, but to solve the problem we just need to hide the extension in Windows Explorer.
To change the setting follow these steps:
  1. Open Windows Explorer
  2. Click Tools -> Folder Options
  3. Click on the View tab
  4. Check Hide extensions for known file types
Now, after updating the field code, we get foobar instead of foobar.doc.

[Source Wordribbon.Tips.net]

Painting in Irfanview

You learn something everyday. A co-worker asked me if I knew of a program to add circles and arrows to an image. Immediately, I thought “Irfanview must be able to do that“. 
Quickly, I opened an image in Irfanview and searched the menus for “Add circle” or “Add arrow” or something like that. Instead I found the menu for “Show Paint Dialog”. Bingo.
Click “Edit -> Show Paint Dialog”, or type F12 and the MS Paint dialog opens. Now you have the capability of MS Paint from within Irfanview. Add a circle, add an arrow, or whatever you like 🙂

En, Em, Eh, What?

There are different types of dashes: hyphen, en dash, and em dash. What is the difference between them?
Hyphen (-): This is the width of one digit. It is used to hyphenate words that break across a new line, or to join compound words. Ex. My, this professor is long-winded.
En Dash (–): This is the width of the letter ‘n’. It is used to indicate a range of values. Ex. I will be on vacation from November 6–10.
Em Dash (—): This is the width of the letter ‘w’. This is used to indicate a break in thought. Ex. I was eating pizza—and what a good pizza it was—with all my favorite toppings.
Using courier font we can get a better idea of the widths:
0 n m
– – —
[Sources: Fonts.com and Wikipedia]

Excel Macro for Background Color

I was writing an Excel Macro and I needed to set the background color of a cell based on the text value. To do this I wanted to use Conditional Formatting, and I found out that you cannot (AFAIK) setup Conditional Formatting from inside of a macro. But, you can create a macro that loops through a range and sets the formatting.
Here is how:
  1. Open the VB editor (Alt + F11)
  2. Open ThisWorkbook under Microsoft Excel Objects
  3. Add the following code:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Set myRange = Range(“C4:C104”)
    For Each Cell In myRange
        If Cell.Value = “n” Then
            Cell.Interior.ColorIndex = 3
        End If
    Next
End Sub
Anytime  a change is made to the workbook, the macro is called. It checks a range of cells, and then if the text is ‘n’ it sets the background color to 3. But what is ‘3’?
Excel uses an object called ColorIndex which takes an integer as its argument. The integer is one of 56 colors, given by the following table (a link to the source is below):

[Color 0] [Color 0] [Color 15] [Color 15] [Color 30] [Color 30] [Color 45] [Color 45]
[Color 1] [Color 1] [Color 16] [Color 16] [Color 31] [Color 31] [Color 46] [Color 46]
[Color 2] [Color 2] [Color 17] [Color 17] [Color 32] [Color 32] [Color 47] [Color 47]
[Color 3] [Color 3] [Color 18] [Color 18] [Color 33] [Color 33] [Color 48] [Color 48]
[Color 4] [Color 4] [Color 19] [Color 19] [Color 34] [Color 34] [Color 49] [Color 49]
[Color 5] [Color 5] [Color 20] [Color 20] [Color 35] [Color 35] [Color 50] [Color 50]
[Color 6] [Color 6] [Color 21] [Color 21] [Color 36] [Color 36] [Color 51] [Color 51]
[Color 7] [Color 7] [Color 22] [Color 22] [Color 37] [Color 37] [Color 52] [Color 52]
[Color 8] [Color 8] [Color 23] [Color 23] [Color 38] [Color 38] [Color 53] [Color 53]
[Color 9] [Color 9] [Color 24] [Color 24] [Color 39] [Color 39] [Color 54] [Color 54]
[Color 10] [Color 10] [Color 25] [Color 25] [Color 40] [Color 40] [Color 55] [Color 55]
[Color 11] [Color 11] [Color 26] [Color 26] [Color 41] [Color 41] [Color 56] [Color 56]
[Color 12] [Color 12] [Color 27] [Color 27] [Color 42] [Color 42]
[Color 13] [Color 13] [Color 28] [Color 28] [Color 43] [Color 43]
[Color 14] [Color 14] [Color 29] [Color 29] [Color 44] [Color 44]
A more complex example is shown below. This is used in a ToDo sheet. It checks the “Done” column (C) to see if  the cell is marked ‘y’ or ‘n’ and sets the corresponding color. If the task is done (C=’y’) then it also sets the cell adjacent tothe color gray with strikethrough text.
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Set myRange = Range(“C4:C104”)
    For Each Cell In myRange
   
        If Cell.Value = “n” Then
            Cell.Interior.ColorIndex = 3
        End If
        If Cell.Value = “y” Then
            Cell.Interior.ColorIndex = 10
        End If
        If Cell.Value “n” And Cell.Value “y” Then
        Cell.Interior.ColorIndex = xlNone
        End If
       
        ‘ add strikethrough
        Dim myRow As Integer
        Dim myCol As Integer
        myRow = Cell.Row
        myCol = Cell.Column – 1
       
        If Cell.Value = “y” Then
            Cells(myRow, myCol).Font.Strikethrough = True
            Cells(myRow, myCol).Interior.ColorIndex = 15
        End If
       
        If Cell.Value = “n” Then
            Cells(myRow, myCol).Font.Strikethrough = False
        End If
    Next
End Sub
Happy coding 🙂

[Source MVPS site]