Solved

Macro to Make Table Borders Invisible Depending on Style Used - Inner Border Persists

Posted on 2008-10-29
14
429 Views
Last Modified: 2012-06-22
Hi

I'm having trouble getting rid of table borders, the following code was for the most part recorded. It looks for Style Heading 2 and if that style exists in the table (anywhere in the table) removes the borders. Only problems are:

1. Vertical borders are not hidden
2. I also want to perform the same thing for tables containing the style "TableHeading" where the 1st word in the 1st cell is "Environment".

I could write a separate macro, but would be nice to kill 2 birds with 1 stone.

Top Code seems to work to some degree, bottom code a dismal failure...

Please help

Kind regards

Steve
Sub A_FindStyleClearTable() 'Top code kinda works... 
 
Dim tTable As Table
 
For Each tTable In ActiveDocument.Tables
 
    Selection.Find.ClearFormatting
    Selection.Find.Style = ActiveDocument.Styles("Heading 2")
    With Selection.Find
        .Text = ""
        .Replacement.Text = ""
        .Forward = True
        .Wrap = wdFindContinue
        .Format = True
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    Selection.Find.Execute
    
    
    With Selection.Tables(1)
        .Borders(wdBorderLeft).LineStyle = wdLineStyleNone
        .Borders(wdBorderRight).LineStyle = wdLineStyleNone
        .Borders(wdBorderTop).LineStyle = wdLineStyleNone
        .Borders(wdBorderBottom).LineStyle = wdLineStyleNone
        .Borders(wdBorderHorizontal).LineStyle = wdLineStyleNone
        .Borders(wdBorderDiagonalDown).LineStyle = wdLineStyleNone
        .Borders(wdBorderDiagonalUp).LineStyle = wdLineStyleNone
        .Borders.Shadow = False
    End With
Next tTable
End Sub
 
 
 
Sub RemBorders()  ' Bottom code, a dismal failure
 
Dim tTable As Table
Dim rRange As Range
   
  
For Each tTable In ActiveDocument.Tables
Set rRange = bTable
 
            If Selection.Range.Find.Execute("Task Completed") Then
            If rRange.Find.Execute = ActiveDocument.Styles("Heading 2") Then
                MsgBox "H2"
                GoTo CleanUpTable
            Else
            If Selection.Find.Style = ActiveDocument.Styles("Table Sub Heading") Then
                        If rRange.Find.Execute("Responsibility:") Then
                            MsgBox "Res"
                            GoTo CleanUpTable
                                Else
                            If rRange.Find.Execute("Environmental:") Then
                                MsgBox "Env"
                                GoTo CleanUpTable
                                    Else
                                If rRange.Find.Execute("References:") Then
                                    MsgBox "Refs"
                                    GoTo CleanUpTable
                                        Else
                                    If rRange.Find.Execute("Tools and Equipment:") Then
                                        MsgBox "Tool"
                                        GoTo CleanUpTable
 
                                    End If
                                End If
                            End If
                        End If
                   End If
            End If
GoTo SkipThis
CleanUpTable:
            
    Selection.ParagraphFormat.Borders.Shadow = False
 
    With Selection.Tables(1)
        .Borders(wdBorderLeft).LineStyle = wdLineStyleNone
        .Borders(wdBorderRight).LineStyle = wdLineStyleNone
        .Borders(wdBorderTop).LineStyle = wdLineStyleNone
        .Borders(wdBorderBottom).LineStyle = wdLineStyleNone
        .Borders(wdBorderHorizontal).LineStyle = wdLineStyleNone
        .Borders(wdBorderDiagonalDown).LineStyle = wdLineStyleNone
        .Borders(wdBorderDiagonalUp).LineStyle = wdLineStyleNone
        .Borders.Shadow = False
    End With
    With Options
        .DefaultBorderLineStyle = wdLineStyleSingle
        .DefaultBorderLineWidth = wdLineWidth050pt
        .DefaultBorderColor = wdColorAutomatic
    End With
 
SkipThis:
        
    Next bTable
 
End Sub

Open in new window

