G
Guest
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.
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.