Just like Word and Access, Excel allows you to customize it with macros and Visual Basic. This lesson takes you through some of these features. This is a really great feature for advanced users - they can build a lot of functionality that doesn't originally appear in the program.
Allowing your users to add their own ocde is a risky because it is a good way to sneak a virus into an application. The code that an expert user adds could be harmful. That is why the default security settings are to disable (make a macro unrunable). Word, Access and Excel all assume that most users don't know what a macro is. If one got attached to their document, it is probably a virus.
However, YOU are becoming expert users. You are starting to create macros on puropse so you need to change the security settings that were designed for idoit users.
Choose Tools -> Macro -> Security Settings and then pick Low.
Switch back to High when you are opening someone else's work.
1. Type in the data that is shown. For the sake of being able to follow this demo, type in the exact cells shown.
2. Select cells A5:C8. It is no mistake that there is a blank row at the bottom and there is no mistake that the headings are not included. Be careful of both factors. They are needed for the insert later on.
3. Name cells A5:C8 as marksData. (Insert->Name->Define, or something close to that). Please be exact with captials and spelling (for the sake of being able to follow the demo).
4. Name the blank row at the bottom "blank".
5. We are going to make a macro that sorts by first name to start. Before we record it, you need to figure out how sorting works. The steps:
(a) select marksData in the name pull down.
(b) Choose Data-> Sort and choose the right information in the dialog.
(c) select cell A1 to unselect the data. (Well, obviously, it doesn't need to be A1. Any cell outside the range works)
6. Once you are good at sorting, it is time to record a macro.
(a) Choose Tools -> Macro -> Record New Macro
(b) Go through the steps to sort. Make sure you click off the data set at the end to deselect it. That makes your macro seem more professional.
(c) Stop recording with the Stop button or with Tools -> Macro -> Stop Recording
7. You need to add a button to the screen.
View -> Tool Bars -> Forms
Assign your sorting macro to the button.
Right click on the button to change its text.
8. Record other macros to sort by last name and by mark. Attach them to buttons.
We are going to make a little dialog box to get the new entry's information and insert it into the spreadsheet. This makes things super user friendly so even a complete idiot can use our spreadsheet.
1. Open the Visual Basic Editor. You've seen this in Word.
2. Insert a Form. (Insert -> User Form)
3. Take some time to modify the properties of your form.
4. Add some labels and textfields to your form. Change their properties so they look nice.
Notice the name of each widget that you add. (It is the top thing in the properties box). For the purpose of this demo, please make sure that you have used the same names as I have. I used the defaults, so this shouldn't be hard. You can rename them by typing in the name of the properties box.
5. We are going to add some code to the button to make it do something interesting.
Double click on the button and paste in this code:
Private Sub CommandButton1_Click()
Application.Goto Reference:="blank"
Selection.EntireRow.Insert
Application.ActiveCell = TextBox1
Application.ActiveCell.Next = TextBox2
Application.ActiveCell.Next.Next = Val(TextBox3)
UserForm1.Hide
Range("B1").Select
End Sub
Note that this code requires things to be named in the same way that I did. If
you did not name them as I did, your code won't run.6. We also need to add a macro to open our form.
Sub Insert() UserForm1.Show End Sub
7. Add an Insert Button and link it to your insert macro.
8. Run your Insert macro by clicking on the macro.
Hopefully, it adds your data. If not go back and check all of the names.
