Results 1 to 5 of 5
  1. #1
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts

    Excel comment cells via VBA (Excel 2003)

    Hi All

    I have a protected sheet which is being used for display of records (in rows) and data entry of new record rows.
    The standard Excel top panel command bars have been turned off.
    I have 'cancelled' the usual Excel right-click menu and replaced it with a custom right-click menu suitable for the data entry tasks.

    I would like my Users to be able to add cell comments, amend cell cell comments and optionally, delete cell comments.
    I have 'copied' the standard Excel button images for these comment tasks onto my protected sheet.
    I would like to assign VBA macros to these images to replicate the Excel function.

    Has anyone done this before?
    I would appreciate any help.

    zeddy

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel comment cells via VBA (Excel 2003)

    If you allow Edit Objects on the protected sheet, users have the ability to insert and edit comments.
    I'd add the Insert | Comment command to the custom right-click menu. This command automatically changes to Edit Comment if the active cell has a comment.
    You can also add the Edit | Delete Comment command to the right-click menu. This command will only be enabled if the active cell has a comment.

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts

    Re: Excel comment cells via VBA (Excel 2003)

    Hi Hans

    Many thanks for your prompt and expert guidance.

    Unfortunately, my custom right-click menu highlights the record row first.
    This was to help the User to clearly identify the particular record before selecting one of the custom right-click menu options (e.g delete record etc).
    But your solution is simpler. Perhaps I should disable the highlight row feature if the User is in a particular column that I would allow comments.

    So how do I "allow Edit Objects on the protected sheet"?

    zeddy

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel comment cells via VBA (Excel 2003)

    In the Tools | Protection | Protect Sheet dialog - see screenshot below.
    Attached Images Attached Images
    • File Type: png x.png (9.9 KB, 0 views)

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Excel comment cells via VBA (Excel 2003)

    Why not have your code highlight the row, then reactivate the clicked cell?
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •