How to prompt an input box and add comment when certain value is selected

[StedDOOM]

Howdy y’all,

I am working on a sheet that will ultimately have a lot of non-Excel-savvy folks using it. For that reason, I’ve been asked to protect the sheet to limit how much can be edited.

As such, I don’t want to allow “edit objects” when protecting because there are several shapes/text boxes that need to be left alone. However, doing so also removes the ability to add comments to cells, even if said cell is unlocked, and the users need to add comments if one singular condition is met.

The only context in which a comment needs to be added is if the option “x” is selected from a dropdown list, and this dropdown is only in one column from J27 downward.

The only thing I can think of to work around the accessibility is to run a routine where if “x” is selected from the dropdown, to have an inputbox prompt the note to be entered, and then once entered, have VBA unprotected the sheet, add the comment to the cell, and re-protect the sheet. However, I have no clue where to even start with doing this.

Additionally, if there’s a better/smarter alternative, I’m all ears, really all I want to do is be able to add comments to cells without having to leave ALL objects editable.

 

[My Aswer Is This]

You said:
where if “x” is selected from the dropdown, to have an inputbox prompt the note to be entered, and then once entered, have

When you say "Note" do you mean a note Excel has a way of adding notes to a cell.
Or do you mean entering a value into the cell.
And your subject title say comment but then you say Note
I believe earlier versions of Excel you could add comments, but later versions use Notes.
I use Excel 2013 which use comments

 

[StedDOOM]

You said:
where if “x” is selected from the dropdown, to have an inputbox prompt the note to be entered, and then once entered, have

When you say "Note" do you mean a note Excel has a way of adding notes to a cell.
Or do you mean entering a value into the cell.
And your subject title say comment but then you say Note
I believe earlier versions of Excel you could add comments, but later versions use Notes.
I use Excel 2013 which use comments

Sorry, yes, I meant comment rather than note. I am trying to add a comment to the cell through prompting an inputbox (or any method really) by utilizing a routine that will unprotect the sheet, add the comment, then re-protect it.

 

[Jeffrey Mahoney]

The code below needs to put in the SHEET module. Find the sheet name in the objects list in VBA (ALT-F11). Past this code. Change the colored lines below depending on your password and the method you want to protect your sheet.

You need to create a named range. I called mine X_rng. For you, that is J27 and down. Any time a cell in that range changes, this macro will run. If a user enters a null or no text, the comment will get deleted.

I had to get rid of the colored text because it added it into the code

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim i As Range
  Dim xRng As Range
  Dim Sht As Worksheet
  Dim NoteText As String
  Dim CommentStat As Boolean
 
  Set xRng = Range("X_rng")
  Set Sht = ActiveSheet
 
  Set i = Intersect(Target, xRng)
  If Not i Is Nothing Then
    On Error Resume Next
    If Not i.Comment Is Nothing Then
      NoteText = i.Comment.Text
      CommentStat = True
    End If
    On Error GoTo 0
    NoteText = InputBox("Please enter a comment for this cell", "Comment Text", NoteText)
   
    On Error Resume Next
   Sht.Unprotect Password:="Jeff"
    On Error GoTo 0
   
    If NoteText = "" And CommentStat = True Then
      i.Comment.Delete
    Else
      If CommentStat = False Then
        i.AddComment
      End If
      i.Comment.Visible = False
      i.Comment.Text Text:=NoteText
    End If

    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="Jeff"
   
  End If
 
   
End Sub

 

[My Aswer Is This]

There you go someone else now is helping you so i will move on to another question that has no answers

 

[StedDOOM]

The code below needs to put in the SHEET module. Find the sheet name in the objects list in VBA (ALT-F11). Past this code. Change the colored lines below depending on your password and the method you want to protect your sheet.

You need to create a named range. I called mine X_rng. For you, that is J27 and down. Any time a cell in that range changes, this macro will run. If a user enters a null or no text, the comment will get deleted.

I had to get rid of the colored text because it added it into the code

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim i As Range
  Dim xRng As Range
  Dim Sht As Worksheet
  Dim NoteText As String
  Dim CommentStat As Boolean
 
  Set xRng = Range("X_rng")
  Set Sht = ActiveSheet
 
  Set i = Intersect(Target, xRng)
  If Not i Is Nothing Then
    On Error Resume Next
    If Not i.Comment Is Nothing Then
      NoteText = i.Comment.Text
      CommentStat = True
    End If
    On Error GoTo 0
    NoteText = InputBox("Please enter a comment for this cell", "Comment Text", NoteText)
  
    On Error Resume Next
   Sht.Unprotect Password:="Jeff"
    On Error GoTo 0
  
    If NoteText = "" And CommentStat = True Then
      i.Comment.Delete
    Else
      If CommentStat = False Then
        i.AddComment
      End If
      i.Comment.Visible = False
      i.Comment.Text Text:=NoteText
    End If

    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="Jeff"
  
  End If
 
  
End Sub

Thank you so much!

Is there a way to only prompt an inputbox if one specific drop down item is chosen? Users will only need to add a comment if one particular item is selected.

If not, this will definitely still work. Thanks again!

 

[Jeffrey Mahoney]

Do you want to delete the comment if the user selects a different value after already entering a comment?

 

[StedDOOM]

Do you want to delete the comment if the user selects a different value after already entering a comment?

That would be ideal, yes.

 

[Jeffrey Mahoney]

Ok, so see the code below

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim i As Range
  Dim xRng As Range
  Dim Sht As Worksheet
  Dim NoteText As String
  Dim CommentStat As Boolean
  
  Set xRng = Range("X_rng")
  Set Sht = ActiveSheet
  
  Set i = Intersect(Target, xRng)
  If Not i Is Nothing Then
    On Error Resume Next
    If Not i.Comment Is Nothing Then
      NoteText = i.Comment.Text
      CommentStat = True
    End If
    On Error GoTo 0
    
    If i.Value <> "X" And CommentStat = True Then
      i.Comment.Delete
      Exit Sub
    End If
    
    NoteText = InputBox("Please enter a comment for this cell", "Comment Text", NoteText)
    
    On Error Resume Next
    Sht.Unprotect Password:="Jeff"
    On Error GoTo 0
    
    If NoteText = "" And CommentStat = True Then
      i.Comment.Delete
    Else
      If CommentStat = False Then
        i.AddComment
      End If
      i.Comment.Visible = False
      i.Comment.Text Text:=NoteText
    End If

    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="Jeff"
    
  End If
  
    
End Sub

 

[Jeffrey Mahoney]

I assumed that "X" was the value to target the macro asking for a comment. You can change that as needed.