Custom Property Object Example

This routine loops through the worksheets in a workbook and creates a CustomProperty called IsBudget. The value of IsBudget depends on whether or not the worksheet contains the phrase Budget Analysis. It then lists the results:

Sub CreateCustomProperties() Dim bBudget As Boolean Dim lRow As Long

Dim oCustomProp As CustomProperty Dim rng As Range, wks As Worksheet

'Turn off the screen and clear the search formats

CustomView Object and the CustomViews Collection

With Application

.FindFormat.Clear .ScreenUpdating = False End With

'Clear the worksheet that will contain the Custom Property list ActiveSheet.UsedRange.Offset(1, 0).ClearContents

'Initialize the row counter lRow = 2 'Row 1 contains the Column Headings

'Loop through the worksheet in this workbook For Each wks In ThisWorkbook.Worksheets

'Supress errors resulting in no cells found and no Custom Property On Error Resume Next bBudget = False bBudget = (Len(wks.UsedRange.Find(What:="Budget Analysis").Address) > 0)

'We cannot refer to a Custom Property by its name, only its numeric index Set oCustomProp = wks.CustomProperties(1) On Error GoTo 0

'If the Custom Property exists, delete it and add it again

If Not oCustomProp Is Nothing Then oCustomProp.Delete

'Note the value of bBudget is encased in double quotes.

'If we don't, True will be stored as -1 and False 0 (their numeric values).

Set oCustomProp = wks.CustomProperties.Add(Name:="IsBudget", Value:="" _ & bBudget & "")

'List the Custom Property settings on the worksheet With ActiveSheet

'Parent.Name returns the name of the object holding the Custom Property

'That object is the worksheet name in this case

.Cells(lRow, 1).Value = oCustomProp.Parent.Name .Cells(lRow, 2).Value = oCustomProp.Name .Cells(lRow, 3).Value = oCustomProp.Value End With

Next wks End Sub

0 0

Post a comment