0
Comment
Question by:DrTribos
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
14 Comments
 
LVL 17

Expert Comment

by:aflockhart
ID: 22829471
To remove vertical lines you need :

  .Borders(wdBorderVertical).LineStyle = wdLineStyleNone


That's the easy bit ...
0
 
LVL 17

Expert Comment

by:aflockhart
ID: 22829504
You also ( in the first macro) need an extra line at the start of the loop:

For Each tTable In ActiveDocument.Tables
 tTable.Select
   ....

Your code, at the moment, repeatedly checks the first table that it finds, because once you it has been through the loop once, there is a paragraph of text selected, and the selection.find command only searches within the currently selected area.

0
 
LVL 17

Expert Comment

by:aflockhart
ID: 22829597
Try this for the second one.  It loops round the tables in the same way, but checks the first cell to see if it begins with the word "environment" before doing anything.
Sub B_FindStyleAndTExtClearTable()
Dim tTable As Table
 
For Each tTable In ActiveDocument.Tables
 tTable.Select
    Selection.Find.ClearFormatting
    Selection.Find.Style = ActiveDocument.Styles("TableHeading")
    With Selection.Find
        .Text = ""
        .Replacement.Text = ""
        .Forward = True
        .Wrap = wdFindContinue
        .Format = True
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    Selection.Find.Execute
    
    'we are in a table that contains a Heading Text cell.
    'does the first cell contain "Environment"
 
If InStr(1, Selection.Tables(1).Cell(1, 1), "environment", vbTextCompare) =1 Then
 
    
    
    With Selection.Tables(1)
        .Borders(wdBorderLeft).LineStyle = wdLineStyleNone
        .Borders(wdBorderRight).LineStyle = wdLineStyleNone
        .Borders(wdBorderTop).LineStyle = wdLineStyleNone
        .Borders(wdBorderBottom).LineStyle = wdLineStyleNone
        .Borders(wdBorderHorizontal).LineStyle = wdLineStyleNone
        .Borders(wdBorderVertical).LineStyle = wdLineStyleNone
        .Borders(wdBorderDiagonalDown).LineStyle = wdLineStyleNone
        .Borders(wdBorderDiagonalUp).LineStyle = wdLineStyleNone
        .Borders.Shadow = False
    End With
End If
    
Next tTable
End Sub

Open in new window

0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 15

Author Comment

by:DrTribos
ID: 22829914
Hi aflockhart

Thanks - I tried the mod to the 1st macro (insert tTable.Select) and now have run time errror #5941 member of collection does not exits - debug highlights line starting "With" per below.
_______________________________
End With
    Selection.Find.Execute
     
    With Selection.Tables(1)                                                                  <--Highlighted
        .Borders(wdBorderLeft).LineStyle = wdLineStyleNone
______________________________

0
 
LVL 15

Author Comment

by:DrTribos
ID: 22829942
Regarding macro 2 - your change seems to work well, now the challenge (well I still find it challenging) is to search for the other words (cases?) in my original macro 2.

So, in addition to finding Environment, I want the macro to be expandable to find other words (i.e. Responsibility, References, Tools... etc.) this is what I was trying to achieve with the use of all those If statements... any suggestions welcome.

Kind regards

Steve
0
 
LVL 15

Author Comment

by:DrTribos
ID: 22829959
I guess - really - I have a few ideas how to achieve the above (thanks to your input) but would like neat solution to deal with the other situations.... :-)
0
 
LVL 17

Expert Comment

by:aflockhart
ID: 22830298
Error 5941  : Probably being caused by the macro finding an instance of the "Heading 2" style that is not contained in any table, so when it tries to find the containing table, it fails. We can just trap and ignore this error.

Revised version with some error checking.



Sub A_FindStyleClearTable() 'Top code kinda works...
 
Dim tTable As Table
 
