+ Reply to Thread
Results 1 to 7 of 7

VBA Help with inserting a comment to a cell range that has been modified

  1. 06-24-2022, 06:23 AM #1
    flsanchez122
    flsanchez122 is offline
    Registered User
    Join Date
    06-22-2022
    Location
    NY
    MS-Off Ver
    MS Office
    Posts
    4

    VBA Help with inserting a comment to a cell range that has been modified

    I have this code which will add a comment to a specific cell (A1) however I would like to add comments to any cell modified in Column A or A1:A700. Thank in advance for the help!!

    Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Set rng = Range("A1")

    On Error Resume Next

    If rng.Comment Is Nothing And Len(rng.Value) > 0 Then
    Target.Worksheet.Unprotect Password:="password"
    rng.AddComment Now & "-" & Application.username & "-" & rng.Value
    Target.Worksheet.Protect Password:="password"

    Else

    rng.Comment.Text vbNewLine & Now & "-" & Application.username & "-" & rng.Value, Len(rng.Comment.Text) + 1
    End If
    Last edited by flsanchez122; 06-24-2022 at 06:59 AM.
    Reply With QuoteRegister To Reply
  2. 06-24-2022, 07:19 AM #2
    Artik
    Artik is online now
    Forum Expert
    Join Date
    08-17-2007
    Location
    Poland
    Posts
    2,222

    Re: VBA Help with inserting a comment to a cell range that has been modified

    Try it: 
    Please Login or Register  to view this content.
    Artik
    Last edited by Artik; 06-27-2022 at 06:15 PM. Reason: Improving the code
    Reply With QuoteRegister To Reply
  3. 06-24-2022, 08:04 AM #3
    flsanchez122
    flsanchez122 is offline
    Registered User
    Join Date
    06-22-2022
    Location
    NY
    MS-Off Ver
    MS Office
    Posts
    4

    Re: VBA Help with inserting a comment to a cell range that has been modified

    Wow thank you so much!! Enjoy your day and weekend!
    Reply With QuoteRegister To Reply
  4. 06-24-2022, 08:22 AM #4
    carlmon
    carlmon is offline
    Valued Forum Contributor
    Join Date
    10-12-2021
    Location
    CT
    MS-Off Ver
    365
    Posts
    462

    Re: VBA Help with inserting a comment to a cell range that has been modified

    Make sure to mark this thread as solved & give rep to Artik for his help
    Reply With QuoteRegister To Reply
  5. 06-27-2022, 01:25 PM #5
    flsanchez122
    flsanchez122 is offline
    Registered User
    Join Date
    06-22-2022
    Location
    NY
    MS-Off Ver
    MS Office
    Posts
    4

    Re: VBA Help with inserting a comment to a cell range that has been modified

    Hi Artik

    So when you supplied the code, i did not try to enter data into any other cells. I just typed in a few cells in Column A. So when I type in other cells, I receive an error: Run-time error '91': Objective variable or with block variable not set. When I click debug, I am taken to the line of code: For Each rng In rngScope.Cells

    Can you help? Column A only needs the comments. All other cells do not need comments and data can be entered without unprotecting the worksheet. Thank you in advance Artik. Truly appreciate it!
    Reply With QuoteRegister To Reply
  6. 06-27-2022, 06:19 PM #6
    Artik
    Artik is online now
    Forum Expert
    Join Date
    08-17-2007
    Location
    Poland
    Posts
    2,222

    Re: VBA Help with inserting a comment to a cell range that has been modified

    A bug crept into my code. I highlighted the part that was missing.
    If there is to be no sheet protection, then remove the lines that unprotect and protect the sheet.

    Artik
    Reply With QuoteRegister To Reply
  7. 06-28-2022, 07:26 AM #7
    flsanchez122
    flsanchez122 is offline
    Registered User
    Join Date
    06-22-2022
    Location
    NY
    MS-Off Ver
    MS Office
    Posts
    4
    Ok I will thanks Artik I appreciate it


    Quote Originally Posted by Artik View Post
    A bug crept into my code. I highlighted the part that was missing.
    If there is to be no sheet protection, then remove the lines that unprotect and protect the sheet.

    Artik
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Cells that have been modified or added are marked with a comment column
    By wk9128 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-17-2020, 10:54 AM
  2. Check if cell in range is empty, if it is delete comment else format comment to
    By banaanas in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-15-2015, 01:15 AM
  3. Clear cell range on multiple sheets if cell on one sheet is modified.
    By bjg1016 in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 11-12-2012, 02:39 PM
  4. open a comment to cell range upon true condition then removing comment on false condition
    By ferrum_equitis in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-08-2012, 12:55 AM
  5. Display Message Box if any cell value in range is modified
    By eddieD95 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-16-2012, 10:31 AM
  6. inserting date modified in sheet.
    By rtkman in forum Excel General
    Replies: 6
    Last Post: 09-01-2011, 09:01 AM
  7. Inserting Last Date Modified in Footer
    By Russ in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-04-2006, 08:35 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1