Help with a script for IF with Cell Border LineWidth less than X, then put Y

Cendrinne · #1 09-07-2022, 12:09 AM

Hello Pros,
Again, I'm stuck on this script I found on the web, I've just modified it to my needs.
It Works, BUT, it has No Dim, so being on Option Explicit, doesn't work.
If I remove ''Option Explicit'', it's slow for a few tables on a couple of pages as a test, yet it checks every cell, so normal it's slow, but it does Work.

I'm trying to fix the macro to have Option Explicit, along with Dim, etc. Reason, because all my scripts are for Option Explicit.
Whatever I try, it either does Nothing, or I get an error message.

I hate to give up, so PLEASE, could you help me.

Cause on tables in a word document, people don't open the screen big enough, and they assume, a 0.75 pt, could be a 0.50 pt, so on same line or row, I see both borders.

It's very long when you have to verify over 50 tables in a document to fix them.

On that same web page, someone created a macro with Dim, but it add's borders from the enum (-6 to -1), from these data: WdBorderType Enum (Microsoft.Office.Interop.Word) | Microsoft Docs

Code:

Sub FixCellBorders()
'Found on Web https://wordribbon.tips.net/T013081_Setting_a_Default_Table_Border_Width
   On Error Resume Next
    ' Work through all tables in document
    For Each objTable In ActiveDocument.Tables
        ' Work through all cells in each table
        For Each objCell In objTable.Range.Cells
            ' Work through all borders in each cell
            For Each objBorder In objCell.Borders
                ' Check if line weight is less than 1.00 pt
                If objBorder.LineWidth = wdLineWidth050pt _
                  Or objBorder.LineWidth = wdLineWidth075pt Then
                    ' too thin, change it
                    objBorder.LineWidth = wdLineWidth100pt
                End If
            Next objBorder
        Next objCell
    Next objTable
End Sub

I'm very clueless, so any insights would be so appreciative

Cendrinne

BrianHoard · #2 09-07-2022, 05:41 AM

Something to try. What if instead of checking each cell for it's border size, you just set the entire table to 1pt? Would that speed things up?

As far as the Option Explicit issue, it simply means you have to declare each variable before you can use it. For example,
Dim objTable As Table
Dim someVar As Integer
Dim someVar As String
Dim someVar As Variant
Dim someVar As Object
etc

Cendrinne · #3 09-08-2022, 08:41 AM

I am hoping to avoid the 1pt to the entire table. The tables I need to fix are normally empty on the sides, and the borders are normally for sub total row or Total rows.

And since some people update tables from version to versions, they don't always put the word screen as big, so it's hard to detect from border size 0.50 pt vs 0.75, so I might see both on the same row.

I found that macro that I wrote on my post, however, it's without Explicit Option, and since almost if not all are with explicit option, I've put it to my Normal module. All my macros are all categorize by type of module within the Normal modules. I have one for Tbl1, other is for TblAll, Tbl1_opt, TblAll_opt, others are for graphic, some are for batch, I have test modules, I have Notes, and I hve InputBox, etc. wow, that is alot all within the same Normal.dotm. I was hoping to organize them well, to not need any more modules, to easily find the one I need.

I'll keep at it but for now, I'm glad I have one for all tables. Could take a long time if it's for 50+ Tables.

Have a good one,

Cendrinne

Italophile · #4 09-08-2022, 09:23 AM

Cendrinne

Only put code modules in Normal if you are prepared to lose them. From time to time Word will decide that your Normal template has become corrupt and replace it with a new one, which won't contain any code modules. At the very least you need to back-up your modules by exporting them to a safe location.

BrianHoard · #5 09-09-2022, 09:10 AM

I second what Italophile said. I'm new to VBA, but that was some advice I read early on. Especially if you share your modules to others, as you want to leave normal.dotm alone as users may have recorded macros that are stored there.

I just made my own .dotm file, and store it in %appdata%\Microsoft\Word\STARTUP so it loads when you start Word.

If you want to upload a Word doc with your tables that have the problem, as well as what it looks like after it's fixed, I can look at it and see if I can get it to run faster.

macropod · #6 09-09-2022, 03:11 PM

Quote:

Originally Posted by Italophile

From time to time Word will decide that your Normal template has become corrupt and replace it with a new one, which won't contain any code modules.

I've been using Word for 25+ years and have not had this happen even once. The only time I have ever seen macros wiped from the Normal template is in a corporate environment where the IT department decided to replace everyone's Normal template with a different one; the same cowboys that would delete any other automatically-loaded templates they find...

macropod · #7 09-09-2022, 03:44 PM

Try:

Code:

Sub FixTableBorders()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim Tbl As Table, Bdr As Border
With ActiveDocument
  For Each Tbl In .Tables
    With Tbl
      For Each Bdr In .Borders
        With Bdr
          If .LineStyle = wdLineStyleSingle Then
            Select Case .LineWidth
              'Case 2 To 6: .LineWidth = 8
              Case wdLineWidth025pt, wdLineWidth050pt, wdLineWidth075pt: .LineWidth = wdLineWidth100pt
            End Select
          End If
        End With
      Next
    End With
  Next
End With
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Cendrinne · #8 09-09-2022, 10:41 PM

Quote:

Originally Posted by macropod

