Geoff,

Try using Rob Bovey's CodeCleaner. It can be a lifesaver.

http://www.appspro.com/downloads/CodeCleaner.exe

HTH,
Bernie
MS Excel MVP

"Geoff C" wrote in message ...
Thanks for the ideas guys, but nothing's working - the macro only protects
the sheet after making changes - it's never caused a problem before. Also,
while trying difference things, it worked once, but when I went back in to
work out why, it stopped working again. I think there must be something
damaged about the file. Trouble is, I really don't want to start again!

"gocush" wrote:

I used the following successfully on the FIRST run of the macro.


Private Sub Workbook_Open()

Dim x As String
On Error GoTo Workbook_Open_Error

If Worksheets("Audit").Range("E2").Value = "New" Then
Worksheets("Audit").Range("A2").Value = Application.UserName
Worksheets("Audit").Range("B2").Value = Date
Worksheets("Audit").Range("C2").Value = Time
Randomize
Worksheets("Audit").Range("D2").Value = Rnd()

'...up to here, but the next two lines don't do anything. They are supposed
'to protect with a random password, and then hide the sheet....
x = "A" & Int(Rnd() * 10000000000#)
Worksheets("Audit").Protect Password:=x
Worksheets("Audit").Visible = False
End If

GoTo Workbook_Open_Complete

Workbook_Open_Error:
MsgBox "Error in Workbook_Open"

Workbook_Open_Complete:

End Sub

HOWEVER, when you think of the logic, all subsequent opening events are
trying to
make changes to the Audit sheet which is now PROTECTED and not likely to be
unprotected, even by the developer, since the password is random. It seems
to me this is the cause of a failu trying to change a protected sheet.

"Geoff C" wrote:

Bernie, thank for this, but having tried it, it made no difference. Is there
anything else that might help? I've succesfully used the random
string-protect technique many times before, but not in a Workbook_Open macro
before. I don't really understand why it seems to be failing on these two
lines.

Thanks, Geoff.

"Bernie Deitrick" wrote:

Geoff,

You need to coerce the random numbe to be a string:

Worksheets("Audit").Protect Password:="A" & CStr(Int(Rnd() * 10000000000#))

HTH,
Bernie
MS Excel MVP

"Geoff C" wrote in message
...
The following macro works partially...

---------------------------------------
Private Sub Workbook_Open()

On Error GoTo Workbook_Open_Error

If Worksheets("Audit").Range("E2").Value = "New" Then
Worksheets("Audit").Range("A2").Value = Application.UserName
Worksheets("Audit").Range("B2").Value = Date
Worksheets("Audit").Range("C2").Value = Time
Randomize
Worksheets("Audit").Range("D2").Value = Rnd()

...up to here, but the next two lines don't do anything. They are supposed
to protect with a random password, and then hide the sheet....

Worksheets("Audit").Protect Password:="A" & Int(Rnd() *
10000000000#)
Worksheets("Audit").Visible = False
End If

GoTo Workbook_Open_Complete

Workbook_Open_Error:
MsgBox "Error in Workbook_Open"

Workbook_Open_Complete:

End Sub
---------------------------------------

Can someone give me some clues? I'm sure this worked yesterday!