CODEWITHSUNDEEP
excelvba
GoldFusion
GoldFusion

Reputation: 149

Getting Run-time error '424': object required

I have written a code to add a custom header for each specific sheets whether the cell "H13" on "DonotPrint - Setup" sheet is empty. If found empty it will just add a default text. While if its not empty, it will copy over "H13" value and add it to the header.

Note: this code is written within "DonotPrint - Setup" sheet 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim headerSheets As Variant, sh
    Dim Filename As String
    headerSheets = Array("COVER", "SCOPE", "SUMMARY", "Updated Hours EST", "RATES")

    If IsEmpty(Me.Range("H13").value) Then
    Filename = "Proposal Template"
    headerSheets.PageSetup.CenterHeader = "&B &12 PROPOSAL" & Chr(10) & Chr(10) & " &08 " & Filename
    Else
    Filename = .Range("H13")
    headerSheets.PageSetup.CenterHeader = "&B &12 PROPOSAL" & Chr(10) & Chr(10) & " &08 " & Filename
    End If
End Sub

But I am getting Run-time error '424': object required when doing this and its highlighting the second code line after the "Else".

Upvotes: 0

Views: 124

Answers (1)

Brian M Stafford
Brian M Stafford

Reputation: 8868

The headerSheets variable is defined as a variant, containing an array of strings. As such, it is not an object you can call methods on.

You want to iterate the array of sheet names, and for each sheet name, get the Worksheet object with that name.

Dim i As Long
For i = LBound(headerSheets) To UBound(headerSheets)
    Dim ws As Worksheet
    Set ws = ActiveWorkbook.Worksheets(headerSheets(i))

    'now you have a Worksheet object to make member calls against:
    ws.PageSetup.CenterHeader = "..."
    '...
Next

Upvotes: 2

Related Questions