For Each tTable In ActiveDocument.Tables
 tTable.Select
    Selection.Find.ClearFormatting
    Selection.Find.Style = ActiveDocument.Styles("Heading 2")
    With Selection.Find
        .Text = ""
        .Replacement.Text = ""
        .Forward = True
        .Wrap = wdFindContinue
        .Format = True
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    Selection.Find.Execute
    
    'we are now on a heading2 paragraph but may not be in the orioginal table
    'are we actually still in a table ? if not, we'll get an error whihc we can ignore, and skip to the next part
    
      On Error GoTo notInTable:
    
    
    With Selection.Tables(1)
        .Borders(wdBorderLeft).LineStyle = wdLineStyleNone
        .Borders(wdBorderRight).LineStyle = wdLineStyleNone
        .Borders(wdBorderTop).LineStyle = wdLineStyleNone
        .Borders(wdBorderBottom).LineStyle = wdLineStyleNone
        .Borders(wdBorderHorizontal).LineStyle = wdLineStyleNone
        .Borders(wdBorderVertical).LineStyle = wdLineStyleNone
        .Borders(wdBorderDiagonalDown).LineStyle = wdLineStyleNone
        .Borders(wdBorderDiagonalUp).LineStyle = wdLineStyleNone
        .Borders.Shadow = False
    End With
    
    
notInTable:
 'restore error checking
 On Error GoTo 0
    
Next tTable
End Sub

Open in new window

0
 
LVL 17

Accepted Solution

by:
aflockhart earned 500 total points
ID: 22830354
You may need similar error checking in the second macro, although you are less likely to have the "TableHeader" style used outside a table.

On dealing with other words to search for:

You could hard code multiple words into the macro with something like:

If InStr(1, Selection.Tables(1).Cell(1, 1), "environment", vbTextCompare) =1  OR InStr(1, Selection.Tables(1).Cell(1, 1), "responsibility", vbTextCompare) =1 OR ...    Then ...


or ask for input at the start of the macro each time you run it , and look for whatever the user types:

WordToSearchFor = InputBox("ENter word to search for")
...
...
If InStr(1, Selection.Tables(1).Cell(1, 1), WordToSearchFor, vbTextCompare) = 1 Then ...


 
0
 
LVL 15

Author Comment

by:DrTribos
ID: 22830355
Hi - I think you're right about the 'Heading 2' - I substituted Heading 2 into the macro that worked with TableHeading and it fell over - will try the error trapping. Thanks (I was going stir crazy)
0
 
LVL 15

Author Comment

by:DrTribos
ID: 22830369
I like the 'OR' option - did not know I could do that...  
0
 
LVL 15

Author Comment

by:DrTribos
ID: 22830514
Seems to have a problem here too if I try to replace TableHeading with Heading 2

If InStr(1, Selection.Tables(1).Cell(1, 1), "refer", vbTextCompare) = 1 Then

Not sure if it is because Heading 2 has numbering... get message Requested Member of the Collection does not exist.

My battery is about to die (both the PC and the one keeping me awake) will sleep on it and try more in the morn - thanks heaps for your help.

Cheers

Steve
0
 
LVL 9

Expert Comment

by:jkunrein
ID: 22830529
The WithinTable property is also helpful for conditions.  You can avert errors by having an If statement using this snippet as a model.

Incidentally, unless I miss my guess, the second macro may have failed also in part because I see bTable referenced but not defined.  Was that supposed to be tTable?  Also, when you define variables, keep the type in mind.  If tTable is a table, then you cannot set rRange to that.  But you can set rRange equal to tTable.Range.  

Just some additional thoughts.  Good luck.
Public Sub Test()
    Dim rRange As Range
    Set rRange = ActiveDocument.Paragraphs(1).Range
    
    If rRange.Information(wdWithInTable) = True Then
        MsgBox "In table"
    Else
        MsgBox "Not in table"
    End If
 
End Sub

Open in new window

0
 
LVL 15

Author Closing Comment

by:DrTribos
ID: 31511090
Thanks - got it all working now, although did not end up looking for the words, only the style.

Cheers

Steve
0
 
LVL 15

Author Comment

by:DrTribos
ID: 22838703
Thanks aflockhart - works :-)

Hi jkunrein Thanks for your input, I have another question if you can help...  hope the link works

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Word/Q_23839091.html 

cheers

Steve
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

690 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question