Function writing cells
 

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 ?

Function writing cells
 

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" wrote:

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 ?

Function writing cells
 

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" wrote:

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" wrote:

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 ?

Function writing cells
 

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

"kleysonr" wrote:

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" wrote:

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" wrote:

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 ?

Function writing cells
 

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


On Sat, 16 Aug 2008 09:27:01 -0700, kleysonr
wrote:

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" wrote:

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" wrote:

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 ?

Function writing cells
 

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 ?

"Mike H" wrote:

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

"kleysonr" wrote:

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" wrote:

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" wrote:

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 ?

Function writing cells
 

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

On B1 i have a formula =SE(), B2 i have a formula =SE() etc
But when function SE change cell value excel don't call Worksheet_Change.

"Gord Dibben" wrote:

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


On Sat, 16 Aug 2008 09:27:01 -0700, kleysonr
wrote:

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" wrote:

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" wrote:

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 ?

Function writing cells
 

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" wrote:

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

On B1 i have a formula =SE(), B2 i have a formula =SE() etc
But when function SE change cell value excel don't call Worksheet_Change.

"Gord Dibben" wrote:

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


On Sat, 16 Aug 2008 09:27:01 -0700, kleysonr
wrote:

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" wrote:

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" wrote:

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 ?

Function writing cells
 

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.

"Mike H" wrote:

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" wrote:

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

On B1 i have a formula =SE(), B2 i have a formula =SE() etc
But when function SE change cell value excel don't call Worksheet_Change.

"Gord Dibben" wrote:

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


On Sat, 16 Aug 2008 09:27:01 -0700, kleysonr
wrote:

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" wrote:

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" wrote:

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 ?

Function writing cells
 

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

On Sat, 16 Aug 2008 10:09:01 -0700, kleysonr
wrote:

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

On B1 i have a formula =SE(), B2 i have a formula =SE() etc
But when function SE change cell value excel don't call Worksheet_Change.

"Gord Dibben" wrote:

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


On Sat, 16 Aug 2008 09:27:01 -0700, kleysonr
wrote:

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" wrote:

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" wrote:

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 ?

Function writing cells
 

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" wrote:

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

On Sat, 16 Aug 2008 10:09:01 -0700, kleysonr
wrote:

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

On B1 i have a formula =SE(), B2 i have a formula =SE() etc
But when function SE change cell value excel don't call Worksheet_Change.

"Gord Dibben" wrote:

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


On Sat, 16 Aug 2008 09:27:01 -0700, kleysonr
wrote:

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" wrote:

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" wrote:

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 ?

Function writing cells
 

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

Hopefully someone else has an idea for you.


Gord

On Sat, 16 Aug 2008 12:01:02 -0700, kleysonr
wrote:

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" wrote:

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

On Sat, 16 Aug 2008 10:09:01 -0700, kleysonr
wrote:

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

On B1 i have a formula =SE(), B2 i have a formula =SE() etc
But when function SE change cell value excel don't call Worksheet_Change.

"Gord Dibben" wrote:

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


On Sat, 16 Aug 2008 09:27:01 -0700, kleysonr
wrote:

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" wrote:

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" wrote:

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 ?