| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 ? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 ? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 ? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 ? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 ? |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 ? |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 ? |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 ? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| need help writing function | Excel Worksheet Functions | |||
| Function writing help | Excel Worksheet Functions | |||
| WRITING A WHAT IF FUNCTION | Excel Worksheet Functions | |||
| Help Writing Function | Excel Worksheet Functions | |||
| writing its own function | Excel Programming | |||
Hybrid Mode
