Help with Workbook_Open macro please

Geoff C · 01-12-2005, 11:06 AM

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!

Bernie Deitrick · 01-12-2005, 12:06 PM

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" <[email protected]> wrote in message
news:[email protected]...
> 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!

Geoff C · 01-12-2005, 01:06 PM

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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!
>
>
>

gocush · 01-12-2005, 01:06 PM

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.

"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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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!
> >
> >
> >

Geoff C · 01-12-2005, 01:06 PM

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 failure: 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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!
> > >
> > >
> > >

Bernie Deitrick · 01-12-2005, 02:06 PM

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" <[email protected]> wrote in message news:[email protected]...
> 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 failure: 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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!
> > > >
> > > >
> > > >

gocush · 01-12-2005, 07:06 PM

On your "Audit" sheet are the cells in A2:D2 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.

On the other hand if these cells are NOT locked what is the point?

"Geoff C" wrote:

> 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 failure: 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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!
> > > >
> > > >
> > > >

Geoff C · 01-13-2005, 07:06 AM

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.

"Bernie Deitrick" wrote:

> 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" <[email protected]> wrote in message news:[email protected]...
> > 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 failure: 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" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > 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!
> > > > >
> > > > >
> > > > >