Function to Check Borders
 

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

Function to Check Borders
 

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


"Marco" wrote:

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

Function to Check Borders
 

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 wrote:

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

--

Dave Peterson

Function to Check Borders
 

No go. Still getting same error.


"JLGWhiz" wrote:

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


"Marco" wrote:

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

Function to Check Borders
 

No go. Still getting the same error.

Do I need to put in "Option Explicit"?

"Dave Peterson" wrote:

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 wrote:

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

--

Dave Peterson

Function to Check Borders
 

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 wrote:

No go. Still getting the same error.

Do I need to put in "Option Explicit"?

"Dave Peterson" wrote:

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 wrote:

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

--

Dave Peterson


--

Dave Peterson

Function to Check Borders
 

No go. Did as instructed and getting same error.

"Dave Peterson" wrote:

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 wrote:

No go. Still getting the same error.

Do I need to put in "Option Explicit"?

"Dave Peterson" wrote:

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 wrote:

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

--

Dave Peterson


--

Dave Peterson

Function to Check Borders
 

I don't think you did.

Debra Dalgleish has some notes how to implement macros he
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.)

Marco wrote:

No go. Did as instructed and getting same error.

"Dave Peterson" wrote:

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 wrote:

No go. Still getting the same error.

Do I need to put in "Option Explicit"?

"Dave Peterson" wrote:

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 wrote:

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

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Function to Check Borders
 

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" wrote:

No go. Did as instructed and getting same error.

"Dave Peterson" wrote:

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 wrote:

No go. Still getting the same error.

Do I need to put in "Option Explicit"?

"Dave Peterson" wrote:

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 wrote:

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

--

Dave Peterson


--

Dave Peterson

Function to Check Borders
 

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.

"JLGWhiz" wrote:

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" wrote:

No go. Did as instructed and getting same error.

"Dave Peterson" wrote:

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 wrote:

No go. Still getting the same error.

Do I need to put in "Option Explicit"?

"Dave Peterson" wrote:

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 wrote:

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

--

Dave Peterson


--

Dave Peterson

Function to Check Borders
 

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

"JLGWhiz" wrote:

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" wrote:

No go. Did as instructed and getting same error.

"Dave Peterson" wrote:

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 wrote:

No go. Still getting the same error.

Do I need to put in "Option Explicit"?

"Dave Peterson" wrote:

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 wrote:

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

--

Dave Peterson


--

Dave Peterson

Function to Check Borders
 

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" wrote:

I don't think you did.

Debra Dalgleish has some notes how to implement macros he
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.)

Marco wrote:

No go. Did as instructed and getting same error.

"Dave Peterson" wrote:

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 wrote:

No go. Still getting the same error.

Do I need to put in "Option Explicit"?

"Dave Peterson" wrote:

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 wrote:

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

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Function to Check Borders
 

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

Function to Check Borders
 

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" wrote:

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

Function to Check Borders
 

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.

Function to Check Borders
 

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.

"JLGWhiz" wrote:

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" wrote:

No go. Did as instructed and getting same error.

"Dave Peterson" wrote:

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 wrote:

No go. Still getting the same error.

Do I need to put in "Option Explicit"?

"Dave Peterson" wrote:

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 wrote:

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

--

Dave Peterson


--

Dave Peterson

Function to Check Borders
 

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" wrote:

I don't think you did.

Debra Dalgleish has some notes how to implement macros he
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.)

Marco wrote:

No go. Did as instructed and getting same error.

"Dave Peterson" wrote:

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 wrote:

No go. Still getting the same error.

Do I need to put in "Option Explicit"?

"Dave Peterson" wrote:

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 wrote:

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

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Function to Check Borders
 

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)

Marco wrote:

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" wrote:

I don't think you did.

Debra Dalgleish has some notes how to implement macros he
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.)

Marco wrote:

No go. Did as instructed and getting same error.

"Dave Peterson" wrote:

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 wrote:

No go. Still getting the same error.

Do I need to put in "Option Explicit"?

"Dave Peterson" wrote:

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 wrote:

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

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Function to Check Borders
 

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)




Marco wrote:

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" wrote:

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

--

Dave Peterson