Function to Check Borders

[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

 

[JLGWhiz]

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

 

[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)

 

[Marco]

No go. Still getting same error.

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

 

[Marco]

No go. Still getting the same error.

Do I need to put in "Option Explicit"?

 

[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.

 

[Marco]

No go. Did as instructed and getting same error.

 

[Dave Peterson]

I don't think you did.

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

No go. Did as instructed and getting same error.

 

[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.

No go. Did as instructed and getting same error.

 

[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.

 

[Marco]

It worked when I put it in a new wb, but not when it's in my personal.xls wb.

 

[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

 

[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

 

[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.

 

[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.

 

[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.

 

[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.

 

[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

 

[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)