|
|
Thread Tools | Display Modes |
#1
|
||||
|
||||
VBA Word - Format ALL Tables - Apply Specific Formatting to Sets of Columns – Font, Border & Width
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 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 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 |
#2
|
||||
|
||||
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.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#3
|
||||
|
||||
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 |
#4
|
||||
|
||||
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?
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#5
|
||||
|
||||
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 |
#6
|
||||
|
||||
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
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#7
|
||||
|
||||
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 |
#8
|
||||
|
||||
There isn't one - "TblGhost" is the name of a Style the macro creates.
Quote:
.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.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#9
|
||||
|
||||
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 If I can solve this last problem - it would make my week! thank you J |
#10
|
||||
|
||||
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
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#11
|
||||
|
||||
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 ! |
Tags |
table, vba |
|
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 |