Comment box

[Chiefgarcia]

I am trying to find a way where I can have excel Insert a comment box
automatically.

For example:
I have a data sheet and every time I need to insert a new row, I then need
to insert a comment box in every cell. Instead of copying and pasting, is
there a way to make excel auto populate the comment box when I insert a new
column?

If so, how or is there a macro that I can use?

Thanks.

 

[Rick Rothstein]

Yes, this can be done. What do you mean when you say "in every cell"? Do you
mean every cell in the new row (all 256 of them for XL2003 and lower, 16384
of them for XL2007)? Or do you mean a smaller range within the row? If the
smaller range, tell us what that range is. The bigger question is... what do
you want in each of these Comment boxes?

 

[Chiefgarcia]

Thank you for your willingness to help me on this.
To make it more specific, I hava a data that sheet that contains about 14
rows (cells). But I want to find a way to insert comment boxes automatically.
So when I right clicka and choose the insert button, the new row show all the
new 14 (cells) with a comment box on them and if possible to have the
following information in the comment box:

Phone:
Website:
Org Description:
Leads/Contacts:
Active Members at Nike:
Other Notes:

Thanks.

 

[Rick Rothstein]

I think you may be mixing the rows for columns, or perhaps I'm still not
visualizing your set up. You say you have 14 rows (cells)... do you mean you
have data in 14 columns per row? Is so, which columns are these (it's just
as easy to give you a solution for your exact setup as it is to give you a
general solution that you have to change later). And when you insert a row,
you want to insert Comments in the 14 cells in that row, correct? You showed
the information that you want to put in the Comments... where is that
information currently located at?

 

[Chiefgarcia]

Hi Rick. I don't want to make this complicated, the cells where I need these
comment boxes are for example: A1:N14 (14 cells across) the data that I said
I wanted to have inside the comment boxes is not located anywhere. The data
would be like a template for when I insert a new row, I want those cells to
appear with the comment boxes and the comment boxes should already have:

Phone:
Website:
Org Description:
Leads/Contacts:
Active Members at Nike:
Other Notes:

But if it is not possible to include this information, I just want to know
how I can make the cells show up with a comment box, when I insert a new row.

Thanks.

 

[Rick Rothstein]

I can give you a macro that will insert the "template" you indicated you
wanted, but I do not know of a way to active "Edit Comment" so you can just
type in the remainder of the information. I can write a macro that will ask
the user to fill in the information in an InputBox, TextBox, or some such
way, and then modify the Comment "template" to insert that information.
Would that be acceptable? In any event, here is the macro that will insert a
row (at the active cell) and then create the 14 Comments with the "template"
text you indicated...

Sub InsertRowAndCommentTemplate()
Dim X As Long
ActiveCell.EntireRow.Insert
For X = 1 To 14
With Cells(ActiveCell.Row, X).AddComment("Phone:" & vbLf & _
"Website:" & vbLf & "Org Description:" & vbLf & _
"Leads/Contacts:" & vbLf & "Active Members at Nike:" & _
vbLf & "Other Notes:").Shape
.Height = 100
.Width = 200
End With
Next
End Sub

 

[Chiefgarcia]

Hi Rick, I believe this should work just fine. Thank you so much for your
assistance on this, I really appreciate you taking the time to help me out on
my request.

Thanks.

 

[Chiefgarcia]

Hi Rick,
I'm just wondering if you would have an answer on how to make the text
inside the comment box BOLD, the macro that I'm using works but I just want
to know if I can make the text bold.

Thanks.