VBA Word - Format ALL Tables - Apply Specific Formatting to Sets of Columns

jc491 · #1 11-03-2015, 08:38 AM

Hi,
I hope everyone is well.
I am looking for some advice from the VBA experts.
I apologize if this has a complete solution elsewhere I have not been able to find it.

I have word documents that will contain a number of tables.
Each table may contain up to 15 columns.
If a table contains less columns I can delete a column from the final code.

I would like to format them in a specific way

Columns 1,2,4,6,7,9,10,12
· Font Size 2
· Font Color White
· Border Color White
· Column Width = 20px

Columns 2,5,8,11
· Font Size 12
· Font Color - Black
· Border Color Dark Grey
· Column Width = 150px

I know that I could run 2 separate macros – I would end up with a long page of code for each column.
However, I would like to learn how to do it in one - as I have a lot of documents to convert.
I have tried to fiddle about and searched online documentation but am not closer to a solution.

My attempt at putting together the concept,

Column Sizes

Code:

      For Each Table In ActiveDocument.Tables
      On Error Resume Next
      Table.Columns(1).Width = 20
      Table.Columns(2).Width = 20
  Table.Columns(4).Width = 20
  Table.Columns(6).Width = 20
                                etc
      On Error GoTo 0
    Next
  End Sub

Formatting the Columns

Code:

  Dim Table As Table, Cell As Cell
  Dim col as column
  For Each Table In ActiveDocument.Tables
  For Each Cell In Table.Range.Cells
   For Each Cell In .Columns(1).Cells
  Cell.Width = 20px
  Cell.Range.Font.Name = "Arial"
  Cell.Range.Font.Size = 2
  Cell.Range.Font.ColorIndex = white
   Cell.border.color =white
       
  Next Cell or column
      Next Table
      End Sub

I have managed to confuse myself

- and am making errors. Also they are separate macros - I'm not sure how to combine them.

I would appreciate some help.

To Recap.
I need to Format all the Tables in a document, that have various number of columns.

Columns 1,2,4,6,7,9,10,12

· Font Size 2
· Font Color White
· Border Color White
· Column Width = 20px
These columns have ids and codes in them hence trying to give them a hidden appearance.

Columns 2,5,8,11 - Normal Columns
· Font Size 12
· Font Color - Black
· Border Color Dark Grey
· Column Width = 150px

I would be really grateful if an expert can guide me.

Thanking you in advance for your kind help and time.

J

macropod · #2 11-03-2015, 02:40 PM

You say there are up to 15 columns, but your description only accounts for 12 of them. Column 3 isn't accounted for either, and you given conflicting specs for column 2. What are the specs for the unaccounted-for columns?

As for the fonts, these should be managed by Styles, not by brute-force overriding whatever Styles are already in use. Using Styles also simplifies font changes later on.

jc491 · #3 11-03-2015, 03:00 PM

Hi Paul,

thanks for your reply.

Yes even I'm getting confused

Columns

1,3,4,6,7,9,10,12,13,15
Font Size 2
· Font Color White
· Border Color White
· Column Width = 20px

Columns - Normal Text Columns

2,5,8,11,14

Font Size 12
· Font Color - Black
· Border Color Dark Grey
· Column Width = 150px

There may be a few tables with up to 15 columns but majority will be around 12 columns or less

The reason they need to be formatted - as you can imagine the page cant hold that many columns - not enough space for comfortable editing.
So I need to hide some Columns to make space for easier editing reading etc.

I am unable to understand how to combine 2 macros to achieve my goal

J

macropod · #4 11-03-2015, 03:05 PM

How do you propose that columns will have both a White border and a Dark Grey border (e.g. the border between columns 1 & 2)? What about top & bottom borders? What applies to the table's outside borders?

jc491 · #5 11-03-2015, 03:25 PM

Hi Paul,

oops that's a good question - seems more complicated than I anticipated now.

I was trying to keep the normal columns with their borders intact

Columns - Normal Text Columns

2,5,8,11,14

Columns - The Ones I want to give the appearance of hidden - no borders at all or white to make them seem invisible.

1,3,4,6,7,9,10,12,13,15

Here is a pic of what I am trying to achieve. The hidden columns will be tiny so the main columns will be closer together in the end.

http://tinypic.com/r/faa2p4/9

In excel you can simply hide columns - word is unforgiving - no such function.
Word also has a bad habit of crashing when you try to change column sizes, hence needing some sort of solution that can help me hide some of the columns.

J

macropod · #6 11-03-2015, 04:32 PM

If you're trying to simulate hidden columns, a better way than changing the font size & colour would be to hide the content and to minimize the relevant column widths. For example:

Code:

