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
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Sep 10, 2012 at 09:14 AM
Related:
- Event causing a popup comment to be saved
- Send popup message to another computer on network windows 10 - Guide
- Fortnite christmas event 2023 - Guide
- Save to pdf download - Download - Other
- Do instagram lives get saved ✓ - Instagram Forum
- Your comment goes against community standards - Facebook Forum
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
Jul 12, 2012 at 09:27 AM
Hi Drake,
You mean something like this?:
Best regards,
Trowa
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
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Aug 14, 2012 at 10:25 AM
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
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
dragon2300
Posts
8
Registration date
Wednesday July 11, 2012
Status
Member
Last seen
September 5, 2012
Aug 16, 2012 at 01:59 PM
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 :)
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 :)
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Aug 20, 2012 at 09:09 AM
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
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
dragon2300
Posts
8
Registration date
Wednesday July 11, 2012
Status
Member
Last seen
September 5, 2012
Aug 22, 2012 at 03:08 PM
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
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
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Aug 23, 2012 at 10:56 AM
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
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
dragon2300
Posts
8
Registration date
Wednesday July 11, 2012
Status
Member
Last seen
September 5, 2012
Aug 23, 2012 at 11:30 PM
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
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
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Aug 28, 2012 at 10:47 AM
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:
On the other hand you told me you want users to be forced to input a comment. In that case use this:
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:
Hope you like.
Best regards,
Trowa
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
dragon2300
Posts
8
Registration date
Wednesday July 11, 2012
Status
Member
Last seen
September 5, 2012
Sep 5, 2012 at 04:03 PM
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
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
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Sep 10, 2012 at 09:14 AM
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
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
Jul 12, 2012 at 09:45 AM
-Drake
Jul 12, 2012 at 03:53 PM
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?
Jul 17, 2012 at 08:51 AM
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:
Best regards,
Trowa
Aug 4, 2012 at 12:11 PM
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
Aug 4, 2012 at 12:25 PM
Thanks again!
-Drake