Worksheet change macro for paste special
 

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

Worksheet change macro for paste special
 

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

Worksheet change macro for paste special
 

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

Worksheet change macro for paste special
 

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


--
HTH,
Bernie
MS Excel MVP


"Phil" wrote in message
...
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

Worksheet change macro for paste special
 

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