Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Check for # of Words in Function | Excel Worksheet Functions | |||
Please check my use of the ACCRINTM function. | Excel Worksheet Functions | |||
Check my IF function | Excel Worksheet Functions | |||
check box function? | Excel Worksheet Functions | |||
Function to set borders of cell | Excel Worksheet Functions |