Function writing cells

[kleysonr]

Hi

I've created a function that call a Sub to write some cells or delete a row.
But don't work.

If i call the Sub from a button it works fine.

I saw a post saying that a function can't change cells, only Sub can do
that. But i really need that a function call a Sub to delete a row for me
because i use that function in cells' formulas.

How can i do that ?

 

[Mike H]

Hi,

You can't. A function can only directly alter the cell it was called from
and a sub called from a function has the same limitations.

Mike

 

[kleysonr]

Mike,

How can I resolv my problem ?

I have a formula that check if a value cell is True or False, if it's True a
need remove a specific row.

 

[Mike H]

Hi,

Some 'other' cell (your value cell) must change it's value to set your
formula cell to evaluate as true/false and that would trigger the worksheet
change event which you could use to call your line deletion macro. The
formula cell changing between true/false wouldn't call the worksheet change
event

Mike

 

[Gord Dibben]

Maybe wrap your row deleting inside sheet event code.

Private Sub Worksheet_Calculate()

If target cell value = True then

run the macro to delete your row or just code it inside this event

End Sub


Gord Dibben MS Excel MVP

 

[kleysonr]

Mike sorry,

But i didn't understand very well. Could you give some example ?

For example:

cell A1: =IF(b1-b2<0;1;0)
You said if cell change, my worksheet_change won't call Sub. I need check
the value in another cell, right ? But, how can i do ?

 

[kleysonr]

Gord,

I tested but dont work.
I many cells with formula SE(), so i need identify the source of event.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, Range("B:B")) Is Nothing Then
If (Target.Range("B" & Target.Cells.Row).Value = 1) Then
MsgBox (": " & Target.Row)
End If
End If
Application.EnableEvents = True
End Sub

 

[Mike H]

Hi,

As I tried to explain before your cell changing from true to false won't
fire the worksheet change macro. You must pick a cell or range of cells that
physically change and cause the change from true to false and use these to
call your macro.

I don't understand the formula =SE() I assume this is the name of your UDF.

Mike

 

[kleysonr]

Mike sorry for my mistake,

=SE() is the function =IF() of Excell in Portuguese.

The datas for function IF() come of other cells that receive their values
from a link DDE. So i don't know if will work too.

But i need evaluate the calculation of many cells, not just one. If the
result of calculation is equal 1 i need remove the row where the formula is.

 

[Gord Dibben]

Won't work because you used change event rather than calculate event.

See if this works for you

Private Sub Worksheet_Calculate()
Const WS_RANGE As String = "B:B"
Dim cell As Range
On Error GoTo ws_exit:
Application.EnableEvents = False
For Each cell In Me.Range(WS_RANGE)
If cell.Value = 1 Then
MsgBox cell.Address
End If
Next cell
ws_exit:
Application.EnableEvents = True
End Sub

Note: if you have more than one result of 1 in the range you will get
multiple msgbox's at each re-calc.

But it's a start<g>


Gord

 

[kleysonr]

Hi Gord,

This work very good.
But look, i have many, many rows making calc in Real Time, so I think if,
for each calc it do a loop for the column, i can have a problem with
performance.

Do you think that there is a solution, like change event, that call the
macro passing the address of the specific cell that the formula has changed
its value ?

 

[Gord Dibben]

A change event is not triggered by a calculation in a cell.

Hopefully someone else has an idea for you.


Gord