Worksheet Calculate

[Guest]

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

 

[Guest]

Hi,

I've got the percentage worked out. I just need help with the
worksheet_change

Thanks

 

[Doug Glancy]

Karen,

It worked for me. Are you sure that the Calculate event is even being
triggered? You could put a breakpoint or a msgbox at the begin of the
routine to check. Have you possibly disabled Calculation or Events by
stopping your code in the middle? To test, in the immediate window of the
VBE (Ctrl-G) enter these two lines:

? Application.EnableEvents
? application.Calculation = xlCalculationAutomatic

Also, it seems like all of this could be handled by If statements in your
workbook, e.g., in cell J29:

If(H27 >0,"Rental Tax","")

Also, your formula "=R27,C12*R29,C11" is not a valid formula. Maybe you
meant:

"=Sum(R27,C12*R29,C11)"

hth,

Doug

 

[Bill Renaud]

I have several questions:

1. Should you be using the Worksheet_Change event instead of the
Worksheet_Calculate event? Changing the value of H27 does not cause a
recalculate event, because it does not affect any formulas anywhere.
2. Technically, I would use the Formula property instead of the Value
property in your statements. So, for example:
Me.Range("J29").Value = "Rental Tax" would become
Me.Range("J29").Formula = "Rental Tax".
This is especially true on the next line (see my question 3 below).
3. "=R27,C12*R29,C11" is not a valid formula! What is it supposed to be?
Put it in a cell, then copy the formula from there to your next post.

Turning off the events while the routine is running is correct. The routine
works OK on my machine otherwise, but you need to fix question 3 above.

As a final thought, are you sure that you need an event to do this? How
about just making the formula in J29 such that the value calculates to 0,
if the value in H27 is 0? You could format it so that it doesn't show when
the value is 0. I do this a lot in templates that I build. In other words,
format cell J29 with the following style:

CurrencyNonZero2d
$#,##0.00;-$#,##0.00;;@

Notice how the 3rd part (when the value is zero) is blank (two semi-colons
right together). Check out the Excel Help topic "Create a custom number
format".

 

[Guest]

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!

 

[Bill Renaud]

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(H27>0,"Rental Tax","")
K29: =IF(H27>0,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).

 

[Guest]

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(I29>0,"Rental Tax","")
=IF($I$29>0,SUM($K$27*$I$29,""))

Do you have any suggestions?

Thanks again for your help.

 

[Guest]

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.

 

[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(H27>0,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!)

 

[Guest]

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!

 

[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."