[RESOLVED] Excel 97: onChange?

[Guru]

I've got the following code in cell d1
(Username() is a function in a module which gets the user's username)

=IF(ISBLANK(A1),"",UserName())

What I want is to set D1 to the user name only when A1 changes from blank to not-blank

At the moment if A1 is non-blank D1 get updated whenever the spreadsheet is opened

So I supose I need a sort of onchange event on A1
How?

Thanks

[DKenny]

You can use the _Change event of the worksheet - checking to see if A1 was the cell being changed. Then based on what change was made to A1 you can either add or remove the formula from cell D1.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    'Only continue if the user is
    'changing cell A1
    If Target.Address = "$A$1" Then
        'If A1 is not blank then add
        'the formula to D1
        If Target.Value <> "" Then
            Range("D1").Formula = "=IF(ISBLANK(A1),"""",UserName())"
        'Otherwise remove the formula
        Else
            Range("D1").Formula =""
        End If
    End If
End Sub

[Guru]

O...K...

Thanks for the reply, it looks like we're heading in the right direction
but I wasn't telling the whole truth.

I need to cover the whole column so if A1 changes, D1 gets populated
but also if ANY cell in column A changes the corresponding D cell needs to be set.

Any ideas?

Thanks

[DKenny]

OK
In that case you need to check the Column number of the cell being changed to see if it is column A and then change the formula in the cell 3 to the right of that cell, i.e. in column D.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    'Only continue if the user is
    'changing column A
    If Target.Column = 1 Then
        'If the cell in column A
        'is not blank then add
        'the formula to the coresponding
        'cell in column D
        If Target.Value <> "" Then
            Target.Offset(0, 3).Formula = "=IF(ISBLANK(A1),"""",UserName())"
        'Otherwise remove the formula
        Else
            Target.Offset(0, 3).Formula = ""
        End If
    End If
    
End Sub

[Guru]

Thanks DKenny

I was just working on it myself and came up with this:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column = 1 Then
    
        If Target.Value <> "" Then
            Target.Worksheet.Cells(Target.Row, 4) = UserName()
           
        Else
            Target.Worksheet.Cells(Target.Row, 4) = ""
        End If
        
    End If
End Sub

Thanks for the help dude!