Item Property

Microsoft Excel Visual Basic

Show All

Item Property

   

Item property as it applies to the Adjustments object.

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.

Item property as it applies to the Areas, Filters, HPageBreaks, Panes, Phonetics, RecentFiles, and VPageBreaks objects.

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.

Item property as it applies to the Borders 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.

XlBordersIndex can be one of these XlBordersIndex constants.
xlDiagonalDown
xlDiagonalUp
xlEdgeBottom
xlEdgeLeft
xlEdgeRight
xlEdgeTop
xlInsideHorizontal
xlInsideVertical

Item property as it applies to the Dialogs object.

Returns a Dialog object that represents a single built-in dialog box.

expression.Item(Index)

expression   Required. An expression that returns a Dialogs object.

Index  Required XlBuiltInDialog.

XlBuiltInDialog can be one of these XlBuiltInDialog constants.
_xlDialogChartSourceData
_xlDialogPhonetic
xlDialogActivate
xlDialogActiveCellFont
xlDialogAddChartAutoformat
xlDialogAddinManager
xlDialogAlignment
xlDialogApplyNames
xlDialogApplyStyle
xlDialogAppMove
xlDialogAppSize
xlDialogArrangeAll
xlDialogAssignToObject
xlDialogAssignToTool
xlDialogAttachText
xlDialogAttachToolbars
xlDialogAutoCorrect
xlDialogAxes
xlDialogBorder
xlDialogCalculation
xlDialogCellProtection
xlDialogChangeLink
xlDialogChartAddData
xlDialogChartLocation
xlDialogChartOptionsDataLabelMultiple
xlDialogChartOptionsDataLabels
xlDialogChartOptionsDataTable
xlDialogChartSourceData
xlDialogChartTrend
xlDialogChartType
xlDialogChartWizard
xlDialogCheckboxProperties
xlDialogClear
xlDialogColorPalette
xlDialogColumnWidth
xlDialogCombination
xlDialogConditionalFormatting
xlDialogConsolidate
xlDialogCopyChart
xlDialogCopyPicture
xlDialogCreateNames
xlDialogCreatePublisher
xlDialogCustomizeToolbar
xlDialogCustomViews
xlDialogDataDelete
xlDialogDataLabel
xlDialogDataLabelMultiple
xlDialogDataSeries
xlDialogDataValidation
xlDialogDefineName
xlDialogDefineStyle
xlDialogDeleteFormat
xlDialogDeleteName
xlDialogDemote
xlDialogDisplay
xlDialogEditboxProperties
xlDialogEditColor
xlDialogEditDelete
xlDialogEditionOptions
xlDialogEditSeries
xlDialogErrorbarX
xlDialogErrorbarY
xlDialogErrorChecking
xlDialogEvaluateFormula
xlDialogExternalDataProperties
xlDialogExtract
xlDialogFileDelete
xlDialogFileSharing
xlDialogFillGroup
xlDialogFillWorkgroup
xlDialogFilter
xlDialogFilterAdvanced
xlDialogFindFile
xlDialogFont
xlDialogFontProperties
xlDialogFormatAuto
xlDialogFormatChart
xlDialogFormatCharttype
xlDialogFormatFont
xlDialogFormatLegend
xlDialogFormatMain
xlDialogFormatMove
xlDialogFormatNumber
xlDialogFormatOverlay
xlDialogFormatSize
xlDialogFormatText
xlDialogFormulaFind
xlDialogFormulaGoto
xlDialogFormulaReplace
xlDialogFunctionWizard
xlDialogGallery3dArea
xlDialogGallery3dBar
xlDialogGallery3dColumn
xlDialogGallery3dLine
xlDialogGallery3dPie
xlDialogGallery3dSurface
xlDialogGalleryArea
xlDialogGalleryBar
xlDialogGalleryColumn
xlDialogGalleryCustom
xlDialogGalleryDoughnut
xlDialogGalleryLine
xlDialogGalleryPie
xlDialogGalleryRadar
xlDialogGalleryScatter
xlDialogGoalSeek
xlDialogGridlines
xlDialogImportTextFile
xlDialogInsert
xlDialogInsertHyperlink
xlDialogInsertNameLabel
xlDialogInsertObject
xlDialogInsertPicture
xlDialogInsertTitle
xlDialogItemProperties
xlDialogLabelProperties
xlDialogListboxProperties
xlDialogMacroOptions
xlDialogMailEditMailer
xlDialogMailLogon
xlDialogMailNextLetter
xlDialogMainChart
xlDialogMainChartType
xlDialogMenuEditor
xlDialogMove
xlDialogNew
xlDialogNewWebQuery
xlDialogNote
xlDialogObjectProperties
xlDialogObjectProtection
xlDialogOpen
xlDialogOpenLinks
xlDialogOpenMail
xlDialogOpenText
xlDialogOptionsCalculation
xlDialogOptionsChart
xlDialogOptionsEdit
xlDialogOptionsGeneral
xlDialogOptionsListsAdd
xlDialogOptionsME
xlDialogOptionsTransition
xlDialogOptionsView
xlDialogOutline
xlDialogOverlay
xlDialogOverlayChartType
xlDialogPageSetup
xlDialogParse
xlDialogPasteNames
xlDialogPasteSpecial
xlDialogPatterns
xlDialogPhonetic
xlDialogPivotCalculatedField
xlDialogPivotCalculatedItem
xlDialogPivotClientServerSet
xlDialogPivotFieldGroup
xlDialogPivotFieldProperties
xlDialogPivotFieldUngroup
xlDialogPivotShowPages
xlDialogPivotSolveOrder
xlDialogPivotTableOptions
xlDialogPivotTableWizard
xlDialogPlacement
xlDialogPrint
xlDialogPrinterSetup
xlDialogPrintPreview
xlDialogPromote
xlDialogProperties
xlDialogProtectDocument
xlDialogProtectSharing
xlDialogPublishAsWebPage
xlDialogPushbuttonProperties
xlDialogReplaceFont
xlDialogRoutingSlip
xlDialogRowHeight
xlDialogRun
xlDialogSaveAs
xlDialogSaveCopyAs
xlDialogSaveNewObject
xlDialogSaveWorkbook
xlDialogSaveWorkspace
xlDialogScale
xlDialogScenarioAdd
xlDialogScenarioCells
xlDialogScenarioEdit
xlDialogScenarioMerge
xlDialogScenarioSummary
xlDialogScrollbarProperties
xlDialogSearch
xlDialogSelectSpecial
xlDialogSendMail
xlDialogSeriesAxes
xlDialogSeriesOptions
xlDialogSeriesOrder
xlDialogSeriesShape
xlDialogSeriesX
xlDialogSeriesY
xlDialogSetBackgroundPicture
xlDialogSetPrintTitles
xlDialogSetUpdateStatus
xlDialogShowDetail
xlDialogShowToolbar
xlDialogSize
xlDialogSort
xlDialogSortSpecial
xlDialogSplit
xlDialogStandardFont
xlDialogStandardWidth
xlDialogStyle
xlDialogSubscribeTo
xlDialogSubtotalCreate
xlDialogSummaryInfo
xlDialogTable
xlDialogTabOrder
xlDialogTextToColumns
xlDialogUnhide
xlDialogUpdateLink
xlDialogVbaInsertFile
xlDialogVbaMakeAddin
xlDialogVbaProcedureDefinition
xlDialogView3d
xlDialogWebOptionsBrowsers
xlDialogWebOptionsEncoding
xlDialogWebOptionsFiles
xlDialogWebOptionsFonts
xlDialogWebOptionsGeneral
xlDialogWebOptionsPictures
xlDialogWindowMove
xlDialogWindowSize
xlDialogWorkbookAdd
xlDialogWorkbookCopy
xlDialogWorkbookInsert
xlDialogWorkbookMove
xlDialogWorkbookName
xlDialogWorkbookNew
xlDialogWorkbookOptions
xlDialogWorkbookProtect
xlDialogWorkbookTabSplit
xlDialogWorkbookUnhide
xlDialogWorkgroup
xlDialogWorkspace
xlDialogZoom

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.

