Adding Sub code in Function module

[wpreqq99]

When the user types in a Y in column O, the statement below will put
today's date in Column Q. The cells in Column Q have this formula:
=IF(O2="y",dateStamp()," ")

Here's what I have in a module:
Function DateStamp()
DateStamp = Date
End Function

What I would like to do is to add something like this
ActiveCell.Formula = ActiveCell.Value

I want the cell in Column Q to be static after the Y in typed in.
Right now, that value will change each day.

I've tried to add the last line in the Function module, and it doesn't
work. I've tried to have a seperate Sub, but it won't go there from
the Function module.

Any suggestions?

 

[jasontferrell]

I do not think this is possible via a UDF. Functions that are
designed to return a value within a cell (and called from a cell)
cannot also change attributes of that cell, including formatting or
changing the formula of the cell itself.

 

[wpreqq99]

I do not think this is possible via a UDF.  Functions that are
designed to return a value within a cell (and called from a cell)
cannot also change attributes of that cell, including formatting or
changing the formula of the cell itself.

Thanks for replying.
I know there has to be a way to apply a static date stamp in a cell
when a user types something in another cell.
What I'm asking for in this post is another work around I've tried to
come up with this end result.
If anyone has an answer to how to do this, please let me know.

I would appreciate any help.
j.o.

 

[jasontferrell]

Ahhh, try this:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("O:O"), Target) Is Nothing Then
If LCase(Target.Value) = "y" Then
Target.Offset(0, 2).Value = Date
End If
End If
End Sub

It needs to go into the code module specific to the sheet on which you
want the functionality. (VBAProject->Microsoft Excel Objects->Sheet1,
for example).

 

[wpreqq99]

Ahhh, try this:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("O:O"), Target) Is Nothing Then
        If LCase(Target.Value) = "y" Then
            Target.Offset(0, 2).Value = Date
        End If
    End If
End Sub

It needs to go into the code module specific to the sheet on which you
want the functionality.  (VBAProject->Microsoft Excel Objects->Sheet1,
for example).

EXCELLENT
This is what I was looking for.
Many thanks
j.o.