sg549


Hello everyone. I'm new to VBA programming, but I have done a little. I have a new project at work that I'm trying to accomplish via VBA, but I'm not sure how to get it done. Thanks in advance for your feedback.

My goal is to take data from a cell on Sheet2 of a workbook, and place that data in a comment field in the exact cell on Sheet1 in the same workbook (data in A10 on sheet 2 will be a comment in cell A10 on sheet 1). I will have to create a loop to do this in several columns and rows, but I'm pretty sure I know how to do that. But how do I insert that type of comment, if it's possible at all

Thanks!





Re: Inserting Comments in Excel...from a Beginner

Keithyboy1


Here's one possible solution. It assumes that, by comments, you are referring to the yellow post-it type comments that pop up when you hover over a cell (I'm actually not aware of any other type of comment anyway, but just in case!). I have made bold the particular part of the code that answers your question. The rest is just a suggestion.

Sub AddComments()

Dim dataRange As Range
Dim cell As Range

'Prompt user to define range containing values in Sheet2 and set dataRange accordingly
Set dataRange = Application.InputBox("Select range containing the values to convert into comments", Type:=8)

'Loop through each cell in the specified range, ignoring blank cells
For Each cell In dataRange.SpecialCells(xlCellTypeConstants).Cells

'Add a cell comment in the corresponding cell in Sheet1
Sheets(1).Range(cell.Address).AddComment (cell.Value)

Next

End Sub






Re: Inserting Comments in Excel...from a Beginner

sg549

Thanks, Keithyboy1. I understand what you're doing here, and it is a much simpler approach than I imagined. I have input the code, however, and am receiving an error. The input box comes up, and I'm able to select my range, but when I get the error and choose "debug", it takes me to the line that you made bold. It's giving me that stupid "Run-time error '1004'" message, which offers no help, so I'm not sure what to do. Any further assistance would be appreciated!





Re: Inserting Comments in Excel...from a Beginner

Keithyboy1

Having lifted the code straight from this thread and into a blank spreadsheet on my PC at home, it's working fine. The only time I can get it to generate an error is if I select a range of entirely blank cells, or if I were to click cancel to the input box. You would obviously write appropriate code to deal with these eventualities, but for the example I left it out.

Did you copy the code directly from this thread If you typed it in, there could be a typo somewhere. I'd try copying it in directly from this thread and give it another go. Failing that, come back with more precise details of the particular situation you're using it.





Re: Inserting Comments in Excel...from a Beginner

sg549

Yes, I took the code directly from your post, and even repeated that action just to be sure. It's still not working. I'm able to select the range, but get the following error: "Run-time error '1004': Application-defined or object-defined error". When I click "Debug", it highlights the line "Sheets(1).Range(cell.Address).AddComment(cell.Value)". Once again, I appreciate your help. I wish I knew enough to fix this myself.




Re: Inserting Comments in Excel...from a Beginner

Keithyboy1

I managed to get your error by selecting a range containing numerical values, which I assume is what you were doing. I guess by passing a numerical value into the String argument of the AddComment method, it wasn't liking it. I have amended the code to explicitly convert a cell's value to a string as it passes it to the AddComment method. I've also added code that deals with Cancel button being clicked on the input box, just to make it more complete.

Give it a try.

Code Snippet

Sub AddComments()

Dim dataRange As Range
Dim cell As Range

'Prompt user to define range containing values in Sheet2 and set dataRange accordingly
On Error Resume Next
Set dataRange = Application.InputBox("Select range containing the values to convert into comments", Type:=8)
On Error GoTo 0

If dataRange Is Nothing Then Exit Sub

'Loop through each cell in the specified range, ignoring blank cells
For Each cell In dataRange.SpecialCells(xlCellTypeConstants).Cells

'Add a cell comment in the corresponding cell in Sheet1
Sheets(1).Range(cell.Address).AddComment (CStr(cell.Value))

Next

Sheets(1).Select

End Sub