Adding Comments to a Selection in Excel 97

[sirhobbit]

Hi all,
I am working on a shared spreadsheet where a user who wishes to edit the sheet makes a selection of several cells (all along the same row) and clicks on a button to open a user form. Here they can select options and input text. I have a text box in that user form and I want what ever is written in that box to be made a comment for all the cells highlighted or for only 1 cell at the far left or right of the selection. Any assistance you be appreciated.

 

[murad5]

This should work to add a comment to the leftmost cell in their selection...

Sub comment()
With ActiveCell 'the active cell is the leftmost in any selection [/green]
.AddComment
.comment.Visible = False
.comment.Text Text:="Test" ' insert your own comment from your text box here.[/green]
End With
End Sub

 

[RobBroekhuis]

With ActiveCell 'the active cell is the leftmost in any selection

Not quite true. If the selection was made by extending to the left, then the rightmost cell will be the activecell. the following is more universal:

with cells(selection.row,selection.column)



Rob

 

[murad5]

Very true, Rob - this is what comes of typing VB at 8.30 in the morning!

 

[RobBroekhuis]

Heck, it's not even that late here ;-)


Rob