Event handlers

Loading...

Del curso dictado por University of Colorado Boulder

Excel/VBA for Creative Problem Solving, Part 2

201 calificaciones

University of Colorado Boulder

Excel/VBA for Creative Problem Solving, Part 2

201 calificaciones

Curso 2 de 3 en SpecializationExcel/VBA for Creative Problem Solving

"Excel/VBA for Creative Problem Solving, Part 2" builds off of knowledge and skills obtained in "Excel/VBA for Creative Problem Solving, Part 1" and is aimed at learners who are seeking to augment, expand, optimize, and increase the efficiency of their Excel spreadsheet skills by tapping into the powerful programming, automation, and customization capabilities available with Visual Basic for Applications (VBA). In Part 2 of the course, learners will: 1) learn how to work with arrays and import/export arrays from/to Excel using VBA code; 2) learn how to work with text strings and write data to .txt files and import information from .txt files; 3) automate the import, modification, and consolidation of information from multiple worksheets into a central worksheet as well as the import of information from multiple workbooks to a central workbook; and 4) gain experience with creating professional user forms to interface with the user, perform advanced calculations, and manipulate data on the spreadsheet. Learners who have a foundational understanding of VBA code and programming structures can jump right into Part 2 of the course without taking Part 1 and use the screencasts in Part 1 as reference. Each module will introduce foundational and broad problems inspired by situations that you might encounter in the real world. To pass each module, you'll need to pass a mastery quiz and complete a problem solving assignment. This course is unique in that the weekly assignments are completed in-application (i.e., on your own computer in Excel), providing you with valuable hands-on training.

De la lección

User forms and advanced user input/output

In Week 4, you will learn about advanced input and message boxes and event handlers. You will learn how to interface with the user in a professional manner using user forms that validate input and prevent the "debug" window and VBE from appearing. You will also learn how to place combo boxes on user forms and populate those combo boxes with information on the spreadsheet. Finally, you will learn how to put it all together and implement a solving technique (bisection method) to solve a real world problem in a user form. Week 4 is concluded with a quiz, which unlocks Assignment 4.

Advanced input boxes7:32

Advanced message boxes6:53

Event handlers4:33

Introduction to user forms3:07

Creating your first user form10:35

Example: Fuel efficiency user form7:40

Example: Tank volume user form6:08

Dim'ming (or not Dim'ming) variables in user forms1:59

Input validation in user forms6:16

Introduction to combo boxes, Part 15:41

Introduction to combo boxes, Part 26:07

Example: Periodic table user form8:05

(OPTIONAL) Putting it all together: Conversion Solver user form10:38

Conoce a los instructores

Charlie Nuttelman
Instructor
Chemical and Biological Engineering

In this screencast, I'm going to teach you about event handlers.

So, I've opened up a new workbook here.

I'm going to go into the developer tab.

Now instead of inserting a module where I

put the codes or generally we've been creating a module,

but I'm going to show you how you can put code behind this sheet.

So sheet one or this workbook.

Typically, when you're making subroutines you want to put your code in a module.

But for event handlers,

these things know as the event handlers,

you're going to want to put your code behind either this workbook or whatever sheet.

So I'm just going to go into sheet one and it opens up sort of

this module that's similar to when you insert a normal module.

And up here from the drop-down menu,

I'm going to select worksheet.

Now, there are all sorts of different options over here.

These are called event handlers.

So before you right click,

you can do different things.

So, let me click on that and I'm going to put an example here.

This is a Private Sub.

So whenever you right-click,

this is what's going to happen before you

activate what's normally activated when you right-click.

So let me just put in an example here.

So I've just put a message box you just right-clicked.

So if I go to sheet one and I right-click,

then it's going to bring up that message box

and then it brings up the normal right-click menu.

So you wouldn't ordinarily have a reason to do this,

but if you for some reason you want it to have an event happen.

So this is an event when you right-click,

then you can put the code in there in this Private Sub.

The default, one of the most common is changing a selection.

That's why when you bring up the worksheet here,

selection pops up because that's kind of the most popular.

But I can also put something in here.

For example, ActiveCell equals ActiveCell plus one.

Now when I go to worksheet one and by the way,

if I had a worksheet two here,

if I included worksheet two,

these event handlers don't happen to sheet

two because I've written it in the code behind sheet one.

So now whenever I change a selection,

I'm going to add one to the active cell.

And this is quite annoying.

There's no reason you would do this ordinarily.

But this is known as an event handler.

So let's see what happens if you add an event handler to this workbook.

So am going to double-click on this workbook from the drop-down menu,

I'm going to bring up workbook.

The most common is an event handler when the workbook is

opened and I'll just put in something like this message box "welcome to Excel 2016.

Did you remember to turn off the stove this morning?"

I'm going to save this file and I'm going to

close it because we can't really see the effect of

this Workbook Open event handler until we open it.

I save this onto my desktop.

So now I'm just going to double-click event handler example,

and when we open this up,

it pops up with this message;

"Welcome to excel 2016.

Did you remember to turn off the stove this morning?"

I can also put something like this applicationspeech.speak.

Not every version of VBA,

I think only the newer ones have this,

but I can put in "Welcome.

Have a nice day, Charlie."

And when I save this and reopen,.

"Welcome. Have a nice day, Charlie.".

It gives me a nice little welcome message.

And just one more example,

I could put a message box "Would you like to know the time and date?"

That's going to be Yes or No.

If answer equals six,

then we can message box now.

And when I save this and close it and reopen,

I get the following.

And I can then select Yes and it'll output the time and the date.

Event handlers are not tremendously useful,

but they are really useful when you're providing a spreadsheet or

a macro-enabled file to a client or

a co-worker and you want to have things professionally presented.

We're going to be making user forms in the rest of this module,

an incoming screencasts and what we want to happen is when the workbook is opened,

we want the user form to automatically open.

So, I introduced event handlers here

because they're nice to use when we start talking about user forms.

Explora nuestro catálogo

Inscríbete de manera gratuita y obtén recomendaciones personalizadas, actualizaciones y ofertas.

Coursera brinda acceso universal a la mejor educación del mundo, al asociarse con las mejores universidades y organizaciones, para ofrecer cursos en línea.

© 2019 Coursera Inc. Todos los derechos reservados.

Descargar en la App Store

Consíguelo en Google Play