Solved: help with table border formatting

[qr_doc_con]

Hi everyone,

As this is my first post please be gentle.

I've recently started to look at setting up a macro to format tables in a word document.

I've worked out how to check if the border on a selected cell is either on or off and if on then change to a single line as shown in code below.

what i want to do if possible is for it to do this check on all the cells in a table and chance the borders if required. Also to be able to do it to multiple tables in a document.

If any suggestion would be great.
Here is the code.
If Selection.Borders(wdBorderTop).LineStyle <> wdLineStyleNone Then
Selection.Borders(wdBorderTop).LineStyle = wdLineStyleSingle
End If
If Selection.Borders(wdBorderLeft).LineStyle <> wdLineStyleNone Then
Selection.Borders(wdBorderLeft).LineStyle = wdLineStyleSingle
End If
If Selection.Borders(wdBorderBottom).LineStyle <> wdLineStyleNone Then
Selection.Borders(wdBorderBottom).LineStyle = wdLineStyleSingle
End If
If Selection.Borders(wdBorderRight).LineStyle <> wdLineStyleNone Then
Selection.Borders(wdBorderRight).LineStyle = wdLineStyleSingle
End If
End Sub

[Kafrin]

You could try doing something like this. For every table in the active document, it works through each row, finds each individual cell and then checks each border of the cell.


Public Sub SetSingleBorders()
Dim T As Table, R As Row, C As Cell, B As Border

For Each T In ActiveDocument.Tables
For Each R In T.Rows
For Each C In R.Cells
For Each B In C.Borders
If B.LineStyle <> wdLineStyleNone Then B.LineStyle = wdLineStyleSingle
Next B
Next C
Next R
Next T
End Sub

I did try this just looping through each border of each table, but because it works on a more 'global' scale it didn't work properly if you had a line that was only solid in some rows and not in others.

[Frosty]

Kafrin's is good code. It will fail, however, if you have any merged cells. Better to skip the each row in t.Rows bit, and simply do For Each C in T.Cells.

[Frosty]

Sorry, I should have double-checked my suggestion.

For Each C in T.Range.Cells
Next

Is what I should have suggested.

Both ways have you checking the borders of each cell in the document, but if you check the cells off of each Row, it will error out (or fail to get some of the cells, depending on the version of Word, I believe) if you have any vertically merged cells. Ditto for each column type thing with horizontally merged cells.

Basically, the .Rows and .Columns of tables can be unreliable... even if the table *looks* like it doesn't have merged cells.

[qr_doc_con]

thank you this has help big time.

Just one small question what do I need to do to make the size of the line a set size.

[Frosty]

It will be useful to record a macro applying the border you want to apply, but this is a sample... instead of the above line:


If B.LineStyle <> wdLineStyleNone Then B.LineStyle = wdLineStyleSingle
try this:

With B'(border)
'only deal with already bordered cells
if .LineStyle <> wdLineStyleNone Then
'make sure the border is single
.LineStyle = wdLineStyleSingle
'and this thick
.LineWidth = wdLineWidth100pt
End If
End With

[Frosty]

Just summing up the whole code (using Kafrin's and my suggested change to eliminate the use of the row, as well as some additional commenting)

Public Sub SetSingleBorders()
Dim T As Table, C As Cell, B As Border

'cycle through each table in the active document
For Each T In ActiveDocument.Tables
'and each cell in the table
For Each C In T.Range.Cells
'and each border type for this cell's borders
For Each B In C.Borders
'only deal with already bordered cells
If B.LineStyle <> wdLineStyleNone Then
'make sure the border is single
B.LineStyle = wdLineStyleSingle
'and this thick
B.LineWidth = wdLineWidth100pt
End If
Next B
Next C
Next T
End Sub

[qr_doc_con]

Thanks Frosty and Kafrin :bow:

this does everything that i was looking for.

After going through you suggestion I have expanded it to do a few more thing to the table.

here is the code that i did.


Public Sub format_tables()
Dim T As Table, C As Cell, B As Border
Dim shadRGB, borderRGB
'set shading colour variable based on Red/Green/Blue colour code
shadRGB = RGB(238, 238, 238)
'set border colour variable based on Red/Green/Blue colour code
borderRGB = RGB(180, 180, 280)'cycle through each table in the active documentFor Each T In ActiveDocument.Tables

'and each cell in the table
For Each C In T.Range.Cells
'and each border type for this cell's borders
For Each B In C.Borders
'only deal with already bordered cells
If B.LineStyle <> wdLineStyleNone Then
'make sure the border is single
B.LineStyle = wdLineStyleSingle
'and this thick
B.LineWidth = wdLineWidth100pt
'and this border colour based on this variable
B.Color = borderRGB
End If
'only deal with shaded cells and change to colour based on variable
If C.Shading.BackgroundPatternColor <> wdColorAutomatic Then C.Shading.BackgroundPatternColor = shadRGB
Next B
Next C
Next T
End Sub

Once again thank you

[Frosty]

Glad it was helpful! Couple of quick notes:

If you could edit your post to use the VBA tags, that'd be fantastic. You can use the VBA button or type them manually. Makes your actual code much more readable.

1. it's very often going to pay dividends to type your variables, rather than allow VBA to do it for you... so:
Dim shadRGB As Long, borderRBG As Long

2. Although this is more preference than absolute... single line If statements do not really speed up your code in VBA, and are often more difficult to troubleshoot/step-through. I recognize this is a biased opinion, so ignore it as you like :)

3. This is not an opinion: You are changing your cell's shading while within your for each border loop... that means you're doing the same thing multiple times. You should move that code out of the borders loop and just do it once within the cells loop.

In general, you should *always* step through your code at least once... not just run the routine and see if it the end result is what you want. Not only will code efficiencies occur to you, but you will continue to learn by stepping through the code. Especially when the bulk of it is provided by others.

Hope that helps, and good luck!