Introduction to combo boxes, Part 1

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

I want to show you a couple more advanced features of user forms.

In particular, I'm going to show you how to add these drop-down combo boxes.

If I open up this form,

I have populated this combo box with these various items from column A,

so I can select, for example 'happy',

and I can click Go and it's going to output the item that's to the right of it.

So I get a C. So if you've got sort of a list or a bunch of data on a spreadsheet,

combo boxes are a nice way to work with those data.

I've also got this set up so we can remove an item.

This is going to help you for those of you who are doing the assignments,

this is going to really help you for assignment four,

so I can select, let's say,

I wanted to remove sleepy,

then I can remove that item.

It deletes it from the spreadsheet and it also deletes it from the drop-down list.

So, on the course website,

I've got this starter file.

It's got Example 1 tab.

It's got original data because we're going to be working with deleting these cells,

so I just provide the original data so you can easily copy and

paste back to the original spreadsheet if you like.

Now, we've got a list here and we're going to create a combo box.

I've already created this in User Form 1.

So, what I have done down here in the tool box

there's a combo box so I've dragged this in there.

This is named Combo Box 1.

This button is named Go button.

We have a Remove button and a Quit button.

Now, whenever you have a combo box,

before you open up the User Form 1,

you need to populate the items in the combo box.

So, we're going to be filling out this code here and populate Combo Box 1.

So what I'm going to do, we have these items in the A column, A1 through A7,

and we're going to make a vector out of that real quick,

and then we're going to populate Combo Box 1 on User Form 1 with those various elements.

Now, because we're going to be removing items later from the spreadsheet,

we're going to be removing rows,

I'm not going to dim this as a vector of size 7.

Instead, what we're going to do is we're going to count the number of

items every time after we remove an item.

I'm also dimming n, that's going to be the number of items and i as an integer.

That's going to be a index of iteration.

We can obtain n using the worksheet function count A.

What that does is it counts all the items in column A that are not blank.

So we have seven.

We're assuming it's a continuous column.

Once we know the size,

we're going to redim names vector as a string,

and then one at a time,

we're gonna take Range A1 to A_n,

each of those items in column A,

one at a time into our names vector.

Now, I need to make sure I put cells i and then 1.

So, we're going to take doc,

put that into names 2, Grumpy,

that would be names 2, and so on.

After we have the names vector built,

we're going to then create on our User Form 1,

we need to populate the combo box.

This is Combo Box 1 of User Form 1 so we need to

populate that with the various names in our vector.

To add an item,

we can use User Form 1,

Combo Box 1, Add Item,

and we're going to add the i element of the names vector,

and we're going to iterate through all elements of the names vector 1 through n.

I've also got this run form sub down here that's going to call populate Combo Box 1.

You can also just write Populate Combo Box 1 which

runs this subroutine and then we're going to show User Form 1.

So, let's go ahead and run this.

I'm going to put in a break point here.

So let's run this using F8.

We go into the Populate Combo Box.

You notice that as I go through this,

if I open up my names vector down here,

we're taking in those different names.

So it looks like it's working.

We're also adding items to the combo box even though we

haven't brought up or shown the User Form 1 quite yet.

So then we go through this and we show

the user form the user form then has the different items here of our Column A.

So that's how we can populate the combo box.

Now, you notice that there is no default value here at the beginning and a lot of times,

I like to just take the first item of

our names vector and put it in here as a default value.

To do that, we can just put in this line, UserForm1.ComboBox1.Text.

That's the default text equal names of 1.

So that'll give it a default value.

Now, when we run this,

that default value here is the first item in our list.

Let's also code the quit button while we're doing this.

We can just do unload User Form 1 to quit.

So we've got this populate combo box,

we run that, we open up the user form,

we have the different items from our combo box, but right now,

if I select something like sleepy,

we want to do something.

In particular, for sleepy,

we want to be able to display the item in the column right next to it,

but right now, it's not doing anything.

I've separated this screencast into two parts.

So I will continue talking about this in the next screencast.

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