Item Property

Returns or sets the adjustment value specified by the Index argument. For linear adjustments, an adjustment value of 0.0 generally corresponds to the left or top edge of the shape, and a value of 1.0 generally corresponds to the right or bottom edge of the shape. However, adjustments can pass beyond shape boundaries for some shapes. For radial adjustments, an adjustment value of 1.0 corresponds to the width of the shape. For angular adjustments, the adjustment value is specified in degrees. The Item property applies only to shapes that have adjustments. Read/write Single.

expression.Item(Index)

expression    Required. An expression that returns an Adjustments object.

Index   Required Long. The index number of the adjustment.

Remarks

AutoShapes, connectors, and WordArt objects can have up to eight adjustments.

Returns a single object from a collection.

expression.Item(Index)

expression    Required. An expression that returns one of the above objects.

Index   Required Long. The index number of the object.

Returns a Border object that represents one of the borders of either a range of cells or a style.

expression.Item(Index)

expression    Required. An expression that returns a Borders collection.

Index   Required XlBordersIndex.

Item property as it applies to the Dialogs object.

Remarks

Using the Item property of the Dialogs collection and the Show method, you can display approximately 200 built-in dialog boxes. Each dialog box has a constant assigned to it; these constants all begin with "xlDialog."

For a table of the available constants and their corresponding argument lists, see Built-In Dialog Box Argument Lists.

The Item property of the Dialogs collection may fail if you try to show a dialog box in an incorrect context. For example, to display the Data Labels dialog box (using the Visual Basic expression Application.Dialogs(xlDialogDataLabel).Show), the active sheet must be a chart; otherwise, the property fails.

Returns a single member of the Error object.

expression.Item(Index)

expression    Required. An expression that returns an Errors object.

Index   Required Variant. The Index can also be one these constants.

Returns a single object from a collection.

expression.Item(Index)

expression    Required. An expression that returns an object from the Applies To list.

Index    Required Variant. The name or index number of the object.

Returns a Range object that represents a range at an offset to the specified range.

expression.Item(RowIndex, ColumnIndex)

expression    Required. An expression that returns a Range object.

RowIndex   Required Variant. The index number of the cell you want to access, in order from left to right, then down. Range.Item(1) returns the upper-left cell in the range; Range.Item(2) returns the cell immediately to the right of the upper-left cell.

ColumnIndex   Optional Variant. A number or string that indicates the column number of the cell you want to access, starting with either 1 or "A" for the first column in the range.

Remarks

Syntax 1 uses a row number and a column number or letter as index arguments. For more information about this syntax, see the Range object. The RowIndex and ColumnIndex arguments are relative offsets. In other words, specifying a RowIndex of 1 returns cells in the first row of the range, not the first row of the worksheet. For example, if the selection is cell C3, Selection.Cells(2, 2) returns cell D4 (you can use the Item property to index outside the original range).

Returns a single object from a collection.

expression.Item(Index)

expression    Required. An expression that returns one of the above objects.

Index   Required Variant. The name or index number of the object.

Remarks

For more information about returning a single member of a collection, see Returning an Object from a Collection.

This example displays the status of the Analysis ToolPak add-in. Note that the string used as the index to the AddIns method is the Title property of the AddIn object.

If AddIns.Item("Analysis ToolPak").Installed = True Then
    MsgBox "Analysis ToolPak add-in is installed"
Else
    MsgBox "Analysis ToolPak add-in is not installed"
End If
				

This example allows edits to range ("A1:A4") on the active worksheet, notifies the user, then changes the password for this specified range and notifies the user of this change.

Sub UseChangePassword()

    Dim wksOne As Worksheet

    Set wksOne = Application.ActiveSheet

    ' Establish a range that can allow edits
    ' on the protected worksheet.
    wksOne.Protection.AllowEditRanges.Add _
        Title:="Classified", _
        Range:=Range("A1:A4"), _
        Password:="secret"

    MsgBox "Cells A1 to A4 can be edited on the protected worksheet."

    ' Change the password.
    wksOne.Protection.AllowEditRanges.Item(1).ChangePassword _
        Password:="moresecret"

    MsgBox "The password for these cells has been changed."

End Sub
				

This example clears the first area in the current selection if the selection contains more than one area.

If Selection.Areas.Count <> 1 Then
    Selection.Areas.Item(1).Clear
End If
				

This following example sets the color of the bottom border of cells A1:G1.

Worksheets("Sheet1").Range("a1:g1"). _
    Borders.Item(xlEdgeBottom).Color = RGB(255, 0, 0)
				

The following example notifies the user if the calculated member is valid or not. This example assumes a PivotTable exists on the active worksheet that contains either a valid or invalid calculated member.

