Event causing a popup comment to be saved

Hi Trowa, this looks like exactly what i need! I'll give it a shot this afternoon and let you know the results :) thanks a million!

-Drake

Hello again Trowa,

The code you game me works perfectly for user input, however now i need it to analize the result of two other cells for the trigger.

I.E.
cell a1 is 3
Cell B1 is 2
I have code in Cell C1 that takes B1 and Devides is by A1 (=B1/A1)

So i need the message to pop up when the code is C1 gives my an answer,

I.E. if the code in C1 returns a number < 3.2 = [message popup]

can this be done? or do i need to figure out a different way of doing this?

Hi Drake,

Good to see we are on the right track.

The code needs to reevaluate the value in C1 when a change is made in either A1 or B1.
Here is the adjusted code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyMessage As String
If Intersect(Target, Range("A1:B1")) Is Nothing Then Exit Sub
If Range("C1") < 3.2 Then
MyMessage = InputBox("Please input your comment")
With Range("C1")
    .ClearComments
    .AddComment
    .Comment.Visible = False
    .Comment.Text Text:=MyMessage
End With
End If
End Sub

Best regards,
Trowa

Hello again Trowa,

The code above looks sound, however i can't seem to make it work, i think it's a little more complicated. i was wondering if it's possible to get ahold of you directly and chat about what i'm doing, as you seem to know what your talking about, i would also very much like to pick your brain as to the dynamics and use of the code you have given me so i can adapt it to use for other parts of my sheet as well. if not, thats ok, i very much appreciate the help thus far.

The problem i'm having now (i think i've worked out what it is, just not how to solve it) is that the original values i am using (which are not in succession) are results of formulas as well.

so a1:a6 = a7
and b1:b6 = b7

so a7 / b7 = c1

If c1 < 3.2 then run MyMessage

so for your formula, what i've done is this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyMessage As String
If Intersect(Target, Range("A7:B7")) Is Nothing Then Exit Sub
If Range("C1") < 3.2 Then
MyMessage = InputBox("Please input your comment")
With Range("C1")
.ClearComments
.AddComment
.Comment.Visible = False
.Comment.Text Text:=MyMessage
End With
End If
End Sub

But it does not run when the numbers in a7 or b7 change, so i'm thinking it must be used with a user input cell, which is difficult to do as some cells are not always going to have values in them, and i dont want MyMessage to run everytime just one of the values in the original range is changed...

if you want to have a look at my sheet, i would be more than happy to send you a copy, any idea's would be very much appreciated.

Thanks in advance! i hope to hear from you soon

-Drake

P.s. I am going to need to repeat this formula for 7 seperate totals using completely different cells on the same worksheet, all using the same sort of set up as that example I gave you. as well as 5 other sets of 7 results with different qualifiers. Thats why i want to know the dynamics behind the formula, so i dont have to keep bugging you guys :)

Thanks again!
-Drake

Hi Drake,

You need to be a member of excelforum to be able to view the uploaded file(s); which I'm not.

You could upload your file using www.speedyshare.com.

When closing the sheet a question could pop up: "Would you like to update the comment in cell A19?"
or
"Did you made any significant changes?"

Even multiple pop ups after each other is a possibility. This to check wether it's needed to change the comment.

Don't know exactly what you mean by:
"is it possible to use the code repeatedly for eah day, or should each day be coded seperately? from my research i've found that only one code instance can be used at one time in one sheet?"
Guess you have other codes in your file already which you wanted to show me through excelforum.

Best regards,
Trowa

Things definitely got clearer, Drake.

What I'm working on now is to check the values in row 65 column B to H. If they have changed since opening the file then add comments to the respective cell(s) only if there is no comment present and the value is below 3.2.

Let me know if I am on the right track.

Unfortunately I run into some errors and need some more time.

I will check back next week.

Best regards,
Trowa

Hey Trowa,

That sounds great, just to make sure, if nothing has been changed in the cell then it's not going to ask for a comment in that cell right?

Also, can that code be adapted to work with the other sections that need to be checked as well?

thanks again!

Drake

Alright Drake, I think I got what you are looking for. Although you kind of confused me with "the other sections" part. But I'm sure you will let me know if this works out or not.

Personally I found it annoying not being able to cancel the comment. So when users don't input a comment they will get a popup message telling them to input a comment. This reminder popup has a cancel button which will ignore the comment for that cell. If you want this use this:

   MyMessage = InputBox("Please input your comment for Column B")
        If MyMessage = vbNullString Then
        y = MsgBox("You forgot to input your comment, please try again.", vbOKCancel)
        If y = vbCancel Then GoTo Set2

On the other hand you told me you want users to be forced to input a comment. In that case use this:

    MyMessage = InputBox("Please input your comment for Column C")
        If MyMessage = vbNullString Then
        MsgBox "You forgot to input your comment, please try again."

So apply either structure to the rest of the code.
Just want to give you some options.

NOTE : The code will check values when the workbook is opened. When you first copy/paste the code, these values are not checked yet. When saving the rest of the code will be activated and produce indesirable results.
The safest way to implement the code is to first disable macro's (https://support.microsoft.com/en-us/office/change-macro-security-settings-in-excel-a97c09d2-c082-46b8-b19f-e8621e8fe373?ocmsassetid=hp010096919&correlationid=eb26089d-0f90-4bc7-a6ae-7a67fe878c21&ui=en-us&rs=en-us&ad=us then implement the code, save and close your workbook. Now enable macro's, open workbook and test the macro in as many ways as possible.

