Event causing a popup comment to be saved

Solved/Closed
Drake - Jul 11, 2012 at 09:10 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Sep 10, 2012 at 09:14 AM
Hello,
I am trying to create an even within multiple cells that would ask for a comment if certain conditions are met.

For instance:

If c1 < 3.2 then [run event]

I want the event to be a comment popup that would save to a comment on the cell.

I have no idea how i would accomplish this however. an imbedded If statement using macro's would be my simplest idea of a solution to this (I need the original value to be user imputed and left where it is, with a comment added), although i have no idea how to start a popup comment box without user input.

Regards

4 responses

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Jul 12, 2012 at 09:27 AM
Hi Drake,

You mean something like this?:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyMessage As String
If Intersect(Target, Range("C1")) 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
1
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
0
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?
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Jul 17, 2012 at 08:51 AM
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
0
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
0
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
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Aug 14, 2012 at 10:25 AM
Hi Drake,

Well basically it's up to you when you want to run the code.

1. You can create a button (do you know how?) which users must press when they are done inputting their data, the code will then check if the value (A19) is below 3.2 and if necessary ask for comment input.

2. Click on A19. Code will check value and ask for comment input if necessary.

3. Before the file is saved or closed the value of A19 can be checked.

4. You think of something else and would like to know if it's possible.

So what are your thought's saying?

Best regards,
Trowa
0
dragon2300 Posts 8 Registration date Wednesday July 11, 2012 Status Member Last seen September 5, 2012
Aug 16, 2012 at 01:59 PM
Hey Trowa,

I think the simplest solution, and the one least likely to be avoided by parties involved would be to check the value each time the sheet is closed, although when doing that (because it will be checking every day at the same time) i would have to make sure it's not asking for input on days that dont have information inputted yet, or on days that already have a comment set up. 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? I have a copy of my sheet here:

https://www.excelforum.com/excel-programming-vba-macros/851801-copy-row-1-from-worksheet-1-to-row-1-in-worksheet-2-a.html#post2890435

The solution to my second problem has been found, but the first (the one here) is so close i can smell it :P

It's a rough copy, the true copy is far more extensive, but i'm confident i can adapt the code into other sheets, i just need to understand it :)

I hope this sheet makes it clearer what i'm trying to do, i'm sorry for all the confusion in my posts, i do hope all this helps someone else as well :)
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Aug 20, 2012 at 09:09 AM
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
0
dragon2300 Posts 8 Registration date Wednesday July 11, 2012 Status Member Last seen September 5, 2012
Aug 22, 2012 at 03:08 PM
Sorry, here's the file:

http://speedy.sh/dXmm9/Copy-of-index-match-based-on-dropdown.xlsx

Basically what i was hoping to do is to keep the current format of popup, as in force a comment, I don't want the user to be able to choose to comment or not, but only for days that have information in it, but does not already have a comment.

I.e.
If ([Day1] {cell} has information) AND ([Day1] {cell2} has no comment) THEN Run {comment input} popup

But the code needs to check days 1-7 simultaniously, and if there are days that do not have information inputed, i dont want the popup to occur for that day

I also need to run similar code on the same worksheet that would check different values as well, so potentially there would be 3-5 popups for a single day

The code can run either when they close the sheet, or when they save the sheet, or both, it doesnt matter, it's just a catalyst that would start the process of checking all the information.

I'm starting to get the idea of what needs to be done, i just dont know the right code myself to do it.

I really appreciate all the help here, i know this has been a trial :P but it's pretty much like a walkthrough for several different types of sheets now lol

thanks again Trowa! i hope the attached sheet makes this more clear

-Drake
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Aug 23, 2012 at 10:56 AM
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
0
dragon2300 Posts 8 Registration date Wednesday July 11, 2012 Status Member Last seen September 5, 2012
Aug 23, 2012 at 11:30 PM
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
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Aug 28, 2012 at 10:47 AM
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
0
dragon2300 Posts 8 Registration date Wednesday July 11, 2012 Status Member Last seen September 5, 2012
Sep 5, 2012 at 04:03 PM
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
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Sep 10, 2012 at 09:14 AM
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
0