Hi,

Thank you both for responding.

The cell this is looking at is linked to another worksheet. When they enter
the value in that worksheet, this one updates which I thought would trigger
the worksheet calculate. Shouldn't this trigger it?

I was doing it this way because my boss does not want to see "Rental Tax" or
a 0 for the amount if the rental tax does not apply on this sheet.

You have given me some great information. I will try your suggestions
tomorrow morning at work.

Thanks!
--

Karen


"Karen53" wrote:

Hi,

I am trying to do a worksheet calculate. Initially, this would go into an
endless loop. I added the Application.EnableEvents = False and True but it
makes no changes. What do I need to do?

Private Sub Worksheet_Calculate()

On Error GoTo ws_exit
Application.EnableEvents = False

Select Case Range("H27")
Case Is 0
Me.Range("J29").Value = "Rental Tax"
Me.Range("K29").Value = "=R27,C12*R29,C11"
Case Is = 0
Me.Range("J29").Value = ""
Me.Range("K29").Value = ""
End Select

ws_exit:
Application.EnableEvents = True
End Sub

Also, my rental tax goes into a textbox as .024 for a value of 2.40% on the
main form. Then the link picks this up on another sheet and it becomes
240.00%. Is there a way to keep it from multiplying each time it's picked up?

Thanks
--

Karen

The way I would have set up the worksheet would have been something like
the following:

H27: 0 or 1 (the flag that says "Rental Tax" is applicable?)

J29: =IF(H270,"Rental Tax","")
K29: =IF(H270,R27,C12*R29,C11,"")

or whatever the formula is supposed to be (=R27,C12*R29,C11 does not look
like a valid formula to me).
--
Regards,
Bill Renaud

Hi Bill,

Thanks for your help.

I have gotten that far but I am having trouble with the linked cells not
showing when they are 0. The one I'm working on now is percent. I have
tried various combinations. Either they still show the % sign, then my
formula picks it up as not being empty which gives me a "False' for the
rental tax amount. Or, I get a decimal, not a percentage which my formula
then returns #Value.

[0]0.00;;%

This is one that I've tried. Excel puts a slash in.
[0]0.00/%
If the parent cell is empty, I get just a % sign. If the parent cell has a
value in it, I get a decimal but no % sign. I know they are not going to
want the % sign to show if the value is 0.

Here are my other formulas
=IF(I290,"Rental Tax","")
=IF($I$290,SUM($K$27*$I$29,""))

Do you have any suggestions?

Thanks again for your help.
--

Karen


"Bill Renaud" wrote:

The way I would have set up the worksheet would have been something like
the following:

H27: 0 or 1 (the flag that says "Rental Tax" is applicable?)

J29: =IF(H270,"Rental Tax","")
K29: =IF(H270,R27,C12*R29,C11,"")

or whatever the formula is supposed to be (=R27,C12*R29,C11 does not look
like a valid formula to me).
--
Regards,
Bill Renaud

Perhaps some more clarification.

I29 contains a percent. It is a link from another sheet.
If there is no percent, the "Rental Tax" and the rental tax formula do not
appear.

Thanks again.
--

Karen


"Bill Renaud" wrote:

The way I would have set up the worksheet would have been something like
the following:

H27: 0 or 1 (the flag that says "Rental Tax" is applicable?)

J29: =IF(H270,"Rental Tax","")
K29: =IF(H270,R27,C12*R29,C11,"")

or whatever the formula is supposed to be (=R27,C12*R29,C11 does not look
like a valid formula to me).
--
Regards,
Bill Renaud

For a cell that contains a percentage, try the following format:

#,##0.00%;-#,##0.00%;;@

If the value is either positive or negative, it will display. If the value
is 0.00%, then it will not display. This is the style that I use in my
workbooks when I want to hide values or formulas that are (or evaluate to)
exactly 0 (zero).

For a formula that returns a value (not a string), like the following:

K29: =IF(H270,R27+C12*R29+C11,0)

then make the IF statement give a value of 0, if the IF condition is FALSE.
Then format the cell as above with a blank part for the negative
specification to cause the 0 value to not be shown.

(I changed the commas in your example formula to "+" signs. I still don't
understand what your formula is doing!)
--
Regards,
Bill Renaud

Hi Bill,

This is great!

The percent worked perfectly. I had a little trouble with the formula
format. Here is what worked for me.

_($* #,##0.00_);_($* (#,##0.00);;@

The only trouble is it won't let me set this format from VBA.

Range("K29").NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);;@"

Every time I try it, it reverts to the standard 'Accounting' format. Yet I
can set it live on the worksheet.

These formulas are being inserted via VBA so the R1C1 format needs to be
used. R is row and C is column.

This is great! I didn't realize I could do this! Thank you!
--

Karen


"Bill Renaud" wrote:

For a cell that contains a percentage, try the following format:

#,##0.00%;-#,##0.00%;;@

If the value is either positive or negative, it will display. If the value
is 0.00%, then it will not display. This is the style that I use in my
workbooks when I want to hide values or formulas that are (or evaluate to)
exactly 0 (zero).

For a formula that returns a value (not a string), like the following:

K29: =IF(H270,R27+C12*R29+C11,0)

then make the IF statement give a value of 0, if the IF condition is FALSE.
Then format the cell as above with a blank part for the negative
specification to cause the 0 value to not be shown.

(I changed the commas in your example formula to "+" signs. I still don't
understand what your formula is doing!)
--
Regards,
Bill Renaud

I had no trouble at all using VBA to assign this format to a cell:

Sub FormatPctNonZero()
Selection.NumberFormat = "#,##0.00%;-#,##0.00%;;@"
End Sub

Alternatively, if you have a Style (i.e. "PctNonZero [1]") defined that
applies this format, then the VBA code would look like the following:

Sub FormatPctNonZeroStyle()
Selection.Style = "PctNonZero [1]"
End Sub

Just be aware that to use this method, the Style must exist in the
workbook, otherwise you will get the following run-time error (at least in
Excel 2000):

"Run-time error '450':
"Wrong number of arguments or invalid property assignment."

--
Regards,
Bill Renaud