Excel VBA Introduction Part 20.2 - Application Events

By Andrew Gould

[donbogdala]

Very nice Andrew (and WiseOwlTutorials). I can't get enough of your lessons! I must admit, I'm addicted! Please keep them coming.

[maurocastagnera]

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.

[remarcsd]

I'm working my way through this series in 2020, and it's absolutely brilliant. Thank you.

[dalskiBo]

Thanks Andrew, easier to understand with naming conventions relevant to data-type e.g. class1, myVariable, myClass...

[Youtubers]

Your toots are just great!...keep them coming.

[elmartusa]

Finally some widescreen res LOL, love your video tutorials Andrew! Good job!

[PADRONJORGEL]

Merry Christmas, neat video, thank you again....

[knowmore]

Hi Andrew really Enjoying excel VBA .. great Job...
tq.

[jordanjohnson]

Hi do you have a VBA tutorial on copying/transferring data between workbooks?

[joem]

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?

[grantnalder]

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?

[KM-comx]

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.

[lastdance]

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.

[anootoshsarkar]

Hey Thanks for this little secret Ninja technique :)

[bhoopeshsharma]

🙏🙏
Why should we use application event, I am not getting difference between this application event and event procedures

[malinmalindic]

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 ?

[coorsteksarah]

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

[andrewhowe]

**Excel bug can prevent Sub workbook_open() firing **

I have filed a bug report with MS.

[LaoWang-je]

I start fail to keep up with the learning process. This part is a bit difficult for me to understand.

[chahineatallah]

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?