-1

I need help with two items for my project. I've searched but can only find posts about more complex questions than mine or others that I don't understand.

  1. I'm trying to get the comments that I enter on one sheet to appear on a cleaner sheet being sheet 1. Image of what I would like to do

  2. I would like to make an automatic email alert that sends me info from a cell to my email by a certain date and time. Here is an image of what I am trying to do

Thank you for looking!

EDIT 6/22/18

I didn't know how to add an image to the comment so adding here. @ J.Doe I tried my best changing what I could. Doesn't seem to work for me. I'm not to familiar with VBA so just took my best shot at it. I added an extra image that shows what the actual cells are and sheet names. To help make my question clearer

  Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("N4:N48")) Is Nothing And Target.Count = 1 Then
        Dim P1 As Range: Set P1 = Sheets("Solutions").Cells(Target.Row, 1)
        If Not P1.Comment Is Nothing Then
            If Target.Value = "" Then
                P1.Comment.Delete
            Else
                P1.Comment.Text Format(Target, "@")
            End If
        Else
            P1.AddComment Format(Target, "@")
        End If
    End If
End Sub

sheets with correct names and cells

Community
  • 1
  • 1
DavidWExcel
  • 1
  • 1
  • 1
    A search on here would give you : https://stackoverflow.com/a/31191030/4961700 – Solar Mike Jun 22 '18 at 06:22
  • Sorry it's a bit confusing, in your first image you say you want to roll over column A. But in the third image you use Cell F42-45. The code is working fine, just putting the comments sheets "Solutions", column A. The last `1` in: `Set P1 = Sheets("Solutions").Cells(Target.Row, 1)` is the column index than you can change depending on where you want the comments to be added. – J.Doe Jun 25 '18 at 00:44
  • You are asking two separate questions as one question. Consider moving point 2 into a new question. – Zev Spitz Jun 25 '18 at 04:40

1 Answers1

0

For your first point:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("N:N")) Is Nothing And Target.Count = 1 Then
        Dim P1 As Range: Set P1 = Sheets(1).Cells(Target.Row, 1)
        If Not P1.Comment Is Nothing Then
            If Target.Value = "" Then
                P1.Comment.Delete
            Else
                P1.Comment.Text Format(Target, "@")
            End If
        Else
            P1.AddComment Format(Target, "@")
        End If
    End If
End Sub

To be placed directly in your sheet2 code.

Adapt Sheets(1).Cells(Target.Row, 1) to the sheet that need the "excel comment"

This will not deal with multiple cell changes at once (e.g. you delete all comments in sheet2 range N2:N11)

Your point 2 sound very tricky...

J.Doe
  • 596
  • 1
  • 3
  • 7
  • Thank You J.Doe! I tried changing the code but I think I missed something. I added a picture of the actual cell location on the original post and sheet names. I'd appreciate it if you could see if this is what I was supposed to do. – DavidWExcel Jun 22 '18 at 15:57