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!

 

[eli_m]

So

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.

Thanks for your help but I am unsure where to even start. I did the change event and got:

Code I am using is:

Private Sub Worksheet_Calculate(ByVal Target As Range)
If Target.Address = "$K$65" Then
    If Target.Value = "" Or Target.Value < 1 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

I then tried:
If Target.Address = "K15:K64" Then
and that didn't work either

 

[RoryA]

The calculate event doesn't provide a Target argument, so it would just be:

Private Sub Worksheet_Calculate()
dim formulaCell as Range
set formulaCell = Range("K65")

    If formulaCell.Value = "" Or formulaCell.Value < 1 Then
        Me.Shapes("Step2").Visible = msoTrue
        Me.Shapes("Step2.5").Visible = msoFalse
        Me.Shapes("Step3").Visible = msoFalse
    ElseIf formulaCell.Value > 0 Then
        Me.Shapes("Step2").Visible = msoFalse
        Me.Shapes("Step2.5").Visible = msoTrue
        Me.Shapes("Step3").Visible = msoTrue
    End If

End Sub

I don't really understand your criteria though as they overlap.

 

[eli_m]

The calculate event doesn't provide a Target argument, so it would just be:

Private Sub Worksheet_Calculate()
dim formulaCell as Range
set formulaCell = Range("K65")

    If formulaCell.Value = "" Or formulaCell.Value < 1 Then
        Me.Shapes("Step2").Visible = msoTrue
        Me.Shapes("Step2.5").Visible = msoFalse
        Me.Shapes("Step3").Visible = msoFalse
    ElseIf formulaCell.Value > 0 Then
        Me.Shapes("Step2").Visible = msoFalse
        Me.Shapes("Step2.5").Visible = msoTrue
        Me.Shapes("Step3").Visible = msoTrue
    End If

End Sub

I don't really understand your criteria though as they overlap.

Sadly that didn't work. K15:K64 are literal values how could I use that?

 

[RoryA]

Is K65 definitely the right cell? You have P12 in the picture but K65 in the code you posted.

 

[eli_m]

Is K65 definitely the right cell? You have P12 in the picture but K65 in the code you posted.

Yep - K65:

 

[eli_m]

Yep - K65:

View attachment 109798

Just realised I need it to be 0.01 instead of just 1 as its percentages. Works now! Thank you so much!

 

[RoryA]

Glad we could help.