VBA For Comments

[MikeG]

I would like it so that when a user presses ctrl m, a macro will be activated that reads any comment that is in the current cell, displays this in a user form, allows the user to edit the comment, then puts the new comment in place when the user hits OK?

I wonder if anyone could help.

Thanks,

MikeG

 

[shg]

Why not use Shift+F2 instead?

 

[MikeG]

Why not use Shift+F2 instead?

Thanks shg. I just want to give my users a better-sized box.

Mike

 

[MikeG]

I think I have got most of the code.

However, could someone tell me how I can get the text that someone enters into a textbox on the user form into a variable.

I have

Private Sub TextBox1_Change

New_Comment=???

End Sub

And I don't know what to do for the ??? part.

Thanks

 

[Rick Rothstein]

However, could someone tell me how I can get the text that someone enters into a textbox on the user form into a variable.

I have

Private Sub TextBox1_Change

New_Comment=???

End Sub

And I don't know what to do for the ??? part.

Try...

New_Comment = TextBox1.Value

or...

New_Comment = TextBox1.Text

 

[MikeG]

Try...

New_Comment = TextBox1.Value

or...

New_Comment = TextBox1.Text

Thanks Rick.

 

[MikeG]

I think the final thing I need is to put the comment in when the ok button is hit.

Using some old code, I tried

With Selection

ActiveCell.Comment= New_Comment
End With

Unload Me

End Sub

But got an error.

 

[Rick Rothstein]

I think the final thing I need is to put the comment in when the ok button is hit.

ActiveCell.Comment= New_Comment

Not sure if this is the simplest way or not, but it works...

ActiveSheet.Shapes("Comment 1").TextFrame.Characters.Text = New_Comment

If you are not sure of the comment's name for use in the Shapes collection call, you can find it by selecting the cell, right click it and pick "Edit Comment" from the popup menu that appears... click inside the edit area as if you were going to change the text and then look at the Name Box (field to the left of the Formula Bar).

 

[MikeG]

Rick. Thanks, and sorry, I don't thing I explained myself very well, or I misunderstand.

When a user selects any cell in the worksheet, they can them press crtl l and this brings up a user form with a text box, and an OK button.

When the text box opens, I want it to display any comment that was already in the current cell. So I have tried:

Private Sub UserForm_Initialize()

Me.TextBox1.Text = ActiveCell.Comment

End Sub

but this does not work.

What I then want is that the user can edit or type in new text in the text box, and then when the OK button, then the new text replaces the old comment. So I tried:


Private Sub OKButton_Click()

Unload Me
ActiveCell.Comment = TextBox1.Text

End Sub

And this does not work either.

The code works OK if I just work with the Cell's value, but stops working when I add the .Comment lines.

Thanks,

Mike

 

[MikeG]

Well I have made some progress by using NoteText instead of Comment.

I can read the comment in OK now, but when I try to write it back using the following...

Private Sub OKButton_Click()

Unload Me

ActiveCell.NoteText = TextBox1.Value

End Sub

...I get an error on the last line "Assignemnt to constant not permitted"

Hmmmm