Help with Workbook_Open macro please

[Guest]

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

 

[Bernie Deitrick]

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

 

[Guest]

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.

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

 

[Guest]

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 failure: trying to change a protected sheet.

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.

 

[Guest]

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!

 

[Bernie Deitrick]

Geoff,

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

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

HTH,
Bernie
MS Excel MVP

 

[Guest]

On your "Audit" sheet are the cells in A22 LOCKED?
Or are you locking them with some other macro, say in your Close_event?

If so, the next time you try to Open this file it will crash since your
Workbook_Open event tries to change these cells.

 

[Guest]

Thankyou! I tracked the problem down to some sort of interference with a UDF.
I have no idea what CodeCleaner does, but after 3 tries it turned out a
version that works. Saved and cemented to the ground.