Worksheet change macro for paste special

[Phil]

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
(e-mail address removed) cJnWtQdp
(e-mail address removed) dNPFioKg
(e-mail address removed) FIbWsMXz
(e-mail address removed) NrNZgDcD
<empty> =IF(P6="","",RandPass())

Can anyone help?

Many thanks,

Phil

 

[Bernie Deitrick]

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.

 

[Phil]

That is absolutely brilliant, Bernie!

Does the job a treat. many thanks for that.

One last queation - is it true I can only have one On Worksheet change
macro per sheet? I heard somewhere that was the case, not dure if it's
right.

Thanks again and best regards,

Phil

 

[Bernie Deitrick]

Phil,

Yes, you can have only one, but it can be complex:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then Goto Column1
If Target.Column = 2 Then Goto Column2
If Target.Column = 4 Then Goto Column4
Exit Sub
Column1:
'Code for column 1
Exit Sub
Column2:
'Code for column 2
Exit Sub
Column4:
'Code for column 4
Exit Sub

End Sub

 

[Phil]

Hmm....I see what you mean!

I think I'll come back to it when I'm a bit more proficient in VBA!

Many thanks,

Phil