Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test for comment in cell
I have a routine that search for a given number in a range on worksheet. If
the given number if found I want to test for a comment associated with the found cell. My code is returning true for any given number so something is wrong, but I can't seem to figure it out. If anyone has experience with testing for a comment, I'd be very appreciative. My code follows. TIA, Ken Public Function fCellHasComment(lngStoreNumber As Long) As Boolean On Error GoTo PROC_ERROR Dim Cell As Range With ThisWorkbook.Worksheets("Price Groups") For Each Cell In Range("PriceGroupsStoreNumbers") If Cell.Value = lngStoreNumber Then If Cells(Cell.Row, Cell.Column).Comment.Text = "" Then fCellHasComment = False Else fCellHasComment = True End If End If Next Cell End With PROC_EXIT: Exit Function PROC_ERROR: Call ShowError("modUtilities", "fCellHasComment", Err.Number, Err.Description, Err.Source) Resume PROC_EXIT End Function |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test for comment in cell
fCellHasComment = Not Cell.Comment Is Nothing
or Dim cm as Comment Set cm = cell.comment fCellHasComment = Not cm Is Nothing but looking at your code what happens if more than one cell in the range has the value of lngStoreNumber, and some of these cells have comments and some don't... Regards, Peter T "Ken Warthen" wrote in message ... I have a routine that search for a given number in a range on worksheet. If the given number if found I want to test for a comment associated with the found cell. My code is returning true for any given number so something is wrong, but I can't seem to figure it out. If anyone has experience with testing for a comment, I'd be very appreciative. My code follows. TIA, Ken Public Function fCellHasComment(lngStoreNumber As Long) As Boolean On Error GoTo PROC_ERROR Dim Cell As Range With ThisWorkbook.Worksheets("Price Groups") For Each Cell In Range("PriceGroupsStoreNumbers") If Cell.Value = lngStoreNumber Then If Cells(Cell.Row, Cell.Column).Comment.Text = "" Then fCellHasComment = False Else fCellHasComment = True End If End If Next Cell End With PROC_EXIT: Exit Function PROC_ERROR: Call ShowError("modUtilities", "fCellHasComment", Err.Number, Err.Description, Err.Source) Resume PROC_EXIT End Function |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test for comment in cell
Ken,
Try this. I couldn't duplicate your ShowError sub because I don't know what is does Public Function fCellHasComment(lngStoreNumber As Long) As Boolean On Error GoTo PROC_ERROR Dim Cell As Range With ThisWorkbook.Worksheets("Price Groups") For Each Cell In Range("PriceGroupsStoreNumbers") If Cell.Value = lngStoreNumber Then Set mycomment = Cell.Comment If mycomment Is Nothing Then fCellHasComment = False Else fCellHasComment = True End If End If Next Cell End With PROC_EXIT: Exit Function PROC_ERROR: Call ShowError("modUtilities", "fCellHasComment", Err.Number, Err.Description, Err.Source) Resume PROC_EXIT End Function -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Ken Warthen" wrote: I have a routine that search for a given number in a range on worksheet. If the given number if found I want to test for a comment associated with the found cell. My code is returning true for any given number so something is wrong, but I can't seem to figure it out. If anyone has experience with testing for a comment, I'd be very appreciative. My code follows. TIA, Ken Public Function fCellHasComment(lngStoreNumber As Long) As Boolean On Error GoTo PROC_ERROR Dim Cell As Range With ThisWorkbook.Worksheets("Price Groups") For Each Cell In Range("PriceGroupsStoreNumbers") If Cell.Value = lngStoreNumber Then If Cells(Cell.Row, Cell.Column).Comment.Text = "" Then fCellHasComment = False Else fCellHasComment = True End If End If Next Cell End With PROC_EXIT: Exit Function PROC_ERROR: Call ShowError("modUtilities", "fCellHasComment", Err.Number, Err.Description, Err.Source) Resume PROC_EXIT End Function |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test for comment in cell
I noticed a few things that are wrong about your code.
1.) I would highly recommend not using On Error GoTo statements unless absolutely necessary, this only causes problems when you are trying to debug code. 2.) In my code, I test if the cell has a comment, even if the comment is a zero length string. If it doesn't have a comment the MyComment variable is Nothing. If it does have a comment, I then test if the comment is a non-zero length string. If so, the function returns True. 3.) You don't have to set a function to False. The default value for a function that returns a Boolean data type will always be False, unless set to True. 4.) I also added a "." in front of Cells(Cell.Row, Cell.Column).Comment which will get the correct cell from the Price Groups worksheet. Without the "." you will be getting cells from the activesheet. Maybe in your case the activesheet is Price Groups, but it's worth noting. 5.) Hope this helps! If so, let me know, click "YES" below. Public Function fCellHasComment(lngStoreNumber As Long) As Boolean Dim Cell As Range Dim MyComment As Comment With ThisWorkbook.Sheets("Price Groups") For Each Cell In Range("PriceGroupsStoreNumbers") If Cell.Value = lngStoreNumber Then Set MyComment = .Cells(Cell.Row, Cell.Column).Comment If Not MyComment Is Nothing Then If MyComment.Text < "" Then fCellHasComment = True End If End If Next Cell End With End Function -- Cheers, Ryan "Ken Warthen" wrote: I have a routine that search for a given number in a range on worksheet. If the given number if found I want to test for a comment associated with the found cell. My code is returning true for any given number so something is wrong, but I can't seem to figure it out. If anyone has experience with testing for a comment, I'd be very appreciative. My code follows. TIA, Ken Public Function fCellHasComment(lngStoreNumber As Long) As Boolean On Error GoTo PROC_ERROR Dim Cell As Range With ThisWorkbook.Worksheets("Price Groups") For Each Cell In Range("PriceGroupsStoreNumbers") If Cell.Value = lngStoreNumber Then If Cells(Cell.Row, Cell.Column).Comment.Text = "" Then fCellHasComment = False Else fCellHasComment = True End If End If Next Cell End With PROC_EXIT: Exit Function PROC_ERROR: Call ShowError("modUtilities", "fCellHasComment", Err.Number, Err.Description, Err.Source) Resume PROC_EXIT End Function |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test for comment in cell
Couple of 'comments' !
1.) I would highly recommend not using On Error GoTo statements unless absolutely necessary, this only causes problems when you are trying to debug code. I'd recommend the exact opposite in production code (unless 110% no error can occur). Can temporarily set a global, eg gbDebug, so that any error is handled differently while debugging. Set MyComment = .Cells(Cell.Row, Cell.Column).Comment why not simply Set MyComment = Cell.Comment If MyComment.Text < "" Then fCellHasComment = True Even an empty comment still means the cell has a comment. However, and I didn't notice before - With ThisWorkbook.Sheets("Price Groups") For Each Cell In Range("PriceGroupsStoreNumbers") The "With" line is only required if the range "PriceGroupsStoreNumbers" is a worksheet level range, in which case "Range" should be prefixed with a dot. However, if ThisWorkbook is not the active workbook would also need the With and a dot before Range (but not the sheet qualification unless a worksheet level name) Regards, Peter T "Ryan H" wrote in message ... I noticed a few things that are wrong about your code. 1.) I would highly recommend not using On Error GoTo statements unless absolutely necessary, this only causes problems when you are trying to debug code. 2.) In my code, I test if the cell has a comment, even if the comment is a zero length string. If it doesn't have a comment the MyComment variable is Nothing. If it does have a comment, I then test if the comment is a non-zero length string. If so, the function returns True. 3.) You don't have to set a function to False. The default value for a function that returns a Boolean data type will always be False, unless set to True. 4.) I also added a "." in front of Cells(Cell.Row, Cell.Column).Comment which will get the correct cell from the Price Groups worksheet. Without the "." you will be getting cells from the activesheet. Maybe in your case the activesheet is Price Groups, but it's worth noting. 5.) Hope this helps! If so, let me know, click "YES" below. Public Function fCellHasComment(lngStoreNumber As Long) As Boolean Dim Cell As Range Dim MyComment As Comment With ThisWorkbook.Sheets("Price Groups") For Each Cell In Range("PriceGroupsStoreNumbers") If Cell.Value = lngStoreNumber Then Set MyComment = .Cells(Cell.Row, Cell.Column).Comment If Not MyComment Is Nothing Then If MyComment.Text < "" Then fCellHasComment = True End If End If Next Cell End With End Function -- Cheers, Ryan "Ken Warthen" wrote: I have a routine that search for a given number in a range on worksheet. If the given number if found I want to test for a comment associated with the found cell. My code is returning true for any given number so something is wrong, but I can't seem to figure it out. If anyone has experience with testing for a comment, I'd be very appreciative. My code follows. TIA, Ken Public Function fCellHasComment(lngStoreNumber As Long) As Boolean On Error GoTo PROC_ERROR Dim Cell As Range With ThisWorkbook.Worksheets("Price Groups") For Each Cell In Range("PriceGroupsStoreNumbers") If Cell.Value = lngStoreNumber Then If Cells(Cell.Row, Cell.Column).Comment.Text = "" Then fCellHasComment = False Else fCellHasComment = True End If End If Next Cell End With PROC_EXIT: Exit Function PROC_ERROR: Call ShowError("modUtilities", "fCellHasComment", Err.Number, Err.Description, Err.Source) Resume PROC_EXIT End Function |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test for comment in cell
I guess it's personal preference on the On Error statement. I prefer to do a
little extra coding to protect against errors rather than use it. Not to say it never should be used, but I feel it can lead to problems. I noticed I should have used Cell.Comment after I had posted. I used this line: If MyComment.Text < "" Then fCellHasComment = True, because in Ken's orginal code he was testing if the comment had a non zero lenght string. I just added the If...Then statement that tested if a comment exists even if it is a zero length string. -- Cheers, Ryan "Peter T" wrote: Couple of 'comments' ! 1.) I would highly recommend not using On Error GoTo statements unless absolutely necessary, this only causes problems when you are trying to debug code. I'd recommend the exact opposite in production code (unless 110% no error can occur). Can temporarily set a global, eg gbDebug, so that any error is handled differently while debugging. Set MyComment = .Cells(Cell.Row, Cell.Column).Comment why not simply Set MyComment = Cell.Comment If MyComment.Text < "" Then fCellHasComment = True Even an empty comment still means the cell has a comment. However, and I didn't notice before - With ThisWorkbook.Sheets("Price Groups") For Each Cell In Range("PriceGroupsStoreNumbers") The "With" line is only required if the range "PriceGroupsStoreNumbers" is a worksheet level range, in which case "Range" should be prefixed with a dot. However, if ThisWorkbook is not the active workbook would also need the With and a dot before Range (but not the sheet qualification unless a worksheet level name) Regards, Peter T "Ryan H" wrote in message ... I noticed a few things that are wrong about your code. 1.) I would highly recommend not using On Error GoTo statements unless absolutely necessary, this only causes problems when you are trying to debug code. 2.) In my code, I test if the cell has a comment, even if the comment is a zero length string. If it doesn't have a comment the MyComment variable is Nothing. If it does have a comment, I then test if the comment is a non-zero length string. If so, the function returns True. 3.) You don't have to set a function to False. The default value for a function that returns a Boolean data type will always be False, unless set to True. 4.) I also added a "." in front of Cells(Cell.Row, Cell.Column).Comment which will get the correct cell from the Price Groups worksheet. Without the "." you will be getting cells from the activesheet. Maybe in your case the activesheet is Price Groups, but it's worth noting. 5.) Hope this helps! If so, let me know, click "YES" below. Public Function fCellHasComment(lngStoreNumber As Long) As Boolean Dim Cell As Range Dim MyComment As Comment With ThisWorkbook.Sheets("Price Groups") For Each Cell In Range("PriceGroupsStoreNumbers") If Cell.Value = lngStoreNumber Then Set MyComment = .Cells(Cell.Row, Cell.Column).Comment If Not MyComment Is Nothing Then If MyComment.Text < "" Then fCellHasComment = True End If End If Next Cell End With End Function -- Cheers, Ryan "Ken Warthen" wrote: I have a routine that search for a given number in a range on worksheet. If the given number if found I want to test for a comment associated with the found cell. My code is returning true for any given number so something is wrong, but I can't seem to figure it out. If anyone has experience with testing for a comment, I'd be very appreciative. My code follows. TIA, Ken Public Function fCellHasComment(lngStoreNumber As Long) As Boolean On Error GoTo PROC_ERROR Dim Cell As Range With ThisWorkbook.Worksheets("Price Groups") For Each Cell In Range("PriceGroupsStoreNumbers") If Cell.Value = lngStoreNumber Then If Cells(Cell.Row, Cell.Column).Comment.Text = "" Then fCellHasComment = False Else fCellHasComment = True End If End If Next Cell End With PROC_EXIT: Exit Function PROC_ERROR: Call ShowError("modUtilities", "fCellHasComment", Err.Number, Err.Description, Err.Source) Resume PROC_EXIT End Function . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Test for comment in cell
Thanks for the help. Your suggestion worked fine.
Ken "Ken Warthen" wrote: I have a routine that search for a given number in a range on worksheet. If the given number if found I want to test for a comment associated with the found cell. My code is returning true for any given number so something is wrong, but I can't seem to figure it out. If anyone has experience with testing for a comment, I'd be very appreciative. My code follows. TIA, Ken Public Function fCellHasComment(lngStoreNumber As Long) As Boolean On Error GoTo PROC_ERROR Dim Cell As Range With ThisWorkbook.Worksheets("Price Groups") For Each Cell In Range("PriceGroupsStoreNumbers") If Cell.Value = lngStoreNumber Then If Cells(Cell.Row, Cell.Column).Comment.Text = "" Then fCellHasComment = False Else fCellHasComment = True End If End If Next Cell End With PROC_EXIT: Exit Function PROC_ERROR: Call ShowError("modUtilities", "fCellHasComment", Err.Number, Err.Description, Err.Source) Resume PROC_EXIT End Function |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Test cell for list data and modify cell contents | Excel Programming | |||
Test a cell for a comment presence from within a macro form 2007 | Excel Programming | |||
copy comment content to cell content as data not as comment | Excel Discussion (Misc queries) | |||
Create Cell Comment based on text in a cell on another worksheet | Excel Discussion (Misc queries) | |||
How can i test a cell for bold style in a cell (Excel 2003)? | Excel Worksheet Functions |