I've been using Word for 25+ years and have not had this happen even once. The only time I have ever seen macros wiped from the Normal template is in a corporate environment where the IT department decided to replace everyone's Normal template with a different one; the same cowboys that would delete any other automatically-loaded templates they find...

Hi MacroPod,
Yeah, same at the company I work for. We are all on a network, and I do back all my Normal.dotm every time I think of it, since some macro could take for ever and screw things for me, and I have to force it to close or it closes on its own, 95% of the time, I was able to recoperate my same old Normal, with all the newly created Tabs on the Ribbon, and all my macros or icons created, remained.

The only time, I lost some macros are the ones I've just loaded it up.

So thanks for confirming that to all of us

Cendrinne

Cendrinne · #9 09-10-2022, 12:14 AM

Quote:

Originally Posted by macropod

Try:

Code:

Sub FixTableBorders()
...
End Sub

Hi there,
I've tried to see if I could fix it, but I can't. I've tried also, is to remove ' before the Case 2 To 6: LineWidth = 8, so with the ' or without the ', It doesn't do anything.

I've tested on a table that had some 075pt, and modified on the same row, by changing 075 for 050pt just on 1 cell, on a few rows.

I feel it's closer to the solution. I'll try different things, but if you do, please let me know. Thank you soooooo much for trying to help me. I'm deeply appreciative.

Cendrinne

Italophile · #10 09-10-2022, 10:48 AM

Quote:

Originally Posted by macropod

I've been using Word for 25+ years and have not had this happen even once. The only time I have ever seen macros wiped from the Normal template is in a corporate environment where the IT department decided to replace everyone's Normal template with a different one; the same cowboys that would delete any other automatically-loaded templates they find...

I’ve been using Word for about the same length of time and have had it happen several times, and not in a corporate environment. The last time was only a couple months ago.

Cendrinne · #11 09-10-2022, 12:53 PM

Quote:

Originally Posted by Italophile

I’ve been using Word for about the same length of time and have had it happen several times, and not in a corporate environment. The last time was only a couple months ago.

I say I back-up my stuff, but at times I forget to do it because it doesn't happen to me all that ofen, but just like you, it could happen to me. It takes one moment then you are frustrated at yourself, you didn't do it, and with all these trial and error so far, I haven't done it in the past few days. I think I'll do it now just in case.

Cendrinne

macropod · #12 09-10-2022, 02:52 PM

Quote:

Originally Posted by Cendrinne

Hi there,
I've tried to see if I could fix it, but I can't. I've tried also, is to remove ' before the Case 2 To 6: LineWidth = 8, so with the ' or without the ', It doesn't do anything.

The line:
Case 2 To 6: LineWidth = 8
does exactly the same thing as:
Case wdLineWidth025pt, wdLineWidth050pt, wdLineWidth075pt: .LineWidth = wdLineWidth100pt
Try:

Code:

Sub FixTableBorders()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim Tbl As Table, Bdr As Border, Cll As Cell
With ActiveDocument
  For Each Tbl In .Tables
    For Each Cll In Tbl.Range.Cells
      For Each Bdr In Cll.Borders
        With Bdr
          If .LineStyle = wdLineStyleSingle Then
            Select Case .LineWidth
              Case wdLineWidth025pt, wdLineWidth050pt, wdLineWidth075pt: .LineWidth = wdLineWidth100pt
            End Select
          End If
        End With
      Next
    Next
  Next
End With
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Cendrinne · #13 09-10-2022, 03:29 PM

Quote:

Originally Posted by macropod

The line:
Case 2 To 6: LineWidth = 8
does exactly the same thing as:
Case wdLineWidth025pt, wdLineWidth050pt, wdLineWidth075pt: .LineWidth = wdLineWidth100pt

You are so right. It's redundant.

OK let me try the above modified script. I'm thinking maybe it's because my operating system is in French, which I have not control on that. Believe me, I've tried many time to request an English operating system.

Thank god I truly LOVE my job.

Ok enough bla bla, let me test this. Crossing fingers.

Cendrinne · #14 09-10-2022, 03:39 PM

I was crossing my fingers, hoping for the best. ==> WOW, Thank you!

I'm beyond words. I have tears in my eyes, so grateful. Was spending so much time fixing other people's documents when you have more than 25, it gets sooooo long.

I'm so grateful to you. I will not forget you and your forum.

I owe you. If there is a paying format to request help, please please let me know. I wish I could send you a private message, which I can't to you. But I feel I should pay something to you or have a subscription of some sort, I would pay (as long it's reasonable).

p.s. I've look through the forum for any option to give a donation. I found it, but it's stuck on $5.00 so regardless what amount I chose, which I would give you more then $20, it goes to $5.00. So I'm just letting you know.

macropod · #15 09-11-2022, 03:13 PM

I don't accept payment for my forum contributions. Any donations you make go to the forum owner.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
cell border lineweights	bkcell	Excel	0	08-20-2017 09:55 AM
Can I type over a cell with a border?	Juju37	Excel	0	01-20-2015 07:40 AM
Need to remove a line that isn't a cell border	msbytes	Word	4	08-15-2011 09:21 AM
Cell border	markg2	Excel	10	04-12-2011 05:05 PM
Table cell border	markg2	Word Tables	2	01-15-2010 05:22 PM