Object required Error with 'If If Target.Address = "$K$65" Then

[eli_m]

Hi,

I am getting the below error:

With:

Private Sub Worksheet_Change(ByVal Targer As Range)

If Target.Address = "$K$65" Then
    If Target.Value = "" Then
        Shapes.Range(Array("Step2")).Line.Visible = msoTrue
        Shapes.Range(Array("Step2.5")).Line.Visible = msoFalse
        Shapes.Range(Array("Step3")).Line.Visible = msoFalse
    Else
    If Target.Value > 0 Then
        Shapes.Range(Array("Step2")).Line.Visible = msoFalse
        Shapes.Range(Array("Step2.5")).Line.Visible = msoTrue
        Shapes.Range(Array("Step3")).Line.Visible = msoTrue
        End If
    End If
End If

End Sub

It's saying it has something do with this:

But I can't figure it out.

I have tested the cell with:
=IF($K$65="","EMPTY",IF($K$65>0,"YES",""))

And it shows EMPTY when there's nothing there and "YES" if the number is above 0 so it seems to work there but not in VBA.

Thank you in advance!

 

[Snakehips]

@eli_m Looks like you have a typo in declaration line of the sub?

Private Sub Worksheet_Change(ByVal Targer As Range)

Try Target

 

[eli_m]

@eli_m Looks like you have a typo in declaration line of the sub?

Private Sub Worksheet_Change(ByVal Targer As Range)

Try Target

great pick up!

Do you know why the rest of the code wouldn't work?
I've also tried:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$K$65" Then
    If Target.Value = "" Then
        Me.Shapes("Step2").Visible = msoTrue
        Me.Shapes("Step2.5").Visible = msoFalse
        Me.Shapes("Step3").Visible = msoFalse
    Else
    If Target.Value > 0 Then
        Me.Shapes("Step2").Visible = msoFalse
        Me.Shapes("Step2.5").Visible = msoTrue
        Me.Shapes("Step3").Visible = msoTrue
        End If
    End If
End If

End Sub

 

[Snakehips]

It works ok for me when testing on three shapes. In what way does it not work for you?
Are your shape names correct?

 

[Snakehips]

Is it that you want an entry of 0 to act same as entry of "" ???
If so then maybe like

Private Sub Worksheet_Change(ByVal Target As Range)
Debug.Print Target.Address
If Target.Address = "$K$65" Then
    If Target.Value = "" Or Target.Value < 1 Then '<<<<<????????
        Shapes.Range(Array("Step2")).Line.Visible = msoTrue
        Shapes.Range(Array("Step2.5")).Line.Visible = msoFalse
        Shapes.Range(Array("Step3")).Line.Visible = msoFalse
    Else
    If Target.Value > 0 Then
        Shapes.Range(Array("Step2")).Line.Visible = msoFalse
        Shapes.Range(Array("Step2.5")).Line.Visible = msoTrue
        Shapes.Range(Array("Step3")).Line.Visible = msoTrue
        End If
    End If
End If

End Sub

 

[eli_m]

Is it that you want an entry of 0 to act same as entry of "" ???
If so then maybe like

Private Sub Worksheet_Change(ByVal Target As Range)
Debug.Print Target.Address
If Target.Address = "$K$65" Then
    If Target.Value = "" Or Target.Value < 1 Then '<<<<<????????
        Shapes.Range(Array("Step2")).Line.Visible = msoTrue
        Shapes.Range(Array("Step2.5")).Line.Visible = msoFalse
        Shapes.Range(Array("Step3")).Line.Visible = msoFalse
    Else
    If Target.Value > 0 Then
        Shapes.Range(Array("Step2")).Line.Visible = msoFalse
        Shapes.Range(Array("Step2.5")).Line.Visible = msoTrue
        Shapes.Range(Array("Step3")).Line.Visible = msoTrue
        End If
    End If
End If

End Sub

This is the shape

Using all the codes about I can't seem to get it to disappear and reappear - am I using the wrong shape?

 

[Snakehips]

Like I say, it's working absolutely fine for me. I'm using three similar standard shapes.
So I'm stumped.
I notice that I didn't edit out the debug.print Target.Address line that I put in as a check.
It will do no real harm other than the address of Target will have been printed to the Immediate pane of your vba editor.
Best remove the line and clear the Immediate pane.
That said, you may want to see if using debug.print ???? gives you any clues as to what is not happening as you had hoped.

Is your intended trigger cell definitely K65 or if a merged cell the K65 is the top-left celll?
A really stupid question......Is the code in the code module of the right sheet?

 

[Snakehips]

Another thought.
Do have any sub that might contain 'Application.EnableEvents = False' in order to temporarily suspend the triggering of Event codes?
If so then it would require 'Application.EnableEvents = True' at the end of that code so as to re-establish normal Event triggering.

If, when you are testing such code, there is an error that causes you to escape the sub without getting to 'Application.EnableEvents = True' then your Event triggering will still be disabled!
If that is the case then none of your Event codes will run at all.

Try going to the vba editor Immediate pane and type Application.EnableEvents = True then hit return.
That will ensure Event Triggering is established. Then see what gives.

 

[eli_m]

Another thought.
Do have any sub that might contain 'Application.EnableEvents = False' in order to temporarily suspend the triggering of Event codes?
If so then it would require 'Application.EnableEvents = True' at the end of that code so as to re-establish normal Event triggering.

If, when you are testing such code, there is an error that causes you to escape the sub without getting to 'Application.EnableEvents = True' then your Event triggering will still be disabled!
If that is the case then none of your Event codes will run at all.

Try going to the vba editor Immediate pane and type Application.EnableEvents = True then hit return.
That will ensure Event Triggering is established. Then see what gives.

Ah I figured out the problem!

$K$65 is a formula:
=IFERROR(AVERAGEIF(K15:K64,"<>"),"")

So even though the number is over 1 it doesn't work.
Is there a way to make it work with a formula?

 

[RoryA]

You need the Worksheet_Calculate event, not the Change event, or if K15:K64 are literal values, monitor those cells in the change event, not the formula cell.