Event causing a popup comment to be saved

[Solved/Closed]
Report
-
Posts
2783
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
August 3, 2021
-
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 replies

Posts
2783
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
August 3, 2021
468
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
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
2783
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
August 3, 2021
468
Hi Drake,

The point of kioskea (imo) is to record the help of individuals so that other's (at a later time) can have benefit from it as well.

And please don't feel like you are bugging anyone. My knowledge of VBA comes solely from Kioskea and google searches. Answering questions helps me practice my skills and to be creative about situations I don't put myself in, which broaden my horizen.

Back to your query.
The line: Private Sub Worksheet_Change(ByVal Target As Range)
Will launch the code whenever a change is made to the sheet.
Since you only need the code to be launched when specific cells change we tell excel to only look at specific cells:
If Intersect(Target, Range("A1:B1")) Is Nothing Then Exit Sub

A change according to excel is a manual change made to a cell.
When a cell recalculates it isn't a change.

So in the intersect part you will have to input the range which affect the calculations.
In your case that would be "A1:B6".

Hopefully this wil clarify a little bit of what is going on.

In you previous message you wrote:
"i dont want MyMessage to run everytime just one of the values in the original range is changed"
Have you thought about any criteria for this?
Do you want on added question which asks you to replace the comment or not?

Kind regards,
Trowa
Posts
8
Registration date
Wednesday July 11, 2012
Status
Member
Last seen
September 5, 2012

Hi Trowa,

I understand, and appreciate the same information :)

the problem i am having is this:

Cells a1:a6 are guest counts
cell a7 is the total guest count

cells a9:a16 are labor hours
cell a17 is total hours
and cell a19 is a formula that divides a7 by a17

if a19 is less than 3.2 i want to run my message

a7 and a19 are formulas

cells a1:a6 are all filled in every day

cells a9:a16 my have some cells not filled in because there is no meeting hours or over time ect.

as the information is entered one cell at a time i am trying to have the MyMessage run only after all the relevant information is inputed, without the user needing to press a button.

Although it looks to me that I may have to have a button...

Any advice would be much appreciated, i know this is a head cracker...

Thanks in advance!
-Drake
Posts
8
Registration date
Wednesday July 11, 2012
Status
Member
Last seen
September 5, 2012

please keep in mind the cells are for reference only, the cells involved are much longer range, and as i said before they are repeated

I.e. a1:a6
b1:b6
c1:c6
ect.
Posts
8
Registration date
Wednesday July 11, 2012
Status
Member
Last seen
September 5, 2012

Is there a way to run the
If Intersect(Target, Range("B46,B50")) Is Nothing Then Exit Sub

as If Intersect(Target, Range("B46 OR B50")) Is Nothing Then Exit Sub?
Posts
2783
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
August 3, 2021
468
The correct way to write it would be:
If Intersect(Target, Range("B46", "B50")) Is Nothing Then Exit Sub
Posts
2783
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
August 3, 2021
468
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
Posts
8
Registration date
Wednesday July 11, 2012
Status
Member
Last seen
September 5, 2012

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 :)
Posts
2783
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
August 3, 2021
468
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
Posts
8
Registration date
Wednesday July 11, 2012
Status
Member
Last seen
September 5, 2012

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
Posts
2783
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
August 3, 2021
468
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
Posts
8
Registration date
Wednesday July 11, 2012
Status
Member
Last seen
September 5, 2012

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
Posts
2783
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
August 3, 2021
468
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
Posts
8
Registration date
Wednesday July 11, 2012
Status
Member
Last seen
September 5, 2012

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
Posts
2783
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
August 3, 2021
468
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

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!