Sub CheckValidity()

    Dim pvtTable As PivotTable
    Dim pvtCache As PivotCache

    Set pvtTable = ActiveSheet.PivotTables(1)
    Set pvtCache = Application.ActiveWorkbook.PivotCaches.Item(1)

    ' Handle run-time error if external source is not an OLEDB data source.
    On Error GoTo Not_OLEDB

    ' Check connection setting and make connection if necessary.
    If pvtCache.IsConnected = False Then
        pvtCache.MakeConnection
    End If

    ' Check if calculated member is valid.
    If pvtTable.CalculatedMembers.Item(1).IsValid = True Then
        MsgBox "The calculated member is valid."
    Else
        MsgBox "The calculated member is not valid."
    End If

End Sub
				

This example sets the number of units that the trendline on Chart1 extends forward and backward. The example should be run on a 2-D column chart that contains a single series with a trendline.

With Charts.Item("Chart1").SeriesCollection(1).Trendlines(1)
    .Forward = 5
    .Backward = .5
End With
				

This example finds the first PivotTable report whose first cube field name contains the string “Paris”. The Boolean variable blnFoundName is set to True if the name is found.

blnFoundName = False
For Each objPT in ActiveSheet.PivotTables
    Set objCubeField = _
        objPT.CubeFields.Item(1)
    If instr(1,objCubeField.Name, "Paris") <> 0 Then
        blnFoundName = True
        Exit For
    End If
Next objPT
				

The following example demonstrates this feature. In this example, Microsoft Excel adds identifier information to the active worksheet and returns the name and value to the user.

Sub CheckCustomProperties()

    Dim wksSheet1 As Worksheet

    Set wksSheet1 = Application.ActiveSheet

    ' Add metadata to worksheet.
    wksSheet1.CustomProperties.Add _
        Name:="Market", Value:="Nasdaq"

    ' Display metadata.
    With wksSheet1.CustomProperties.Item(1)
        MsgBox .Name & vbTab & .Value
    End With

End Sub
				

This example displays the Open dialog box and selects the Read-Only option.

Application.Dialogs.Item(xlDialogOpen).Show arg3:=True
				

The following example sets a variable to the value of the On property of the filter for the first column in the filtered range on the Crew worksheet.

Set w = Worksheets("Crew")
If w.AutoFilterMode Then
    filterIsOn = w.AutoFilter.Filters.Item(1).On
End If
				

This example changes the location of horizontal page break one.

Worksheets(1).HPageBreaks.Item(1).Location = .Range("e5")
				

The following example activates hyperlink one on cell E5.

Worksheets(1).Range("E5").Hyperlinks.Item(1).Follow
				

The following example displays the name of the default list object on Sheet1 of the active workbook.

   
   Set wrksht = ActiveWorkbook.Worksheets("Sheet1")
   Set oListObj = wrksht.ListObjects.Item(1).Name
				

This example splits the window in which worksheet one is displayed and then scrolls through the pane in the lower-left corner of the window until row five is at the top of the pane.

Worksheets(1).Activate
ActiveWindow.Split = True
ActiveWindow.Panes.Item(3).ScrollRow = 5
				

This example makes the first phonetic text string in the active cell visible.

ActiveCell.Phonetics.Item(1).Visible = True
				

This example obtains the identifier from a <DIV> tag and finds the line in a Web page (q198.htm) that you saved from a workbook. The example then creates a copy of the Web page (newq1.htm) and inserts a comment line before the <DIV> tag in the copy of the file.

strTargetDivID = ActiveWorkbook.PublishObjects.Item(1).DivID
Open "\\server1\reports\q198.htm" For Input As #1
Open "\\server1\reports\newq1.htm" For Output As #2
While Not EOF(1)
    Line Input #1, strFileLine
    If InStr(strFileLine, strTargetDivID) > 0 And _
        InStr(strFileLine, "<div") > 0 Then
            Print #2, "<!--Saved item-->"
    End If
    Print #2, strFileLine
Wend
Close #2
Close #1
				

This example fills the range A1:A10 on Sheet1, based on the contents of cell A1.

Worksheets("Sheet1").Range.Item("A1:A10").FillDown
				

This example opens file two in the list of recently used files.

Application.RecentFiles.Item(2).Open
				

This example activates Sheet1.

Sheets.Item("sheet1").Activate
				

This example notifies the user the full name of the first smart tag recognizer.

MsgBox Application.SmartTagRecognizers.Item(1).FullName
				

This example changes the Normal style for the active workbook by setting the style's Bold property.

ActiveWorkbook.Styles.Item("Normal").Font.Bold = True
				

This example changes the location of vertical page break one.

Worksheets(1).VPageBreaks.Item(1).Location = .Range("e5")
				

This example maximizes the active window.

Windows.Item(1).WindowState = xlMaximized
				

This example sets the wb variable to the workbook for Myaddin.xla.

Set wb = Workbooks.Item("myaddin.xla")
				

Item is the default member for a collection. For example, the following two lines of code are equivalent.

ActiveWorkbook.Worksheets.Item(1)
ActiveWorkbook.Worksheets(1)