Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clearing Cells
Hello Everyone,
I thought this would be easy, but I must have something fundamentally wrong. The following function has various cell-clearing test code lines in it, but none have any effect on the contents of the cell. I have tried many other ways to do it besides what is below. Function ClearIt(v As Double) As Boolean ActiveWorkbook.ActiveSheet.Range("MyData").Select Selection.ClearContents ActiveWorkbook.ActiveSheet.Range("MyData").ClearCo ntents 'Range("C5").Value = "" ActiveWorkbook.ActiveSheet.Cells(5, 3).ClearContents End Function Changes to C5 fire the function as D5 contains =ClearIt(C5). My ultimate goal is to have a change to any of the cells in the range "MyData" fire "ClearIt" which will decide whether or not to clear the cells based on a set of logical rules. But I am stuck on what I thought was a very simple test! Can anyone help me? Thanx, -plh -- Where are we going and why am I in this HAND BASKET?? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clearing Cells
Functions can only return results to cells in which they are entered.
They cannot format or clear cells. Perhaps event code would serve you better. See David McRitchie's site for event code. http://www.mvps.org/dmcritchie/excel/event.htm Or Chip Pearson's site for similar info. http://www.cpearson.com/excel/Events.aspx Gord Dibben MS Excel MVP On 30 Sep 2007 14:36:22 -0700, plh wrote: Hello Everyone, I thought this would be easy, but I must have something fundamentally wrong. The following function has various cell-clearing test code lines in it, but none have any effect on the contents of the cell. I have tried many other ways to do it besides what is below. Function ClearIt(v As Double) As Boolean ActiveWorkbook.ActiveSheet.Range("MyData").Select Selection.ClearContents ActiveWorkbook.ActiveSheet.Range("MyData").ClearCo ntents 'Range("C5").Value = "" ActiveWorkbook.ActiveSheet.Cells(5, 3).ClearContents End Function Changes to C5 fire the function as D5 contains =ClearIt(C5). My ultimate goal is to have a change to any of the cells in the range "MyData" fire "ClearIt" which will decide whether or not to clear the cells based on a set of logical rules. But I am stuck on what I thought was a very simple test! Can anyone help me? Thanx, -plh |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clearing Cells
Hello,
you cannot change cell contents with a function. Take a sub instead. Regards, Bernd |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clearing Cells
You cannot change any cells outside of the cell that the function was
called from, if the function was called from a worksheet cell. You CAN change other cells inside of a function only if the function is called from a VBA macro. You can put the line... Application.Caller ....inside a VBA routine to return information about how Visual Basic was called (see "Caller Property" in Microsoft Excel Visual Basic Reference). -- Regards, Bill Renaud |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clearing Cells
The information in these links did the trick, thank you!
-plh PS, Does everyone top post in these groups? I don't mind doing as the Romans do, but it separates my sig line from my message. In article , Gord Dibben says... Functions can only return results to cells in which they are entered. They cannot format or clear cells. Perhaps event code would serve you better. See David McRitchie's site for event code. http://www.mvps.org/dmcritchie/excel/event.htm Or Chip Pearson's site for similar info. http://www.cpearson.com/excel/Events.aspx Gord Dibben MS Excel MVP On 30 Sep 2007 14:36:22 -0700, plh wrote: Hello Everyone, I thought this would be easy, but I must have something fundamentally wrong. The following function has various cell-clearing test code lines in it, but none have any effect on the contents of the cell. I have tried many other ways to do it besides what is below. Function ClearIt(v As Double) As Boolean ActiveWorkbook.ActiveSheet.Range("MyData").Select Selection.ClearContents ActiveWorkbook.ActiveSheet.Range("MyData").ClearCo ntents 'Range("C5").Value = "" ActiveWorkbook.ActiveSheet.Cells(5, 3).ClearContents End Function Changes to C5 fire the function as D5 contains =ClearIt(C5). My ultimate goal is to have a change to any of the cells in the range "MyData" fire "ClearIt" which will decide whether or not to clear the cells based on a set of logical rules. But I am stuck on what I thought was a very simple test! Can anyone help me? Thanx, -plh -- Where are we going and why am I in this HAND BASKET?? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clearing Cells
plh wrote:
<<Does everyone top post in these groups? I don't mind doing as the Romans do, but it separates my sig line from my message. Yes. It makes it easier to see your reply without having to scroll to the bottom of a very long post (most people use newsreaders, not web-based readers). See Chip Pearson's web site tips for new posters (see rule # 11). http://www.cpearson.com/excel/newposte.htm -- Regards, Bill Renaud |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Clearing cells without clearing formulas | Excel Discussion (Misc queries) | |||
clearing cells | Excel Worksheet Functions | |||
Clearing cells | Excel Programming | |||
Clearing Cells | New Users to Excel | |||
Clearing cells | Excel Programming |