Explore
For Enterprise
Join for Free
Log In

Arts et humanités
Business
Informatique
Science des données
Technologies de l'information
santé
Mathématiques et logique
Développement personnel
Sciences physiques et ingénierie
Sciences sociales
Apprentissage des langues
Diplômes
Certificats

Explorez l'intégralité de Coursera

Parcourir
Rechercher
For Enterprise
Connexion
Inscrivez-vous gratuitement

How to add a new record, Part 2

Loading...

Excel/VBA for Creative Problem Solving, Part 3 (Projects)

Université du Colorado à Boulder

4.9 (95 notes) | 3.4K étudiants inscrits

Cours 3 de 3 dans Excel/VBA for Creative Problem Solving Spécialisation

In this course, learners will complete several VBA projects. It is highly recommended that learners first take "Excel/VBA for Creative Problem Solving, Part 1" and "Excel/VBA for Creative Problem Solving, Part 2". This course builds off of skills learned in those two courses. This is a project-based course. Therefore, the projects are quite open-ended and there are multiple ways to solve the problems. Through the use of Peer Review, other learners will grade learners' projects based on a grading rubric.

Visualiser le programme de cours

Avis

4.9 (95 notes)

5 stars
83 ratings
4 stars
9 ratings
3 stars
2 ratings
2 stars
1 ratings

RR

Jun 06, 2018

Challenging and rewarding. Very useful and complete. Great tutor support. A course i am enjoying very, very much.

DF

Feb 03, 2019

Awesome course for all of us that want develop advanced VBA apps for Excel data analysis and management.

À partir de la leçon

Intermediate Projects: Lesson Choices

Introduction to the Ultimate Organizer project7:58

A demonstration of the Ultimate Organizer7:38

How to add a category7:21

How to add a new record, Part 18:00

How to add a new record, Part 23:50

How to reveal hidden labels and text boxes9:22

How to add an icon to the Quick Access Toolbar to run your user form3:26

Enseigné par

Charlie Nuttelman
Instructor

Transcription

The next step then is going to take whatever they enter.

So if somebody entered the different stuff here and

pressed Add Record, we want that to then be placed on the spreadsheet.

In this case, the next empty row, which is row 4.

So now let's work on doing this last part.

This last part is going to happen when we click Add Record.

So after the user fills in these three fields, we want to click Add Record.

So I'm going to go ahead and click on that.

And this is where we're going to put the code for this.

We're going to Dim nr As an Integer, that's going to be the number of rows.

We determine number of rows using the WorksheetFunction CountA.

Columns A:A- 1, so that's going to tell us the total number of rows,

just like we did previously.

We're going to make sure that Range ("A1") is selected.

And then we're going to ActiveCell.Offset.Select.

So we're changing the selection of the ActiveCell, but

we're going to say ActiveCell.Offset (number of rows + 1).Select.

What this does is it selects in the same column.

So we're not ActiveCell offsetting any columns with the 0 here,

but we're going to select and choose the first empty row in column A.

So in our example, this would then go down to cell A4.

And finally with these three lines,

we're taking the items that were input into these three boxes.

We're taking those one at a time and

placing those into the empty row that we are now on.

So after we select the new ActiveCell,

we're going to offset by (0, 0) and place the name.

We're going to offset (0, 1) to place the address.

And we're going to offset by (0, 2) to place the phone number.

So let's go ahead and see if this works.

In the Add Record Sub, I'm going to put a break point here.

And let's go ahead and run it from the beginning.

I'm going to press F5, I'm going to enter some information here.

Now I'm going to click Add Record.

We bump into this Private Sub, the AddButton Private Sub.

And now I'm going to press F8.

We count the number of rows, which is 3 minus 1 for the first row here.

That's 2, so we have two elements.

We're going to select cell A1.

And now I'm going to ActiveCell.Offset 3 rows and 0 columns.

So that'll go 1, 2, 3, and that'll be A4.

So now we're re-selecting as our ActiveCell, cell A4.

And now what I'm going to do is I'm going to take these three input fields,

input one, input two, and input three, one at a time.

And I'm going to place them in ActiveCell.Offset all on the same row.

But I'm going to ActiveCell.Offset 0 columns, 1 column, and 2 column.

So I press F8 three times, and

we place that information that was in the Add Record form onto the spreadsheet.

And then we end that Sub.

And then if I press Quit, we can quit, or I could add another.

Let's do another one here.

So when I click here, I'm just going to go through this pretty quickly,

we're going to place Sally's information on the spreadsheet.

And then I can go ahead and press Quit.

All right, so that kind of shows you how you can

take the information from the Add Record form and place it onto the spreadsheet.

In the next Screencast, I'm going to show you how you can use kind of a dynamic

number of columns, how we can detect that.

And also how we can reveal or unhide some of these hidden labels and text boxes.

Explorer notre catalogue

Rejoignez-nous gratuitement et obtenez des recommendations, des mises à jour et des offres personnalisées.

Coursera propose un accès universel à la meilleure formation au monde, en partenariat avec des universités et des organisations du plus haut niveau, pour proposer des cours en ligne.

© 2019 Coursera Inc. Tous droits réservés.

Télécharger dans l'App Store

Disponible sur Google Play

Coursera
À propos
Direction
Carrières
Catalogue
Certificats
Certificats MasterTrack™
Diplômes
For Enterprise
For Government

Communauté
Learners
Partners
Developers
Beta Testers
Translators

Se connecter
Blog
Facebook
LinkedIn
Twitter
Instagram
YouTube
Blog Tech

Plus
Conditions
Confidentialité
Aide
Accessibilité
Presse
Contact
Répertoire
Filiales