Worksheet change making object appear

[andrewb90]

Hello all,

I'm not sure if this is possible to do, but I figured I'd ask around and maybe someone smarter than I will have a creative solution.

I have a worksheet change event that highlights the current row that is selected. See below:

Option ExplicitConst MyAreas = "D4:R18,D20:R34,D36:R50,D52:R66,D68:R82,D89:V106,D108:V125,D127:V144,D146:V163,D165:V192,D194:V207"
Dim a, MyCol As Collection, rng As Range, X As Range
 
' Highlighting with Conditional Formatting
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim i As Long
  If Application.CutCopyMode Then Exit Sub
  If MyCol Is Nothing Then
    ' Setup MyCol only once first time
    Set MyCol = New Collection
    For Each a In Split(MyAreas, ",")
      MyCol.Add Range(a)
      ' Clear CF highligtings in each area for the first time
      Range(a).FormatConditions.Delete
    Next
  End If
  If Not X Is Nothing Then
    ' Clear the previous CF highlighting
    X.FormatConditions.Delete
  End If
  For Each X In MyCol
    ' Check intersection
    Set rng = Intersect(Target, X)
    If Not rng Is Nothing Then Exit For
  Next
  If Not X Is Nothing Then
    ' Highlight row of MyAreas via CF
    i = ActiveCell.Interior.ColorIndex
    Set X = X.Rows(rng.Row - X.Row + 1)
    With X.FormatConditions.Add(Type:=2, Formula1:=1)
      .Interior.ColorIndex = IIf(i < 0, 8, i + 1)
      .Font.Bold = True
    End With
  End If
End Sub

Now, in Column D is my list of names, and select names will be affixed with *** at the end indicating that there are important notes on another sheet.
Now, I'd like to, when a row is selected that has important notes (***), have an object appear that can take the user to the page with the notes. (The workbook is protected and that sheet is not visible are there are some many sheets)
Basically, I'd just like a way for the user to quickly access the notes without having to find the right sheet.
Other ideas as a solution that I've had and maybe someone else know how to make them work:
Make the name a hyperlink only when the notes are in place for that particular name. (The first name is D89 and the notes are on Notes!S89)
The other thing that would be very ideal is if comments can be programmed to include the notes.
The challenge with just putting the notes in another column that ref's the appropriate cells is my current layout of the sheet. The column wouldn't be conveniently accessible, and if I just inserted a column, a lot of code would need to changed, which I'd rather not do.

Any comments or ideas would be much appreciated.

 

[mikerickson]

I'm on my phone so cant go into great detail, but perhaps you could fetch the comments from the other sheet and put them in the Comment of the **** cell. Fhe user juzt has to hover to see them.

 

[andrewb90]

That's what i'm looking into right now. I'm just trying to figure out the best approach to updating the comments without slowing down excel. I'd imagine a worksheet change event would slow down excel wouldn't it? Especially since I already have that in place on a few other sheets.

Edit:
I've found this online and this maybe closer to what I need.

