Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheets(i).Select - driving me crazy!
Can anyone help. I'm trying to put a Workbook_Open macro in place, and
sometimes it works, other times it doesn't. It all seems to hinge on whether the worksheets(i).select works or not. I've tried lots of variations of the same code, all of which appear fine on the surface, and all of which have worked to various degrees - but generally only the first time I try it. Subsequent attempts seem to make changes on the worksheet that is already selected when saving with macros disabled, but not on the worksheet where the changes are required. The following is my latest attempt; Private Sub Workbook_Open() For i = 1 To ActiveWorkbook.Worksheets.Count Worksheets(i).Select If Worksheets(i).Name = "Audit" Then Range("A2").Value = Application.UserName Range("B2").Value = Date Range("C2").Value = Time Randomize Range("D2").Value = Rnd() Worksheets(i).Protect Password:="A" & Int(Rnd() * 10000000000#) Worksheets(i).Visible = False End If Next i End Sub This worked perfectly the first time I tried it, but when I tried to adjust the code to move to a particular sheet after it had finished the loop, it no longer worked, and worse, returning the code to the original "working" state above, it still doesn't work! There must be something I'm doing wrong, but what is it? Thanks for any suggestions, Geoff. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheets(i).Select - driving me crazy!
See if this works any better
Private Sub Workbook_Open() With Worksheets("Audit") Then .Range("A2").Value = Application.UserName .Range("B2").Value = Date .Range("C2").Value = Time Randomize .Range("D2").Value = Rnd() .Protect Password:="A" & Int(Rnd() * 10000000000#) .Visible = False End With End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Geoff C" wrote in message ... Can anyone help. I'm trying to put a Workbook_Open macro in place, and sometimes it works, other times it doesn't. It all seems to hinge on whether the worksheets(i).select works or not. I've tried lots of variations of the same code, all of which appear fine on the surface, and all of which have worked to various degrees - but generally only the first time I try it. Subsequent attempts seem to make changes on the worksheet that is already selected when saving with macros disabled, but not on the worksheet where the changes are required. The following is my latest attempt; Private Sub Workbook_Open() For i = 1 To ActiveWorkbook.Worksheets.Count Worksheets(i).Select If Worksheets(i).Name = "Audit" Then Range("A2").Value = Application.UserName Range("B2").Value = Date Range("C2").Value = Time Randomize Range("D2").Value = Rnd() Worksheets(i).Protect Password:="A" & Int(Rnd() * 10000000000#) Worksheets(i).Visible = False End If Next i End Sub This worked perfectly the first time I tried it, but when I tried to adjust the code to move to a particular sheet after it had finished the loop, it no longer worked, and worse, returning the code to the original "working" state above, it still doesn't work! There must be something I'm doing wrong, but what is it? Thanks for any suggestions, Geoff. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheets(i).Select - driving me crazy!
Thanks Bob, that gets close, it is at least making the changes in the right
place. However the critical two last lines in the block aren't happening? .Protect Password:="A" & Int(Rnd() * 10000000000#) .Visible = False Geoff. "Bob Phillips" wrote: See if this works any better Private Sub Workbook_Open() With Worksheets("Audit") Then .Range("A2").Value = Application.UserName .Range("B2").Value = Date .Range("C2").Value = Time Randomize .Range("D2").Value = Rnd() .Protect Password:="A" & Int(Rnd() * 10000000000#) .Visible = False End With End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Geoff C" wrote in message ... Can anyone help. I'm trying to put a Workbook_Open macro in place, and sometimes it works, other times it doesn't. It all seems to hinge on whether the worksheets(i).select works or not. I've tried lots of variations of the same code, all of which appear fine on the surface, and all of which have worked to various degrees - but generally only the first time I try it. Subsequent attempts seem to make changes on the worksheet that is already selected when saving with macros disabled, but not on the worksheet where the changes are required. The following is my latest attempt; Private Sub Workbook_Open() For i = 1 To ActiveWorkbook.Worksheets.Count Worksheets(i).Select If Worksheets(i).Name = "Audit" Then Range("A2").Value = Application.UserName Range("B2").Value = Date Range("C2").Value = Time Randomize Range("D2").Value = Rnd() Worksheets(i).Protect Password:="A" & Int(Rnd() * 10000000000#) Worksheets(i).Visible = False End If Next i End Sub This worked perfectly the first time I tried it, but when I tried to adjust the code to move to a particular sheet after it had finished the loop, it no longer worked, and worse, returning the code to the original "working" state above, it still doesn't work! There must be something I'm doing wrong, but what is it? Thanks for any suggestions, Geoff. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheets(i).Select - driving me crazy!
Odd, it works for me. But I do wonder how you will unprotect it as you don't
know the password. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Geoff C" wrote in message ... Thanks Bob, that gets close, it is at least making the changes in the right place. However the critical two last lines in the block aren't happening? .Protect Password:="A" & Int(Rnd() * 10000000000#) .Visible = False Geoff. "Bob Phillips" wrote: See if this works any better Private Sub Workbook_Open() With Worksheets("Audit") Then .Range("A2").Value = Application.UserName .Range("B2").Value = Date .Range("C2").Value = Time Randomize .Range("D2").Value = Rnd() .Protect Password:="A" & Int(Rnd() * 10000000000#) .Visible = False End With End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Geoff C" wrote in message ... Can anyone help. I'm trying to put a Workbook_Open macro in place, and sometimes it works, other times it doesn't. It all seems to hinge on whether the worksheets(i).select works or not. I've tried lots of variations of the same code, all of which appear fine on the surface, and all of which have worked to various degrees - but generally only the first time I try it. Subsequent attempts seem to make changes on the worksheet that is already selected when saving with macros disabled, but not on the worksheet where the changes are required. The following is my latest attempt; Private Sub Workbook_Open() For i = 1 To ActiveWorkbook.Worksheets.Count Worksheets(i).Select If Worksheets(i).Name = "Audit" Then Range("A2").Value = Application.UserName Range("B2").Value = Date Range("C2").Value = Time Randomize Range("D2").Value = Rnd() Worksheets(i).Protect Password:="A" & Int(Rnd() * 10000000000#) Worksheets(i).Visible = False End If Next i End Sub This worked perfectly the first time I tried it, but when I tried to adjust the code to move to a particular sheet after it had finished the loop, it no longer worked, and worse, returning the code to the original "working" state above, it still doesn't work! There must be something I'm doing wrong, but what is it? Thanks for any suggestions, Geoff. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheets(i).Select - driving me crazy!
When you say that the code runs fine the first time........ but not after
that, it indicates to me that perhaps the problem is in locked cells: 1 if cells A2:D2 that you are attempting to change with this code, are locked, and you have protected the worksheet with a random password by running the code the first time, then you will be unable to unprotect the worksheet thereafter. Subsequent attempts at running the code will fail. Is this the problem? "Geoff C" wrote: Can anyone help. I'm trying to put a Workbook_Open macro in place, and sometimes it works, other times it doesn't. It all seems to hinge on whether the worksheets(i).select works or not. I've tried lots of variations of the same code, all of which appear fine on the surface, and all of which have worked to various degrees - but generally only the first time I try it. Subsequent attempts seem to make changes on the worksheet that is already selected when saving with macros disabled, but not on the worksheet where the changes are required. The following is my latest attempt; Private Sub Workbook_Open() For i = 1 To ActiveWorkbook.Worksheets.Count Worksheets(i).Select If Worksheets(i).Name = "Audit" Then Range("A2").Value = Application.UserName Range("B2").Value = Date Range("C2").Value = Time Randomize Range("D2").Value = Rnd() Worksheets(i).Protect Password:="A" & Int(Rnd() * 10000000000#) Worksheets(i).Visible = False End If Next i End Sub This worked perfectly the first time I tried it, but when I tried to adjust the code to move to a particular sheet after it had finished the loop, it no longer worked, and worse, returning the code to the original "working" state above, it still doesn't work! There must be something I'm doing wrong, but what is it? Thanks for any suggestions, Geoff. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Worksheets(i).Select - driving me crazy!
Good thinking, but the workbook is being used as a template - once the
protected parts are protected, it's saved as a new file, so the template is always supposed to remain unprotected (hope that answers!) Bob, thanks for your suggestions - I made a small change in the code you supplied, and it worked. Hurray. I then copied exactly the same code to a related workbook - and it didn't work!!! I guess this is something to do with my computer, not Excel. I have at least got a version of the original file that is working, so I'm going to save it and hope I never need to change it again. btw, the random protection is supposed to permanently protect the worksheet so that it can't be unprotected (I know you can't stop the user getting hold of the password cracker, but it just makes it that bit harder) Thanks all, Geoff. "cush" wrote: When you say that the code runs fine the first time........ but not after that, it indicates to me that perhaps the problem is in locked cells: 1 if cells A2:D2 that you are attempting to change with this code, are locked, and you have protected the worksheet with a random password by running the code the first time, then you will be unable to unprotect the worksheet thereafter. Subsequent attempts at running the code will fail. Is this the problem? "Geoff C" wrote: Can anyone help. I'm trying to put a Workbook_Open macro in place, and sometimes it works, other times it doesn't. It all seems to hinge on whether the worksheets(i).select works or not. I've tried lots of variations of the same code, all of which appear fine on the surface, and all of which have worked to various degrees - but generally only the first time I try it. Subsequent attempts seem to make changes on the worksheet that is already selected when saving with macros disabled, but not on the worksheet where the changes are required. The following is my latest attempt; Private Sub Workbook_Open() For i = 1 To ActiveWorkbook.Worksheets.Count Worksheets(i).Select If Worksheets(i).Name = "Audit" Then Range("A2").Value = Application.UserName Range("B2").Value = Date Range("C2").Value = Time Randomize Range("D2").Value = Rnd() Worksheets(i).Protect Password:="A" & Int(Rnd() * 10000000000#) Worksheets(i).Visible = False End If Next i End Sub This worked perfectly the first time I tried it, but when I tried to adjust the code to move to a particular sheet after it had finished the loop, it no longer worked, and worse, returning the code to the original "working" state above, it still doesn't work! There must be something I'm doing wrong, but what is it? Thanks for any suggestions, Geoff. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Driving me CRAZY~ please help | New Users to Excel | |||
Formula driving me crazy | Excel Discussion (Misc queries) | |||
Driving me crazy! | Excel Programming | |||
It doesn't add up - It's driving me crazy | Excel Programming | |||
Driving me crazy! | Excel Programming |