VBA for userform/Comment box type function

[harryco79]

Hello everyone. Seeking help on a function i want to create in a worksheet that mimics the "comment box" function but with a twist.

I would like the user to be able to click a cell (A1) and then a userform with a textbox will appear to the right of it.
The user then inputs a comment , hits save and the form will disappear.

However i would like the text input and saved to be linked to a particular cell in another worksheet (worksheet B) within the workbook.

I Will be using this function for mutilple cells in the workbook. Can anyone helpp with this.

I am using Excel 2007 and inexperienced with VBA.

Thanks so much for all the help in advance. Love the msg board!!!

 

[harryco79]

Tried it as instructed. When i double click A1 I get the below. Not sure what I am doing wrong

 

[harryco79]

I am rethinking this whole approach. I will have 10s (perhaps up to 100) of such cells to be double-clicked and not so sure how practical that will be on the VBA code side.

Will it be possible to just double click a cell (A1) and a formatted comment box appears ready for the user to input text. Then when done they click out of the out of the comment box and it disappears. The text in the comment is saved to another worksheet in cell B2.

Thanks

 

[mikerickson]

Thats why I choose a UDF approach.

Your 10 cells won't require any change to the code. If you had 100 cells, it wouldn't require any code change.

I've PMed you about my e-mailing a workbook to you, to show how this is all put together.

 

[harryco79]

Thanks. Sent you the email addy. Again I really appreciate your help with this

 

[harryco79]

WORKS LIKE A CHARM!!!!! . Thank you so Much!