Function to Check Borders

M

Marco

I want to create a function that will check a cell for a bottom border and
return a 1 if True or 0 if False.

My function will be in cell M15, checking cell A14 for the border.

I have the following, which is returning a #NAME? error:

If Range.Select.Borders(xlEdgeBottom).Weight = xlThin _
Then ActiveCell.FormulaR1C1 = "1" Else ActiveCell.FomulaR1C1 = "0"

Thank you in advance for your help

Marco
 
J

JLGWhiz

If Selection.Borders(xlEdgeBottom).Weight = xlThin _
Then ActiveCell.FormulaR1C1 = "1" Else ActiveCell.FomulaR1C1 = "0"
 
D

Dave Peterson

You can use a UDF like:
Option Explicit
Function ThinBottomBorder(rng As Range) As Long
If rng.Cells(1).Borders(xlEdgeBottom).Weight = xlThin Then
ThinBottomBorder = 1
Else
ThinBottomBorder = 0
End If
End Function


Then put this in M15:
=thinbottomborder(a14)
 
M

Marco

No go. Still getting same error.


JLGWhiz said:
If Selection.Borders(xlEdgeBottom).Weight = xlThin _
Then ActiveCell.FormulaR1C1 = "1" Else ActiveCell.FomulaR1C1 = "0"
Click to expand...
 
D

Dave Peterson

Put the entire code that I posted in its own module--Insert|Module--don't put it
in a worksheet module and don't put it under the ThisWorkbook module.
 
J

JLGWhiz

Dave's function worked fine for me. I copied his code and pasted it in my
standard code module, then I put a thin solid border on a cell in cell A4.
Then in a cell in another column, I typed "ThinBottomBorder(a4)" without the
quote marks, pressed enter and a 1 appeared in that cell. Maybe, if you
explained how you tried his code the problem could be cleared up.

Marco said:
No go. Did as instructed and getting same error.
Click to expand...
 
M

Marco

So then it is what I am doing.

I have a Personal.xls macro wb and have some 27 modules with projects. I
opened #28, pasted Dave's Code from "option..." on, saved the changes, went
to XL in A1, put a thin border, in A10 typed

=thinbottomborder(A1)

and I get that #NAME? error.
 
M

Marco

THANKS for the vote of confidence. lol

Actually did follow your instructions but it will not work from my
personal.xls macro wb.

So I am appreciative of your help.

Sincere regards,

Marco
 
E

egun

Don't you need something like ='Personal.xls'!Module28.MacroName??? If
you're trying to use a UDF from your Personal macro workbook in a different
workbook, you need to have the entire reference to that macro or you get the
#NAME error, which is telling you it doesn't know what "MacroName" is.

HTH,

Eric
 
M

Marco

I have a different issue now, it is returning a 1 in cells that do not have a
bottom border as well as the one that does.
 
E

egun

I tried my solution above, and it worked.

However, I noticed that when I completely removed borders from the target
cell, the function still thought the border line weight was "xlThin", and put
a "1" in the cell with the UDF. Not sure why it does that. When I put a
thick border in the target cell, the function returned a zero.
 
M

Marco

I have a different issue now, it is returning a 1 in cells that do not have a
bottom border as well as the one that do.
 
M

Marco

I have a different issue now, it is returning a 1 in cells that do not have a
bottom border as well as the one that do.
 
D

Dave Peterson

If you're going to put the code in your personal.xls workbook, then you'll need
to tell the other workbook where to find the function:

=personal.xls!thinbottomborder(a14)
THANKS for the vote of confidence. lol

Actually did follow your instructions but it will not work from my
personal.xls macro wb.

So I am appreciative of your help.

Sincere regards,

Marco
Click to expand...
 
D

Dave Peterson

Changing the format of a cell won't cause excel to recalculate. You can tell
excel to recalculate the UDF the next time it recalculates, but that means you
could be one calculation behind.

Option Explicit
Function ThinBottomBorder(rng As Range) As Long
Application.Volatile
With rng.Cells(1).Borders(xlEdgeBottom)
If .Weight = xlThin _
And .LineStyle <> xlNone Then
ThinBottomBorder = 1
Else
ThinBottomBorder = 0
End If
End With
End Function

This also checks to see if the linestyle is actually used (different from
xlNone).

=personal.xls!thinbottomborder(a1)
 
Top