Object required using "If Target.Address" outside Worksheet_change

[guimenez]

Hi.
I need to compare a cell with a value in my function but i'm having Object required error.
If i put the only the "IF" code inside the worksheet_change everything works fine.
But i need a function.
Can anyone help me?
Thank you

Here is the example

Sub PrintSheet()
If Target.Address = "$F$2" Then
    If (Target.Value = "Work") Or (Target.Value = "Home") Then
        With Worksheets("sheet1").PageSetup
            .LeftHeader = ""
            .CenterHeader = ""
            .RightHeader = ""
            .LeftFooter = ""
            .CenterFooter = ""
            .RightFooter = ""
        End With
    Else
    End If
End If

 

[RoryA]

T

But i need a function

That's not a function.

Target is a range object supplied by the Change event. In your code it has no meaning. Why do you need Target at all if you only want to check F2?

 

[guimenez]

T

That's not a function.

Target is a range object supplied by the Change event. In your code it has no meaning. Why do you need Target at all if you only want to check F2?

Thanks for repying.
I'm not good at programming but i'm trying to setup my workbook.

I've remove the target.address and try the code bellow but i'm having now "Application-defined" or object-defined error"
Do you know what can i do more?
Thank you


I'm making a sub for printing that will compare a cell with a value, if the value is Work or Home it will remove the Footer from the print

Sub PrintSheet()
If ("$F$2" = "Work") Or ("$F$2" = "Home") Then
        With Worksheets("sheet1").PageSetup
            .LeftHeader = ""
            .CenterHeader = ""
            .RightHeader = ""
            .LeftFooter = ""
            .CenterFooter = ""
            .RightFooter = ""
        End With
    Else
    End If
End If

 

[Joe4]

Change this line:

If ("$F$2" = "Work") Or ("$F$2" = "Home") Then

to this:

If (Range("F2") = "Work") Or (Range("F2") = "Home") Then

and get rid of this:

    Else
    End If

as you only have one IF, not two.

So your final code should look like:

Sub PrintSheet()

If (Range("F2") = "Work") Or (Range("F2") = "Home") Then
        With Worksheets("sheet1").PageSetup
            .LeftHeader = ""
            .CenterHeader = ""
            .RightHeader = ""
            .LeftFooter = ""
            .CenterFooter = ""
            .RightFooter = ""
        End With
End If

End Sub

 

[guimenez]

Thank you once again.
Using your code, i still getting "Application-defined" or object-defined error".

My workbook has 2 sheets: Data and PrintView

The button that i use to click to print is inside PrintView sheet.
The F2 cell that i want to compare is inside Data sheet.
And the Sub PrintSheet() is inside Data sheet

Maybe this can help you more.

Thank you.

Change this line:

If ("$F$2" = "Work") Or ("$F$2" = "Home") Then

to this:

If (Range("F2") = "Work") Or (Range("F2") = "Home") Then

and get rid of this:

    Else
    End If

as you only have one IF, not two.

So your final code should look like:

Sub PrintSheet()

If (Range("F2") = "Work") Or (Range("F2") = "Home") Then
        With Worksheets("sheet1").PageSetup
            .LeftHeader = ""
            .CenterHeader = ""
            .RightHeader = ""
            .LeftFooter = ""
            .CenterFooter = ""
            .RightFooter = ""
        End With
End If

End Sub

 

[Joe4]

The button that i use to click to print is inside PrintView sheet.
The F2 cell that i want to compare is inside Data sheet.
And the Sub PrintSheet() is inside Data sheet

OK, a few issues there.

Since you are dealing with multiple sheets, you should use sheet references in your range references, i.e.

Sub PrintSheet()

If (Sheets("Data").Range("F2") = "Work") Or (Sheets("Data").Range("F2") = "Home") Then
        With Worksheets("sheet1").PageSetup
            .LeftHeader = ""
            .CenterHeader = ""
            .RightHeader = ""
            .LeftFooter = ""
            .CenterFooter = ""
            .RightFooter = ""
        End With
End If

End Sub

And you should NOT put this code inside a sheet module, especially since multiple sheets are involved.
You should put it in a General (or Standard) Module in your workbook.
See this here for how to insert a General Module: Insert and run VBA macros in Excel - step-by-step guide

By the way, taking a step back here, what EXACTLY are you trying to accomplish with this code?
Are you simply trying to remove all headers and footers from "sheet1"?
It looks like that is all your code is doing.

 

[guimenez]

OK, a few issues there.

Since you are dealing with multiple sheets, you should use sheet references in your range references, i.e.

Sub PrintSheet()

If (Sheets("Data").Range("F2") = "Work") Or (Sheets("Data").Range("F2") = "Home") Then
        With Worksheets("sheet1").PageSetup
            .LeftHeader = ""
            .CenterHeader = ""
            .RightHeader = ""
            .LeftFooter = ""
            .CenterFooter = ""
            .RightFooter = ""
        End With
End If

End Sub

And you should NOT put this code inside a sheet module, especially since multiple sheets are involved.
You should put it in a General (or Standard) Module in your workbook.
See this here for how to insert a General Module: Insert and run VBA macros in Excel - step-by-step guide

By the way, taking a step back here, what EXACTLY are you trying to accomplish with this code?
Are you simply trying to remove all headers and footers from "sheet1"?
It looks like that is all your code is doing.

Man you are a Genius
I've put the Sub in to Module1 and change the code like you said and its working flawless.
Now i will add the rest of the code to print.
Thank you so much my friend

 

[Joe4]

You are welcome.
Glad we were able to help!