INF 110
Challenges Chapter 1 Excel VBA Programming
Challenges Chapter # 1 Excel
2007 User Instructions
Excel 2010 & 2013 User Instructions
Challenges 1-5 Page 22 Excel VBA Programming Textbook
General Guidelines:
Do steps 1-5 to produce a workbook with 5 spreadsheets named
challengesChapter1ExcelLastName.xlsm.
Note: To get the .xlsm extension on the workbook you need to save the workbook
as an Excel Macro-Enabled Workbook, by selecting it on the Save as type:
down-arrow. This is extremely important or else all your code will be lost.
Name the 5 worksheets challenge1-5, one for each of the challenge questions.
Copy the challenge2 worksheet to create the worksheets for challenges 3-5, it
will make answering the questions a lot easier. (Copy
Instructions)
Remember to put Remark statements at the top of your code identifying the
program, programmer, date, and description. See slide 9 in Excel 1 PowerPoint
for example for challenge 3.
Hints and
corrections:
-
In step 1 on page 22, in cell A1 type in "worksheet events",
in cells A2-A11
type in the names of the 1st ten events
for the Worksheet object in the VBA IDE. Remember to use
ALT-F11 keys to get to Excel's code
window.
See slide 33 in Excel 1 PowerPoint for example on how to
complete challenge 1. You need to be in slide show view, then click on the
command button labeled Challenge 1 Demo.
-
In
step
2 on page 22, add a label across the following range (C5:D6), (Steps:
Office 2007/2010/2013 Click the
Developer tab, click the Insert button (hammer and wrench
icon) on the ribbon to display the control toolbox. Click on a
label icon in the toolbox (capital A) in Active X Controls Toolbox);
then click in the upper left-hand corner of cell C5 and
draw the control on the spreadsheet. Set
the
Name
property
of
the
label to
(lblCellAddress). See slide
33 in Excel 1 PowerPoint for example on how to start challenge 2. You need
to be in slide show view, then click on the command button labeled Challenge
2 Demo.
Put
the
following code
statements
in
the
Click
event
of
the
label (lblCellAddress) using
the
code
window
as
shown
below: This is different than the instructions in the book on page 22,
instead of changing these properties as the book says, we are changing the
label’s properties with the code listed below to get you more familiar with
the VBA code.
lblCellAddress.Caption = "Hello World"
lblCellAddress.Font = "Times New Roman"
lblCellAddress.BackColor = vbYellow
lblCellAddress.ForeColor = vbRed
Copy and Paste the code and
comments
for lblCellAddress_Click event for challenge2
into cell A10.
-
In step 3 on page 22, before beginning this step after copying challenge2
worksheet to challenge3 worksheet, change the Name property of
the label from Label1 to lblCellAddress if necessary, before
adding the code given in step 3 on page 22. Copy and Paste the
code and
comments
for Worksheet_SelectionChange event for
challenge3 into cell A10.
-
In step 4 on page 22, in cell A9 type your answer to
challenge3 into the challenge4 worksheet. Remember to click on a cell in the
worksheet not on the label from challenge3, be sure that challenge4 tab at
the bottom is not selected in the workbook.
-
In step 5 on page 22, after pressing the F1 function key to
get help on the Caption property, use
ALT-Print Screen keys to get a copy of the screen, paste
this into the challenge5 worksheet using the range (A1:G24), use
the sizing handles to resize the screen-shot to fit the range.
Use the same technique discussed in step 5 above to fit the
Address help screen-shot into the range (A26:G48)
See slide 33 in Excel 1 PowerPoint for example on how to start challenge 5.
You need to be in slide show view, then click on the command button labeled
Challenge 5 Demo.
(See video)
(Download video)
(Students should
submit the electronic versions of the
workbook
(challengesChapter1ExcelLastName.xlsm),
together with the In-Chapter work from this chapter
In-ChapterExcel1LastName.xlsm),
each spreadsheet in the In-Chapter Excel workbook should use the following names
(MacroToolPractice,ColorCommandButtonP15),
reflecting the page in the text from which the work comes. Substitute your last
name for LastName listed in file name above)
Note: Steps to submit your work:
Submit the two files by clicking on the Assignments link then click Exer1 link
in the right pane, Choose the
challengesChapter1ExcelLastName.xlsm
workbook, then click Submit. Next click on the InChap1 link. Choose the
In-ChapterExcel1LastName.xlsm
workbook, then click Submit.
Please remember to put comments in the top of the code for the challenges see
slide 10 in Excel1 PowerPoint slide
for example.