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 per our class discussion. 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.

Remember, below are the types of members in the object browser and the icons that represent them:

- Event

- Method

- Property

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". The syntax of the line of code within the For Each ... Next construct for changing the color to red will follow the following format: sh.property.sub-property.RGB = vbRed. The other 2 colors will be similar but will have vbGreen and vbYellow instead of vbRed. Your job is to find the property and sub-property. "sh" is just the variable that you use in your For Each ... Next construct.

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. The syntax of the line of code within the For Each ... Next construct for changing the line thickness to 1.0 will follow the following format: sh.property.sub-property = 1#. To change the line thickness to 2.0 and 3.0, similar lines of code will be needed, but the right side will be 2# and 3#.

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. The syntax of the line of code within the For Each ... Next construct for flipping the shapes horizontally will follow the following format: sh.property (msoFlipHorizontal). Flipping the shapes vertically will require identical code, except the (msoFlipHorizontal) will be (msoFlipVertical).

Submittal Instructions:

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

2. Upload your spreadsheet via Learning Suite.