VBA conditional format of a shape ....not working

[dmj120]

Trying to expand my knowledge whilst creating org-wide, shared excel workbooks.

I found a nice youTube video that's what I'm looking for - except multiple values.

So.......... upon clicking a button, cell F1 = 0. I have an arrow pointing to another cell, which I'd like to "hide" as other cells (normal conditional formatting). The issue is: there's no error, and the shape doesn't 'disappear.'

The last sub "Worksheet_ClearZeroPrice" below is what I'm referring to.

Any suggestions???

Option Explicit
Sub Reset1()

Range("B2:B6").Value = Null  'clears search criteria

Range("A10:M" & Rows.Count).ClearContents  'clears resultant list of A10:M
Range("A9:M" & Rows.Count).Borders.LineStyle = xlNone 'clears previous "removeSpecials macro" borders
Range("E6").ClearContents  'clears high value
Range("F1").Value = "0"  'zeros out
Range("E7").Value = "20"   'sets low value
Range("D3").Value = "verify 365 rnd"
Range("B5").Value = "fswp"  'sets default coverage

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, Range("D4")) Is Nothing Then
Application.Undo
End If
Application.EnableEvents = True

End Sub

Private Sub Worksheet_ClearZeroPrice(ByVal Target As Range)

If Target.Adress = "$F$1" Then
   If Target.Value = 0 Then
      Shapes.Range(Array("Straight Arrow Connector 9")).Line.Visible = msoFalse
   End If
End If

End Sub

 

[6StringJazzer]

FIrst of all how are you calling sub Worksheet_ClearZeroPrice? It is not called in any of the code you show here. In the video you referenced they use Worksheet_Change, which is an event handler.

Also I don't know why he coded it like that. I am not sure it's wrong but I would do this (untested):

      Shapes("Straight Arrow Connector 9").Line.Visible = False

 

[dmj120]

FIrst of all how are you calling sub Worksheet_ClearZeroPrice? It is not called in any of the code you show here. In the video you referenced they use Worksheet_Change, which is an event handler.

Also I don't know why he coded it like that. I am not sure it's wrong but I would do this (untested):

      Shapes("Straight Arrow Connector 9").Line.Visible = False

Not sure, I simply added the code at the bottom thinking it would work - after I did the same thing for the "middle piece" which Candyman8019 helped with.

 

[dmj120]

I'm getting closer..... changed the last part to the below, which works to hide the arrow (F1 = 0), but when the value in F1 changes to other number the arrow does not reappear.

Any suggestions on how to get this last piece working?

Private Sub ClearZeroPrice()

Dim Cell As Range

Set Cell = Me.Range("$F$1")

If Cell.Value > 0 Then
    Me.Shapes("Straight Arrow Connector 9").Visible = msoTrue
    
Else
    If Cell.Value = 0 Then
        Me.Shapes("Straight Arrow Connector 9").Visible = msoFalse
    End If
End If

End Sub

 

[6StringJazzer]

Still need to know how you are calling ClearZeroPrice.

 

[dmj120]

Is this what you're talking about?

 

[6StringJazzer]

No.

How do you make your macro run?