Page 148 - Microsoft Office Excel 2003 Programming Inside Out

Part 3: The Excel Object Mode
Microsoft Office Excel 2003 Programming Inside Out

The full syntax for using the InputBox method is

expression.InputBox(Prompt, Title, Default, Left, Top, HelpFile,
HelpContextID, Type)
Expression, an Application object, and Prompt, a string value, are both required elements for
calling the InputBox method. The other parameters are optional, but placeholders must be
used when calling the method in standard form. To avoid placeholders, you can name the
parameters you are supplying, such as the following:

Application.InputBox(Prompt:="My Prompt", Type:=8)
The InputBox allows the user to use the mouse to select a cell or range of cells from any open
workbook. The Set statement must be used to assign the selected cells to a Range object;
otherwise, the value of the selected cells is used.
You need to use a Set statement whenever you want to assign a specific instance of an object
to a variable. For example, you can create a variable to point to a Worksheet object using the
following statement:

Dim wksNewSheet As Worksheet
There is one more step to go, though; merely creating a variable to point to an object neither
creates a new object nor points to an existing instance of the object. To have the wksNewSheet
variable point to a worksheet named January, you would use the following statement:
Set wksNewSheet = Worksheets("January")

Now that the variable wksNewSheet is assigned to the worksheet named January, you can use
the variable name as a pointer to the January worksheet. These two lines of code are equivalent.

wksNewSheet.Name = "January2"
Worksheets("January").Name = "January2"

The following example uses the InputBox method to ask the user to specify a range of cells to
use for the monthly totals and then prompts the user for the values needed:

Sub InputMonthlyTotals()
Dim strPrompt As String, rgeMonth As Range, sglTotal As Single
Dim intColumn As Integer
strPrompt = "Please select cells to place monthly totals in."
Set rgeMonth = Application.InputBox(Prompt:=strPrompt, Title:=" Microsoft _
Office Excel 2003 Programming Inside Out", Type:=8)
strPrompt = "Please enter the total sales."
For intColumn = 1 To rgeMonth.Columns.Count
sglTotal = Application.InputBox(Prompt:=strPrompt, Title:=" Microsoft _
Office Excel 2003 Programming Inside Out", Type:=1)
rgeMonth(1, intColumn) = sglTotal
Next intColumn
Chapter 6
End Sub
122