arrow object

[reddcannon]

I have an arrow that I got by going to insert/shapes etc. But I do not want it to show unless cell M5 > 0, can someone advise

 

[Smitty]

Welcome to the Board!

You can probably do what you want with VBA, but why not use Conditional Formatting with the Arrow Icon Set?

If you want to stick with your arrow, then how is M5 changing? Via formula or manually?

 

[reddcannon]

I tried that anc cannot get the arrow where I want it on the sheet. Any suggestions on VBA

 

[reddcannon]

also it needs to change when that cell becomes greater >0 by way of entries in other cells

 

[Smitty]

also it needs to change when that cell becomes greater >0 by way of entries in other cells

So M5 is changing as the result of a formula?

If so, then something like this should work:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Calculate()<br>    <SPAN style="color:#00007F">If</SPAN> Range("M5").Value > 0 <SPAN style="color:#00007F">Then</SPAN><br>        Me.Shapes("Arrow1").Visible = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">Else</SPAN><br>        Me.Shapes("Arrow1").Visible = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

HTH,

 

[reddcannon]

yes, as a person enters qty in column b in various places the sum of M5 changes. I just said Greater than 0, it can be where M5 is greater then 100 etc. It's purpose is to make sure the person entering the data notices to tell the person on the phone that they are entitled to a freight allowance because the order reached a certain amount

 

[Smitty]

It's purpose is to make sure the person entering the data notices to tell the person on the phone that they are entitled to a freight allowance because the order reached a certain amount

You'd probably want to use a Message Box instead then. No one's going to miss that.

 

[reddcannon]

your right, if I knew how to do that, I am new to this

 

[Smitty]

your right, if I knew how to do that, I am new to this

The good news is it's remarkably easy:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Calculate()<br>    <SPAN style="color:#00007F">If</SPAN> Range("M5").Value > 0 <SPAN style="color:#00007F">Then</SPAN><br>        MsgBox "Your customer is eligible for a shipping discount!", vbInformation + vbOKOnly, "Shipping DIscount Achieved"<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[reddcannon]

it works, however it pops up even though the value of M5 is nothing. There is a calculation in M5, maybe that is the problem here is what is in M5 =IF(B5="n","",IF(B5="m","",IF(M2<1000,"",M2*5%)))