HW#12 - Working with Objects

The purpose of this assignment, is to get some experience working with objects, and to gain some experience using the VB Help utility and the Object Browser.  You will be writing VB code for a spreadsheet that manipulates two types of objects, Sheets and Shapes.  When completed, your spreadsheet will look something like this:

Click here to download a copy of this spreadsheet minus the option and command button controls.  Add the controls and the corresponding VB code according to the instructions given below.  As you develop your code, you will need to consult the Object Browser and the VB Help utility to figure out how to properly manipulate the objects.  Specifically, you will be looking up certain objects to find out what properties and methods are supported for the objects and how the properties and methods are used. You are NOT allowed to solve the problem by recording macros.

Part 1 Sheets

An Excel spreadsheet is referenced as a "Workbook" object.  Within a workbook, there can be one or more Worksheets (also called Sheets).  Each Worksheet (or Sheet) is part of a collection called Worksheets (or Sheets).  Occasionally, it is useful to use VB code to manipulate sheets.

Add two buttons to part 1 of your spreadsheet as shown above.  Use the buttons to jump to (bring to the front) the second and third sheets of the spreadsheet.

Hint: Do a search in the Object Browser using the actual sheet names ("Sheet1", "Sheet2", etc.). Look for a method that will accomplish what you are trying to do.

Part 2. Shapes

Each sheet in a workbook has a collection called Shapes that contains a set of Shape objects.  This collections represents all of the shapes created on the sheet using the drawing tools.  This collection can also be manipulated using VB code.  To traverse all of the objects in a collection, use the For Each ... Next construct we discussed in class.  Click here for some examples of traversing shapes.  For example,

a. Create a set of option controls for setting the fill color of each of the shapes as shown above.  When the user clicks on one of the options, set all of the shapes to the selected color.

Hint: Use the "RGB" color option and use the pre-defined "color constants".

b. Create a set of option controls to adjust the line thickness for the shapes.

Hint: Look for a property (and sub-property) that will achieve your objective.

c. Create a pair of buttons to flip all of the objects either in the horizontal or vertical directions.

Hint: Look for a method associated with a shape.

Submittal Instructions:

1. Be sure to save the changes to your spreadsheet.

2. Upload your spreadsheet via Learning Suite.