Phil,

Don't use the UDF in the sheet, use it in the change event: this assumes that the usernames are
entered one at a time into column A.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Target.Column < 1 Then Exit Sub
If Target.Value = "" Then Exit Sub

Application.EnableEvents = False
Target.Offset(0, 1).Value = RandPass()
Application.EnableEvents = True

End Sub

Copy the code above, right-click the sheet tab, select "View Code" and paste into the window that
appears.
--
HTH,
Bernie
MS Excel MVP


"Phil" wrote in message
...
Hi all,

I have a membership spreadsheet that I'm trying to create.

Two of the columns contain a username, and a password that is
generated using a UDF.

What I'd like to do is automatically copy and paste special/values
over the generated password in order to prevent the UDF running again,
thus changing the password.

I already have a formula in the password column which only creates a
password if there is a username present in the adjacent column, so I
only want the event macro to paste over a newly-generated passwords,
and preserving the formula if the username is absent.

Username Password
cJnWtQdp
dNPFioKg
FIbWsMXz
NrNZgDcD
<empty =IF(P6="","",RandPass())

Can anyone help?

Many thanks,

Phil