Sub FormatTables()
Application.ScreenUpdating = False
Dim Stl As Style, bStlA As Boolean, bStlB As Boolean, Tbl As Table, i As Long, j As Long, ArrCols
bStlA = False: bStlB = False
ArrCols = Array("2", "5", "8", "11")
With ActiveDocument
  For Each Stl In .Styles
    If Stl.NameLocal = "TblGhost" Then bStlA = True
    If Stl.NameLocal = "TblTxt" Then bStlB = True
  Next
  If bStlA = False Then Call AddStyle("TblGhost", True)
  If bStlB = False Then Call AddStyle("TblTxt", False)
  For Each Tbl In .Tables
    With Tbl
      .AllowAutoFit = False
      .LeftPadding = 0
      .RightPadding = 0
      .Range.Style = "TblGhost"
      With .Columns
        .Borders.Enable = False
        .PreferredWidthType = wdPreferredWidthPoints
        .PreferredWidth = 0
      End With
      For i = 0 To UBound(ArrCols)
        If ArrCols(i) > .Columns.Count Then Exit For
        With .Columns(ArrCols(i))
          .PreferredWidth = 150
          .Borders.Enable = True
          For j = 1 To .Cells.Count
            .Cells(j).Range.Style = "TblTxt"
          Next
        End With
      Next
    End With
  Next
End With
Application.ScreenUpdating = True
End Sub
 
Sub AddStyle(strNm As String, bHidden)
Dim Sty As Style
With ActiveDocument
  Set Sty = .Styles.Add(Name:=strNm, Type:=wdStyleTypeParagraph)
  With Sty
    With .Font
      .Hidden = bHidden
      .Size = 12
    End With
  End With
End With
End Sub

jc491 · #7 11-03-2015, 05:36 PM

Hi Paul,

I can't thank you enough!

I have never heard of the ghost feature before.

Some serious coding here,although it's way too advanced for me at the moment to understand, I can appreciate the complexity.

I have been trying to solve this problem for the past week unsuccessfully, so its really generous of you to solve this problem.

The code looks smashing.

In case I need to check the hidden columns I change the array numbers and the hidden set becomes visible - I tried that and so far it seems OK.

I hope you don't mind me asking - am I able to change the font size in the main Visible columns? Its a size 12 at the moment - If I can make the font bigger it would be easy on the eyes. If not no worries. I can do it manually.

For hidden text - the font size you have coded below - i can easily change that - thank you.

With Sty
With .Font

.Hidden = bHidden
.Size = 12

Again I thank you very much for your generous time and coding skills in helping me!

J

macropod · #8 11-03-2015, 05:44 PM

Quote:

Originally Posted by jc491

I have never heard of the ghost feature before.

There isn't one - "TblGhost" is the name of a Style the macro creates.

Quote:

Originally Posted by jc491

I hope you don't mind me asking - am I able to change the font size in the main Visible columns? Its a size 12 at the moment - If I can make the font bigger it would be easy on the eyes.

You can make the font whatever size you want, just by changing the 12 in:
.Size = 12

For an existing document that's already had the macro run on it, simply modify the font size for the "TblTxt" Style. That's the power of using Styles - change the Style definition and everything based on that Style updates.

jc491 · #9 11-04-2015, 07:39 AM

Hi Paul,

This VBA Macro is absolutely smashing! I have been able to hide many columns that were causing me a lot of stress. Out of sight out of mind.

On a side note I hate to have to bother you again - but

rather than open another thread as is etiquette, I hope you don't mind to look at my newbie code.

There are a set of documents that don't need the columns hidden - as they have less data.

I simply need to change the font size of some columns in each table.
May be Columns 1 and 3.

This is what I have:

Code:

Sub ChangeColumFontSize()
'Change the Font Size in Selected columns in all tables 
    
Dim aTable As Table, aCell As Cell
Dim acolumn As Column

For Each aTable In ActiveDocument.Tables
    
For Each aCell In aTable.Range.Column(1)
aCell.Range.Font.Size = 14

For Each Cell In aTable.Range.Column(3)
aCell.Range.Font.Size = 14

Next aCell 'or Column
    Next aTable
    End Sub

its not working

If I can solve this last problem - it would make my week!

thank you
J

macropod · #10 11-04-2015, 03:12 PM

Try:

Code:

Sub ChangeColumnFontSize()
'Change the Font Size in Selected columns in all tables
Application.ScreenUpdating = False
Dim Tbl As Table, i As Long
For Each Tbl In ActiveDocument.Tables
  With Tbl.Columns(1)
    For i = 1 To .Cells.Count
      .Cells(i).Range.Font.Size = 14
    Next
  End With
  With Tbl.Columns(3)
    For i = 1 To .Cells.Count
      .Cells(i).Range.Font.Size = 14
    Next
  End With
Next
Application.ScreenUpdating = True
End Sub

jc491 · #11 11-04-2015, 04:02 PM

Hi Paul,

it worked - I am really happy!

thanking you again for your time and generous help.
These macros will really help me deal with all the table issues in my documents and minimize word crashing - I know it's hard to believe that word can be so temperamental with tables and columns - especially when you try to manually adjust - with large tables - its a nightmare, having to restart the document - losing changes not saved.

Programmatically changing columns with VBA is the best solution - but then I fall flat with my VBA skills. I am learning but some of the stuff -I can't get my head around - and end up confused.

I am really grateful for your help.

You're the man!

Have an awesome awesome week!

J

Please mark as double solved - 2 problems !

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to apply formatting(color & shading) to specific letters in whole doc. (arabic)	loopseye	Word	3	08-05-2013 10:49 PM
How to set a custom border width in Word 2011 for Mac	kchappell	Word	0	11-10-2012 11:59 AM
Apply font color programatically using VBA Word	divakarganta	Word VBA	3	08-08-2012 08:05 PM
Formatting multiple tables and columns efficiently?	table_column	Word Tables	1	07-02-2012 05:55 PM