Excel VBA Introduction Part 20.2 - Application Events
ฝัง
- เผยแพร่เมื่อ 23 ก.ย. 2024
- If you'd like to help fund Wise Owl's conversion of tea and biscuits into quality training videos you can click this link www.wiseowl.co... to make a donation. Thanks for watching!
You can buy our Introduction to Excel VBA book here www.lulu.com/s...
By Andrew Gould
www.wiseowl.co.uk - In an earlier video in the series we've seen how to write event-handlers for workbooks and worksheets; this video teaches you how to access events of the Excel application itself! You'll learn the significance of the WithEvents keyword, a couple of the basic application events such as the new workbook event, and how to use a basic class module to keep your application event handlers neatly organised.
Visit www.wiseowl.co.uk for more online training resources in Microsoft Excel, SQL Server, Reporting Services, Analysis Services, Integration Services, ASP.NET, C#, Visual Basic, Microsoft Access, Microsoft PowerPoint, Microsoft Word, Microsoft Project, Microsoft Publisher, Microsoft Visio and more
Your tutorials are god sent.
Thanks so much for watching!
Very nice Andrew (and WiseOwlTutorials). I can't get enough of your lessons! I must admit, I'm addicted! Please keep them coming.
I'm working my way through this series in 2020, and it's absolutely brilliant. Thank you.
It is!
Even if I can not understand perfectly all what you explain because I do not fully understand English language, I never tired of listening to you because you're really a great teacher. You don't miss anything that can be important and explain it in a simple and effective way. Congratulations.
Thanks Andrew, easier to understand with naming conventions relevant to data-type e.g. class1, myVariable, myClass...
You're welcome, thanks for watching!
Finally some widescreen res LOL, love your video tutorials Andrew! Good job!
Your toots are just great!...keep them coming.
Hello, what if you add a shape (say a button from Insert/Shapes) can you create a worksheet event for when the user left-clicks the button? Or is this just done by the assign macro? Thanks! All the worksheet events shown in the dropdown don't fit.
Hi! Shapes have an OnAction property that you can use to assign a macro to the object programmatically docs.microsoft.com/en-us/office/vba/api/excel.shape.onaction
Shapes don't have events in the same way that ActiveX controls do as far as I know.
I have really enjoyed learning from your tutorials, but I am having trouble finding a solution to the following problem. I'm trying to add a custom shape (arrow) to my spreadsheet using code in the developer tool (VBA), but the position of the shape is dependent on the value in a cell. This value is derived from a range of preceding cells. If the value is greater than or less than a particular value, I want the arrow to be positioned alongside a scale, relative to the corresponding numbers on that scale (another inserted image). Can you help?
Hi Andrew, I have an userform with around 60 textboxes and comboboxes, which require validation once After Update event is triggered for each control. To avoid repeat myself writing subs for after update event for each control, I tried using class module withevents to solve this issue. However, I then realised that neither textbox nor combobox supports after update event. Is there any workaround? I spent a few hours googling it and asking chatgpt, still can't find anything with detailed explanation. Thanks.
Hi! I don't have an answer on this but it does indeed appear that the AfterUpdate event isn't supported when using WithEvents. There's a tantalising hint of a complicated solution at the end of this question and answer but no concrete information, sorry stackoverflow.com/questions/23995931/unable-to-capture-afterupdate-event-with-a-custom-event-handler
Hi Andrew really Enjoying excel VBA .. great Job...
tq.
Eventapp gives error "user defined type not defined". Does a certain reference need to be checked?
+WiseOwlTutorials I got it - was being lazy and didn't choose to rename the class module. ! Thanks.
Thank-you for the great video. I had a problem with creating a new workbook via Control +N that execute the macro. If i clicked on File on the ribbon and then click on the "New Blank Workbook" button the Open Workbook fires and the macro executes. But if i simply use the shortcut key Control + N, the macro does not execute. Can you explain why?
I am having issues with a code under the before close application event and was wondering if you could maybe help? If the user clicks "yes" on the message box prompt, the message box pops up one more time, and the workbook will close after the second click on "yes". If the user clicks "no" the second time, the Excel "want to save your changes" warning message appears. I tried using a class module instead, but then the message box prompt pops up a third time. I don't know how to get around it, or if there is a way to get around it (I only want the message box to appear once) I think the problem is, since I used the close workbook application if the user clicks yes on the message box, the code is running the before close application an additional time. The purpose of the code is to ensure the user saved the file before closing the workbook, but prevent the "want to save your changes" warning message from popping up (I've got a separate module that will save the workbook as a pdf if the user clicks a button, and another that prevents the user from manually saving the workbook without using the other module or without having a password). Here's the code:
Dim ButtonClicked As VbMsgBoxResult
Application.ScreenUpdating = False
ButtonClicked = MsgBox(prompt:="Did you remember to save this file before closing?", Buttons:=vbYesNo, Title:="Save")
If ButtonClicked = vbNo Then
MsgBox ("Click on the button to save the file before exiting.")
Cancel = True
ElseIf ButtonClicked = vbYes Then
ThisWorkbook.Close savechanges:=False
End If
Application.ScreenUpdating = True
Never mind. I just figured it out. If I change the code from ThisWorkbook.Close to Thisworkbook.Saved = True, then that solves my problem.
Very use full videos from wiseowl..
Hey Thanks for this little secret Ninja technique :)
You're welcome, thanks for watching!
Merry Christmas, neat video, thank you again....
Thanks Andrew
You're very welcome, Sayyad!
If we compare all of this with Java programming
Class Module is nothing else but a Constructor where we initialize out private attributes
NewWorkbook Open Event behaves like main method where we usually create new instance
of our class (at the moment we call constructor)
But in this example Class Module is default (no parameter) constructor , can we proceed some arguments from Event and use Parameter Class Module ?
Hi, the usual workaround to create a constructor in VBA is shown here stackoverflow.com/questions/15224113/pass-arguments-to-constructor-in-vba
I hope it helps!
@@WiseOwlTutorials thank you
**Excel bug can prevent Sub workbook_open() firing **
If you're the sort of person that uses workbook_open() in ThisWorkbook you might like to know that there seems to be an Excel bug, dating back to at least 2009, which can effectively stop workbook_open() firing (probably more correctly stopping the resulting code being executed). It's related to conditional formatting in the workbook. The solution is pretty easy. See the DrMarbuse controbution at stackoverflow.com/questions/11369762/workbook-open-sub-wont-run-when-i-open-the-workbook
I have filed a bug report with MS.
That's an excellent tip, Andrew! Thank you for taking the time to share it, and thank you for watching!
🙏🙏
Why should we use application event, I am not getting difference between this application event and event procedures
Hi do you have a VBA tutorial on copying/transferring data between workbooks?
I start fail to keep up with the learning process. This part is a bit difficult for me to understand.
hello everyone, wanted to ask you, why i cant use just workbook event? what is the difference between workbook event & application event? couldnt get why it is better to use application event rather than workbook/worksheet event?
Hi! The Application object has some events which aren't available to the Workbook or Worksheet objects, such as the NewWorkbook event shown in this video. I hope that helps!
@@WiseOwlTutorials thanks, great videos guys , do you teach office scripts also?
@@chahineatallah2636 Hi, no sorry, we don't currently teach Office Scripts.
Merci
thank you
hi wise owl, i just thought i should apologize because i remember when i saw you were on excel 2010 i believe in one of you videos and then i said in my head "he's still on excel 2010"? i believe i did this in pride as i was on excel 2013 at the time and i believe this is wrong, so im sorry.
How to copy and paste like you do without using mouse? in 8.31
That was "Shift + Up Arrow", "Ctrl+C", Down Arrow, Up Arrow, "Ctrl+V".
It simply highlighted from the current cursor position all the way back to the cursor position after Up Arrow was pressed.
If you hold Shift down while moving the cursor Windows will highlight/select.
although your tutorials are good but last few ones and specially this short as you said, went over my head and completely lost why are we here, may be this time you wrapped up quite fast. may be i have to go over and over again.
@@WiseOwlTutorials Exactly! Knowing the capabilities of a language or a software is very very important... even if you don't remember the syntax or the exact how to... You can alway refer to the internet and great tutorials like this to relearn or refresh your mind on what you need... Nobody remembers everything.. It is a vast field and it is impossible to know or remember everything (with detail) you've seen or used at some point... It's beyond brain capability I think.. At least mine... :P ... And knowing its existence its a "shortcut" or less information for your brain, that is able to handle and refer to...
Nothing can be seen. You should have made the screen look clear to the viewers.