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

dmj120

Active Member
Joined
Jan 5, 2010
Messages
308
Office Version
  1. 365
  2. 2019
  3. 2010
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???

VBA Code:
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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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):
VBA Code:
      Shapes("Straight Arrow Connector 9").Line.Visible = False
 
Upvote 0
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):
VBA Code:
      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.
 
Upvote 0
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?

VBA Code:
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
 
Upvote 0
Is this what you're talking about?
1671060274987.png
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top