Here is the code which needs to be pasted under ThisWorkbook:

Dim MyRange As Range
Dim x1, x2, x3, x4, x5, x6, x7 As Currency
Dim MyMessage As String
Dim MyComment As Object

Private Sub Workbook_Open()
x1 = Sheets("Week 1 (2)").Range("B65").Value
x2 = Sheets("Week 1 (2)").Range("C65").Value
x3 = Sheets("Week 1 (2)").Range("D65").Value
x4 = Sheets("Week 1 (2)").Range("E65").Value
x5 = Sheets("Week 1 (2)").Range("F65").Value
x6 = Sheets("Week 1 (2)").Range("G65").Value
x7 = Sheets("Week 1 (2)").Range("H65").Value
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Sheets("Week 1 (2)").Activate

Set MyRange = Range("B65")
If x1 <> MyRange.Value And MyRange.Value < 3.2 Then
    Set MyComment = MyRange.Comment
    If Not MyComment Is Nothing Then GoTo Set2
MM1:
    MyMessage = InputBox("Please input your comment for Column B")
        If MyMessage = vbNullString Then
        y = MsgBox("You forgot to input your comment, please try again.", vbOKCancel)
        If y = vbCancel Then GoTo Set2
        GoTo MM1
        End If
    With MyRange
    .AddComment
    .Comment.Text Text:=MyMessage
    End With
End If

Set2:
Set MyRange = Range("C65")
If x2 <> MyRange.Value And MyRange.Value < 3.2 Then
Set MyComment = MyRange.Comment
    If Not MyComment Is Nothing Then GoTo Set3
MM2:
    MyMessage = InputBox("Please input your comment for Column C")
        If MyMessage = vbNullString Then
        MsgBox "You forgot to input your comment, please try again."
        GoTo MM2
        End If
    With MyRange
    .AddComment
    .Comment.Text Text:=MyMessage
    End With
End If

Set3:
Set MyRange = Range("D65")
If x3 <> MyRange.Value And MyRange.Value < 3.2 Then
Set MyComment = MyRange.Comment
    If Not MyComment Is Nothing Then GoTo Set4
MM3:
    MyMessage = InputBox("Please input your comment for Column D")
        If MyMessage = vbNullString Then
        MsgBox "You forgot to input your comment, please try again."
        GoTo MM3
        End If
    With MyRange
    .AddComment
    .Comment.Text Text:=MyMessage
    End With
End If

Set4:
Set MyRange = Range("E65")
If x4 <> MyRange.Value And MyRange.Value < 3.2 Then
Set MyComment = MyRange.Comment
    If Not MyComment Is Nothing Then GoTo Set5
MM4:
    MyMessage = InputBox("Please input your comment for Column E")
        If MyMessage = vbNullString Then
        MsgBox "You forgot to input your comment, please try again."
        GoTo MM4
        End If
    With MyRange
    .AddComment
    .Comment.Text Text:=MyMessage
    End With
End If

Set5:
Set MyRange = Range("F65")
If x5 <> MyRange.Value And MyRange.Value < 3.2 Then
Set MyComment = MyRange.Comment
    If Not MyComment Is Nothing Then GoTo Set6
MM5:
    MyMessage = InputBox("Please input your comment for Column F")
        If MyMessage = vbNullString Then
        MsgBox "You forgot to input your comment, please try again."
        GoTo MM5
        End If
    With MyRange
    .AddComment
    .Comment.Text Text:=MyMessage
    End With
End If

Set6:
Set MyRange = Range("G65")
If x6 <> MyRange.Value And MyRange.Value < 3.2 Then
Set MyComment = MyRange.Comment
    If Not MyComment Is Nothing Then GoTo Set7
MM6:
    MyMessage = InputBox("Please input your comment for Column G")
        If MyMessage = vbNullString Then
        MsgBox "You forgot to input your comment, please try again."
        GoTo MM6
        End If
    With MyRange
    .AddComment
    .Comment.Text Text:=MyMessage
    End With
End If

Set7:
Set MyRange = Range("H65")
If x7 <> MyRange.Value And MyRange.Value < 3.2 Then
Set MyComment = MyRange.Comment
    If Not MyComment Is Nothing Then Exit Sub
MM7:
    MyMessage = InputBox("Please input your comment for Column H")
        If MyMessage = vbNullString Then
        MsgBox "You forgot to input your comment, please try again."
        GoTo MM7
        End If
    With MyRange
    .AddComment
    .Comment.Text Text:=MyMessage
    End With
End If

End Sub

Hope you like.

Best regards,
Trowa

Hi trowa, this looks to be exactly what i need to do, and i even understand the code! amazing as that is :P

Problem is, i can't get it to work :P i have changed the sheet names to "Week 1" as that is the sheet it's working on, as well as changed that cell it's checking (it's now on B66 instead of B65),

From what i can tell, it should run just fine, i wonder if perhaps i have something set wrong? i disabled macro's before i set up the code, saved it, and closed the program, then enabled macro's and saved it again, and closed and reopenned the program, but no matter what variables i input, when i hit the save button (or close the workbook) no popup...

Is there something i could try?

thanks again!
Drake

Hi Drake,

Good to hear you are getting the hang of VBA.

All I can tell you is that the code works for me, but that is no help to you.

Can you post your workbook with code (carefull with personal info) so I can check what could be the problem? If you do can you make sure the extention is .xls (Save as... Excel 2003).

Best regards,
Trowa