Item property as it applies to the Errors object.

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.

xlEvaluateToError  The cell evaluates to an error value.
xlTextDate  The cell contains a text date with 2 digit years.
xlNumberAsText  The cell contains a number stored as text.
xlInconsistentFormula  The cell contains an inconsistent formula for a region.
xlOmittedCells  The cell contains a formula omitting a cell for a region.
xlUnlockedFormulaCells  The cell which is unlocked contains a formula.
xlEmptyCellReferences  The cell contains a formula referring to empty cells.

Item property as it applies to the Range 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).

Item property as it applies to all other objects in the Applies To list.

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.

Example

As it applies to the AddIns object.

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

As it applies to the AllowEditRanges object.

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

As it applies to the Areas object.

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

As it applies to the Borders object.

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)

As it applies to the CalculatedMembers object.

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

As it applies to the Charts object.

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

As it applies to the CubeFields object.

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

As it applies to the CustomProperties object.

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

As it applies to the Dialogs object.

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

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

As it applies to the Filters object.

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

As it applies to the HPageBreaks object.

This example changes the location of horizontal page break one.

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

As it applies to the Hyperlinks object.

The following example activates hyperlink one on cell E5.

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

As it applies to the Panes object.

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

As it applies to the Phonetics object.

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

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

As it applies to the PublishObjects object.

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

As it applies to the Range object.

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

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

As it applies to the RecentFiles object.

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

Application.RecentFiles.Item(2).Open

As it applies to the Sheets object.

This example activates Sheet1.

Sheets.Item("sheet1").Activate

As it applies to the SmartTagRecognizer object.

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

MsgBox Application.SmartTagRecognizers.Item(1).FullName

As it applies to the Styles object.

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

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

As it applies to the VPageBreaks object.

This example changes the location of vertical page break one.

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

As it applies to the Windows object.

This example maximizes the active window.

Windows.Item(1).WindowState = xlMaximized

As it applies to the Workbooks object.

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

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

As it applies to the Worksheets object.

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)