Clearing Cells

[plh]

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").ClearContents
'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

 

[Gord Dibben]

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

 

[Bernd P]

Hello,

you cannot change cell contents with a function.

Take a sub instead.

Regards,
Bernd

 

[Bill Renaud]

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).

 

[plh]

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.

 

[Bill Renaud]

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