[COLOR=#000000]Private Sub Worksheet_Change(ByVal Target As Range)[/COLOR]    If Target.Cells.Count > 1 Then Exit Sub
    If Target.Column <> 6 Then Exit Sub

    Dim x As String
    Application.EnableEvents = False
    If Target.HasFormula Then
        x = Evaluate(Target.Formula)
    Else
        x = Target.Text
    End If

    Target.ClearComments
    If Target.Text = "" Then
        Application.EnableEvents = True
        Exit Sub
    End If

    Target.AddComment x
    Target = ""
    Application.EnableEvents = True [COLOR=#000000]End Sub[/COLOR]

The thing I'm not sure of is how to not put the comment elsewhere. I don't want to use the target cell. though the row # is the same, the column and sheet will be different.

 

[Jeffrey Mahoney]

Seems to me a that you could put text in the cell that references the comment you want to enter.
Like: ***{Sheet5!C16}
Then the Event would look in the cell decipher the area between the curly brackets as a cell with a note, then add the contents of that cell into the comment. Then it would delete the curly bracket part.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim i As Range
  Dim A As String
  Dim CelRef As String
  Dim c1 As Integer
  Dim c2 As Integer
  Dim R As Range
  Dim Sht As Worksheet
  
  Set i = Intersect(Range("D:D"), Target)
  If Not i Is Nothing Then
    A = i.Value
    If InStr(A, "{") > 0 And InStr(A, "}") > 0 And InStr(A, "!") > 0 Then
      c1 = InStr(A, "{")
      c2 = InStr(A, "}")
      CelRef = Mid(A, c1 + 1, c2 - c1 - 1)
      Set Sht = Sheets(Left(CelRef, InStr(CelRef, "!") - 1))
      Set R = Sht.Range(Mid(CelRef, InStr(CelRef, "!") + 1))
      On Error Resume Next
      i.ClearComments
      On Error GoTo 0
      i.AddComment
      i.Comment.Text Text:=R.Value
      i.Value = Left(A, c1 - 1)
    End If
  End If
      
    
End Sub

 

[andrewb90]

So the user would enter: here is a note...{Sheet5!C16}?

 

[Jeffrey Mahoney]

I was imagining an administrator adding that part, but yes a user could be instructed to do that.

If you wanted to take it a step further... you could leave the curly bracket reference in the cell in case you wanted to update the comment. Meaning, if people change the note in the reference cell, you would probably want to update the comment. You could create a macro that updates the entire column D at once. That could either be triggered from an event (cell change in a specific area designed for notes; or workbook open), or just a button that updates manually.

Jeff

 

[Jeffrey Mahoney]

If users will be adding the comments, then maybe a little more error checking:

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim i As Range
  Dim A As String
  Dim CelRef As String
  Dim c1 As Integer
  Dim c2 As Integer
  Dim R As Range
  Dim Sht As Worksheet
  
  Set i = Intersect(Range("D:D"), Target)
  If Not i Is Nothing Then
    A = i.Value
    If InStr(A, "{") > 0 And InStr(A, "}") > 0 And InStr(A, "!") > 0 Then
      c1 = InStr(A, "{")
      c2 = InStr(A, "}")
      CelRef = Mid(A, c1 + 1, c2 - c1 - 1)
[COLOR=#ff0000]      On Error GoTo BadSheetOrRangeName[/COLOR]
      Set Sht = Sheets(Left(CelRef, InStr(CelRef, "!") - 1))
      Set R = Sht.Range(Mid(CelRef, InStr(CelRef, "!") + 1))
      On Error Resume Next
      i.ClearComments
      On Error GoTo 0
      i.AddComment
      i.Comment.Text Text:=R.Value
      i.Value = Left(A, c1 - 1)
    End If
  End If
[COLOR=#ff0000]BadSheetOrRangeName:[/COLOR]


    
End Sub

 

[Jeffrey Mahoney]

This is for updating all of them

Sub ChangeAllComments()  
  Dim A As String
  Dim CelRef As String
  Dim c1 As Integer
  Dim c2 As Integer
  Dim R As Range
  Dim Sht As Worksheet
  Dim Cel As Range
  
  For Each Cel In Range("D:D")
    A = Cel.Value
    If InStr(A, "{") > 0 And InStr(A, "}") > 0 And InStr(A, "!") > 0 Then
      c1 = InStr(A, "{")
      c2 = InStr(A, "}")
      CelRef = Mid(A, c1 + 1, c2 - c1 - 1)
      On Error GoTo BadSheetOrRangeName2
      Set Sht = Sheets(Left(CelRef, InStr(CelRef, "!") - 1))
      Set R = Sht.Range(Mid(CelRef, InStr(CelRef, "!") + 1))
      On Error Resume Next
      Cel.ClearComments
      On Error GoTo 0
      Cel.AddComment
      Cel.Comment.Text Text:=R.Value
    End If
  Next Cel
BadSheetOrRangeName2:
End Sub

 

[andrewb90]

Well, I wouldn't want users to add comments. Basically on the sheet "Notes" anytime someone updates values on column S I would want that value added as a note to to the same row # in Column D of the "Names" sheet. My challenge is the names sheet is protected, so I would need to un-protect and re-protect each time. Unless when protecting I can allow commenting, which I don't think...