Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cut & Paste Need Special Macro | Excel Discussion (Misc queries) | |||
PASTE SPECIAL w/ Macro | Excel Discussion (Misc queries) | |||
Copy & Paste Special Macro | Excel Discussion (Misc queries) | |||
Copy Paste Special Macro | Excel Discussion (Misc queries) | |||
Paste Special in a macro | Excel Worksheet Functions |