How to create a pop-up notification for two different conditions at the same time
Hi everyone!!!
I have a really important question that I can not find a solution online.
I want to create a pop-up notification with the following aspects:
On the end of the month I want a pop-up notification only on customers that haven´t paid the rent.
I do not mind to use VBA.
Thanks
Hi miguiax
Welcome to the Excel Forum.
_1 ) Please take some time to read the Forum Rules here
http://www.excelforum.com/forum-rule...rum-rules.html
http://www.excelforum.com/forums-rules/
_2) And once you have done that please give us more detailed information if you want anything other than general Tips. ( and avoid duplicate posting please ! )
http://www.excelforum.com/showthread...09#post4494031
Thanks
Alan
( Duplicated post:
http://www.excelforum.com/excel-gene...same-time.html )
Last edited by Doc.AElstein; 10-22-2016 at 06:27 AM.
'_- Google first, like this _ site:ExcelForum.com Gamut
Use Code Tags: Highlight code; click on the # icon above,
Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE
http://www.excelforum.com/the-water-...ml#post4109080
https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
http://excelmatters.com/excel-forums/ ( Scrolll down to bottom )
Why not use conditional formatting to highlight your condition?
Cuz with highlight it doesn't pop-up a notification and since there are 12 columns with (paid/unpaid status) and 100 customers there is a chance that a customer pays that their status is not changed.Originally Posted by JohnTopley
same with a pop-up as you doing the same condition test.there is a chance that a customer pays that their status is not changed.
Or set a flag based on "unpaid" and "end-of month" and filter on flag.
As previously requested, post a file showing expected results.
To Attach a File:
1. Click "Go Advanced"
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.
Hi miguiax
The following you Posted here
http://www.excelforum.com/showthread...t=#post4507083
One way then is to have a code in the ThisWorkbook Code module, a Workbook_Open() Type code
That would code would start every time you open Excel.
Depending on how your Workbook looks , a code would check ( possibly only when opened at the start or end of a month ) various Cells to determine Customers that have not paid ( and / or varoius other criteria if necerssary) , then it is fairly straight forward to put some reference to the customers in a string ( their name or whatever ) and display that in a message box
Alan
Last edited by Doc.AElstein; 10-22-2016 at 09:04 AM.
Could you show me how to do it? I haven't use VBA for a really long period so I don't know to do it
Tutos.jpg
The red and yellow points are the one that haven´t paid
.. a file please, not image: we are not going to type in your data.post a file showing expected results
Hi again miguiax
We realise you are keen to get an answer. But you are making it very difficult for us to help you
You either have still not read the Rules, and Links I gave, or have missed important points.
Images are very difficult for us to work with. I can hardly see any detail in that Image you posted
We need ( preferably reduced size sample) data that we can work with, such as in a uploaded workbook
Alan
Ok alan here is my workbook
Livro1.xlsx
Thanks for your help
Hi miguiax
It is not clear to me exactly what you want to happen and on what criteria it that action should be based. (especially as I do not speak Portuguese
Please remember you know the project inside and out, so you would understand almost any explanation.
But you must make it crystal clear to someone encountering it for the first time and so not familiar with it
_..........................
How about I suggest a solution?
How does this sound?
When the workbook is opened, the current date is checked.
If the date is Not the last day of the Month then no action is taken.
If the date is the last day of the Month then the following happens:
The column for the current Month is searched through.
If a 1 appears then the corresponding “Nome do inquilino “ is added to a list of customers that haven´t paid, and finally a Pop up appears listing those Customers that haven´t paid yet.
Alan
Last edited by Doc.AElstein; 10-22-2016 at 12:24 PM.
It sounds great !!!! =) That is my idea. I would only add a point.
I would like to have only the pop-up listing those customers without a list of customers that haven´t paid. I don't know if it is possible.
Thanks
Hi
Sorry, I do not understand this ??
??
That makes no sense ??
_..........................
What should the Pop up show?
Which customers should be included in the Pop up ?
Sorry my mistake
The pop up should show the customers that haven´t paid until the end of that month
OK, I will look at this later, and post a reply later Today or Tomorrow
Alan
Ok. Thanks I will for it 😉
Just for demonstration: is this what you require?
How are going to trigger this if workbook is not opened on last day of a month (e.g. it is a weekend)?
Please Login or Register to view this content.
Hi miguiax
_ I have a couple of codes for you also
_ Both are of the Workbook_Open() type
_ they must be placed in the ThisWorkbook code Module
This is done as follows:
Alt + F11 to get VB Editor Window, then double click on ThisWorkbook in VBA Project Window
ThisWorkbookCodeModule.JPG
Paste Code in the large Workbook Open Code Window
_.........................
Regarding, what JohnTopley said, my coding assumes for now that you open the workbook on the last day of the Month **
Code 1 will give a result like this, using a simple message box:
MessageBoxCode1.JPG
http://imgur.com/y3MUAfr
Attachment 485977
Code 2 is somewhat more advanced, allowing for more detail as well as for much larger data
This may be somewhat advanced for you. But I include it anyway.
Code 2 will give a result like this, using a UserForm:
UserFormCode2.JPG
http://imgur.com/Fl0jhja
Attachment 485978
Both codes assume that you open on the last day of the month if you want the code to trigger**
** For testing in both codes is a test line
This simulates the situation when you are at the last day of the Month. This causes the code to trigger by every opening.Please Login or Register to view this content.
If you ‘Rem out this code line thus:_..
_.. then the code should only trigger on the last day of the MonthPlease Login or Register to view this content.
Both codes , if triggered , examine the last Month column data Range
In your uploaded example, this is this range that is examined
Using Excel 2007 32 bit
Worksheet: Data Control Sheet
Row\Col
_............
Each code I explain briefly in the next two posts:
Code 1 is here:
http://www.excelforum.com/showthread...14#post4507414
You simply need to copy it to the ThisWorkbook Code Module.
_ Highlight Code here
http://www.excelforum.com/showthread...14#post4507414
And copy to Clipboard ( Crtl + C )
Alt + F11 to get VB Editor Window, then double click on ThisWorkbook in VBA Project Window
ThisWorkbookCodeModule.JPG
Attachment 485981
_Click in large Empty Code Window
_Paste Code in the large Workbook Open Code Window ( Ctrl + V )
_Save File with required .xlsm extension
SaveAs12.jpg
http://imgur.com/xrx2LHI
Attachment 485983
Save as .xlsm ( With macros )
SaveWithMacros.jpg
http://imgur.com/LlUjBQo
Attachment 485984
_Close File
_Open File and Enable macros on opening
EnableMacroOnOpening.jpg
http://imgur.com/3jqtbZv
_..............................
The code should trigger, ( unless you have ‘Rem ed out Line 150 and it is not the last day of the month )
'Rem Ref
' http://www.excelforum.com/excel-prog...another-2.html
' http://www.excelforum.com/excel-prog...n-on-open.html
' http://www.excelforum.com/excel-prog...orm-issue.html
' http://www.excelfox.com/forum/showth...=9985#post9985
' http://www.snb-vba.eu/VBA_Arraylist_en.html
' http://www.snb-vba.eu/VBA_Arraylist_en.html#L_11.3
' http://www.excelforum.com/showthread...=4#post4502593
' http://www.excelforum.com/excel-prog...ml#post4507157
' http://www.excelfox.com/forum/showth...=9985#post9985
' http://www.excelfox.com/forum/showth...=9991#post9991
' http://www.excelfox.com/forum/showth...=9985#post9985
Code 2 is somewhat more involved .
You need both the code which should be copied to a the ThisWorkbook Code module as explained previously. ( Delete any previous code in the ThisWorkbook Code Module ( or ‘Rem it out ) )
_......
The Code is here:
http://www.excelforum.com/showthread...49#post4507449
Copy to a the ThisWorkbook Code module as explained previously. ( Delete any previous code in the ThisWorkbook Code Module ( or ‘Rem it out ) )
_......................
But you also need the UserForm Class Module, Elstein
You will need to download this from here, _..
https://app.box.com/s/h9c3bz4cm928s3q45ifl283n4lfcbnsk
_.... ( and remember where you save it !!! )
Then you will need to import it into your File:
UserFormImportieren.jpg
http://imgur.com/lCdHgkV
Attachment 485994
then it should show in like this
UserFormImported.jpg
http://imgur.com/4WAzZzH
Attachment 485993
Save your File as .xlsm with macros
Close File
Open File
The code should trigger, ( unless you have ‘Rem ed out Line 150 and it is not the last day of the month )
See how you get on !!
Alan
'Rem Ref
' http://www.excelforum.com/excel-prog...another-2.html
' http://www.excelforum.com/excel-prog...n-on-open.html
' http://www.excelforum.com/excel-prog...orm-issue.html
' http://www.excelfox.com/forum/showth...=9985#post9985
' http://www.snb-vba.eu/VBA_Arraylist_en.html
' http://www.snb-vba.eu/VBA_Arraylist_en.html#L_11.3
' http://www.excelforum.com/showthread...=4#post4502593
' http://www.excelforum.com/excel-prog...ml#post4507157
' http://www.excelfox.com/forum/showth...=9985#post9985
' http://www.excelfox.com/forum/showth...=9991#post9991
' http://www.excelfox.com/forum/showth...=9985#post9985
@Doc.AelStein
Re your feedback to me: I appreciate the need for "Workbook.Open" but I am always "intrigued" by the month-end testing.
At least with RUN button, you can run anytime you want!!!
@ JohnTopley
Hi John,
Yep, I am not a great fan of Event Codes myself. I find the Worksheet_Change sometimes useful. The Workbooks_Open I only occasionally use.
I prefer to try to “work with” computers as much as possible , automating as little as possible.
I like to “run” things as and when I need them.
By automating as little much as possible I “feel” I am still a bit in control.
But I am no computer professional
As for anything to do with dates, I try to avoid them as much as possible. - I always seem to get problems with them changing their format, - and / or Excel mixing up comer and points, decimal and thousand separators etc.. etc.. – just down to experience I suppose, which I do not really have yet
Alan
( we will probably find the OP wants something totally different to what we have guessed anyway !!)
Hi!!!!
I have tried to use replicate the document "Livro 1" That you posted here but I was not able to do it. Could you replicate in this workbook! Pls
Excel das Rendas 2.xlsx
On the data control sheet
Last edited by miguiax; 10-24-2016 at 06:50 AM.
Hi miguiax
Who are you talkng to ?I have tried to use replicate the document "Livro 1" That you posted here but I was not able to do it. Could you replicate in this workbook! Pls
Are you referring to the code from JohnTopley ?
http://www.excelforum.com/showthread...73#post4507382
Sorry my mistake. Ya I was referring to the code from JohnTopley
See attached. Code included
Code is a general module
Use Alt+F11 to Open VBA editor
Once again thanks a lot
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks