Linking a cell's comment to a vba form's textbox

  • Hi,

    I've created a form to display multiple cells when selecting a date and am having difficulty in trying to figure out how (or if it's possible) to link a cell's comment to a textbox on the form.


    Is it possible to modify this line of code to display the cell's comment.

    ('extrainfo' is the textbox. SLNo is an index number in column A)


    Code
    Me.extrainfo.Value = Application.WorksheetFunction.VLookup(SLNo, Sheets("data").Range("A2:AR1087"), 44, 0)

    Edited once, last by royUK: Add code tags ().

  • Hi and Welcome to the Forum :)


    Quite a precise question ...


    But rather risky to ask such a question ... without attaching your file ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Thanks.

    I didn't attach the file as it has a lot of other peoples private data in it.


    Here's the code if that helps

    Edited once, last by royUK: add code tags ().

  • I'm not sure what you are doing, but I woulddn't use VLookup. I would use the VBA Find method.


    The example file has a Function to test if a cell has a comment. The Button on the form tests for a comment using the Function, if the cell contains a comment it will enter it to the TextBox.


    Attach a small dummy workbook and I will make a better example.


    Note: I have added Code Tags to your post, please use them in future when posting code to the Forum. Watch this to see how

    External Content youtu.be
    Content embedded from external sources will not be displayed without your consent.
    Through the activation of external content, you agree that personal data may be transferred to third party platforms. We have provided more information on this in our privacy policy.

  • I think that I have the columns going to the correct textboxes. Check this example.


    If it needs amending I'll check back tomorrow and add explanation.

  • Thanks, that works a lot better for me.

    There is some code i stripped out of the file that would need updating now though which is code to update the various cells from the form.

    Can you take a look at how that could be done please?



    Here's the code:


  • This should do what you want


  • I have added a Procedure to load the ComboBox called RefreshCombo so that it can refreshed when needed. This procedure is called from the Initialize event of the UserForm to load the data. It is also used in the update button code to upload the changed data

  • There is a problem with the updating code.


    example:

    If you edit the comment linked (from cell B3) textbox on the form, it overwrites the text in cell B3 with the value from cell C3


    can you take a look please?

  • Is it possible to change the linked comment boxes to maintain the formatted text in them when on the form and back again when updating them.


    Such as:

    THIS IS THE FIRST LINE OF TEXT IN THE COMMENT

    This is the second line

    This is the third line

  • The problem is the line breaks, etc don't display properly when imported to the UserForm, which is why I used Clean & Trim. You can try with those Functions.

  • Thanks Roy, it's not what i meant though.


    Here's a better example


    Here is an example comment on the sheet:

    "THIS IS THE FIRST LINE OF TEXT IN THE COMMENT

    This is the second line"


    This is what it turns into on the form:

    "THIS IS THE FIRST LINE OF TEXT IN THE COMMENT

    This is the second line"


    I would like to retain the text in italics and the difference in bold & normal text when on the form's textbox if possible?

  • I've exhausted all options on rich text on the form, it's not possible at present.


    Another tweak i'm trying to achieve is selecting a cell (A450 for example) and when opening the form have that record show first instead of having to scroll through all the records to get to it on the 'index number' drop menu.

    Is that possible with the example i posted above?


    thanks

    Paul

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!