Workbook_Open macro - what gives?

Hi there, I was here asking questions yesterday about my workbook_open macro
(correctly placed in ThisWorkbook). The code is below, and I'm stumped. Some
parts work and some don't - and previously the parts that don't work did,
while the parts that do didn't.

Is there something about this special macro that I'm just not getting? The
book contains two worksheets, "Audit" and "Profiles". The code is supposed to
activate the Audit worksheet, record the user, the time and date that a new
workbook is created from a template, enter a random number that determines
whether the workbook is QC'd later on, protect the Audit worksheet with a
random password, hide it, and then return to the Profiles worksheet. If the
sheet is not new (i.e. contains the random number), it should bypass all that.

I changed it a bit yesterday following advice from the group, and now the
lines I've folowed with a * don't work anymore, but in older versions have
functioned perfectly well. I'm working in Excel 2000.

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

Sheets("Audit").Select
*used to work (as far as I know)

' If Sheets("Audit").Range("E2").Value = "New" Then
*Commented out yesterday - but putting it back doesn't help!

If Len(Sheets("Audit").Range("D2").Value) = 0 Then
Sheets("Audit").Range("A2").Value = Application.UserName
Sheets("Audit").Range("B2").Value = Date
Sheets("Audit").Range("C2").Value = Time
Randomize
Sheets("Audit").Range("D2").Value = Rnd()
*
'ActiveSheet.Protect Password:="A" & Int(Rnd() * 10000000000#)
'ActiveSheet.Visible = False
Sheets("Audit").Protect Password:="A" & Int(Rnd() * 10000000000#)
Sheets("Audit").Visible = False
*neither of these code variants work, the code previous contained the first
two lines, and worked fine
End If

Sheets("Profiles").Select

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

Feeling dim, and grateful for some advice,
Geoff.

Is the sheet hidden when the workbook opens. You can not select a sheet that
is not visible. Add this to the beginning

sheets("Audit").visible = xlvisible

HTH

"Geoff C" wrote:

> Hi there, I was here asking questions yesterday about my workbook_open macro
> (correctly placed in ThisWorkbook). The code is below, and I'm stumped. Some
> parts work and some don't - and previously the parts that don't work did,
> while the parts that do didn't.
>
> Is there something about this special macro that I'm just not getting? The
> book contains two worksheets, "Audit" and "Profiles". The code is supposed to
> activate the Audit worksheet, record the user, the time and date that a new
> workbook is created from a template, enter a random number that determines
> whether the workbook is QC'd later on, protect the Audit worksheet with a
> random password, hide it, and then return to the Profiles worksheet. If the
> sheet is not new (i.e. contains the random number), it should bypass all that.
>
> I changed it a bit yesterday following advice from the group, and now the
> lines I've folowed with a * don't work anymore, but in older versions have
> functioned perfectly well. I'm working in Excel 2000.
>
> -------------
> Private Sub Workbook_Open()
>
> Sheets("Audit").Select
> *used to work (as far as I know)
>
> ' If Sheets("Audit").Range("E2").Value = "New" Then
> *Commented out yesterday - but putting it back doesn't help!
>
> If Len(Sheets("Audit").Range("D2").Value) = 0 Then
> Sheets("Audit").Range("A2").Value = Application.UserName
> Sheets("Audit").Range("B2").Value = Date
> Sheets("Audit").Range("C2").Value = Time
> Randomize
> Sheets("Audit").Range("D2").Value = Rnd()
> *
> 'ActiveSheet.Protect Password:="A" & Int(Rnd() * 10000000000#)
> 'ActiveSheet.Visible = False
> Sheets("Audit").Protect Password:="A" & Int(Rnd() * 10000000000#)
> Sheets("Audit").Visible = False
> *neither of these code variants work, the code previous contained the first
> two lines, and worked fine
> End If
>
> Sheets("Profiles").Select
>
> End Sub
> ------------------------
>
> Feeling dim, and grateful for some advice,
> Geoff.

Thanks for the suggestion, but the sheet is visible. I tried it anyway -
after I put the line in, none of the code worked. Took it out, and the value
settings worked again, but not the sheet selection, protection or hiding.


"Jim Thomlinson" wrote:

> Is the sheet hidden when the workbook opens. You can not select a sheet that
> is not visible. Add this to the beginning
>
> sheets("Audit").visible = xlvisible
>
> HTH
>
> "Geoff C" wrote:
>
> > Hi there, I was here asking questions yesterday about my workbook_open macro
> > (correctly placed in ThisWorkbook). The code is below, and I'm stumped. Some
> > parts work and some don't - and previously the parts that don't work did,
> > while the parts that do didn't.
> >
> > Is there something about this special macro that I'm just not getting? The
> > book contains two worksheets, "Audit" and "Profiles". The code is supposed to
> > activate the Audit worksheet, record the user, the time and date that a new
> > workbook is created from a template, enter a random number that determines
> > whether the workbook is QC'd later on, protect the Audit worksheet with a
> > random password, hide it, and then return to the Profiles worksheet. If the
> > sheet is not new (i.e. contains the random number), it should bypass all that.
> >
> > I changed it a bit yesterday following advice from the group, and now the
> > lines I've folowed with a * don't work anymore, but in older versions have
> > functioned perfectly well. I'm working in Excel 2000.
> >
> > -------------
> > Private Sub Workbook_Open()
> >
> > Sheets("Audit").Select
> > *used to work (as far as I know)
> >
> > ' If Sheets("Audit").Range("E2").Value = "New" Then
> > *Commented out yesterday - but putting it back doesn't help!
> >
> > If Len(Sheets("Audit").Range("D2").Value) = 0 Then
> > Sheets("Audit").Range("A2").Value = Application.UserName
> > Sheets("Audit").Range("B2").Value = Date
> > Sheets("Audit").Range("C2").Value = Time
> > Randomize
> > Sheets("Audit").Range("D2").Value = Rnd()
> > *
> > 'ActiveSheet.Protect Password:="A" & Int(Rnd() * 10000000000#)
> > 'ActiveSheet.Visible = False
> > Sheets("Audit").Protect Password:="A" & Int(Rnd() * 10000000000#)
> > Sheets("Audit").Visible = False
> > *neither of these code variants work, the code previous contained the first
> > two lines, and worked fine
> > End If
> >
> > Sheets("Profiles").Select
> >
> > End Sub
> > ------------------------
> >
> > Feeling dim, and grateful for some advice,
> > Geoff.

From looking at your code I notice that you are protecting your sheet using a
random number. How are you unprotecting it? I also don't see where you
unprotect it. This is going to cause you a problem (as a guess).

"Geoff C" wrote:

> Thanks for the suggestion, but the sheet is visible. I tried it anyway -
> after I put the line in, none of the code worked. Took it out, and the value
> settings worked again, but not the sheet selection, protection or hiding.
>
>
> "Jim Thomlinson" wrote:
>
> > Is the sheet hidden when the workbook opens. You can not select a sheet that
> > is not visible. Add this to the beginning
> >
> > sheets("Audit").visible = xlvisible
> >
> > HTH
> >
> > "Geoff C" wrote:
> >
> > > Hi there, I was here asking questions yesterday about my workbook_open macro
> > > (correctly placed in ThisWorkbook). The code is below, and I'm stumped. Some
> > > parts work and some don't - and previously the parts that don't work did,
> > > while the parts that do didn't.
> > >
> > > Is there something about this special macro that I'm just not getting? The
> > > book contains two worksheets, "Audit" and "Profiles". The code is supposed to
> > > activate the Audit worksheet, record the user, the time and date that a new
> > > workbook is created from a template, enter a random number that determines
> > > whether the workbook is QC'd later on, protect the Audit worksheet with a
> > > random password, hide it, and then return to the Profiles worksheet. If the
> > > sheet is not new (i.e. contains the random number), it should bypass all that.
> > >
> > > I changed it a bit yesterday following advice from the group, and now the
> > > lines I've folowed with a * don't work anymore, but in older versions have
> > > functioned perfectly well. I'm working in Excel 2000.
> > >
> > > -------------
> > > Private Sub Workbook_Open()
> > >
> > > Sheets("Audit").Select
> > > *used to work (as far as I know)
> > >
> > > ' If Sheets("Audit").Range("E2").Value = "New" Then
> > > *Commented out yesterday - but putting it back doesn't help!
> > >
> > > If Len(Sheets("Audit").Range("D2").Value) = 0 Then
> > > Sheets("Audit").Range("A2").Value = Application.UserName
> > > Sheets("Audit").Range("B2").Value = Date
> > > Sheets("Audit").Range("C2").Value = Time
> > > Randomize
> > > Sheets("Audit").Range("D2").Value = Rnd()
> > > *
> > > 'ActiveSheet.Protect Password:="A" & Int(Rnd() * 10000000000#)
> > > 'ActiveSheet.Visible = False
> > > Sheets("Audit").Protect Password:="A" & Int(Rnd() * 10000000000#)
> > > Sheets("Audit").Visible = False
> > > *neither of these code variants work, the code previous contained the first
> > > two lines, and worked fine
> > > End If
> > >
> > > Sheets("Profiles").Select
> > >
> > > End Sub
> > > ------------------------
> > >
> > > Feeling dim, and grateful for some advice,
> > > Geoff.

Try this code. It never selects the Audit sheet. It just manipulates it as
necessary...

Private Sub Workbook_Open()
Dim wksAudit As Worksheet

Set wksAudit = Sheets("Audit")

' If Sheets("Audit").Range("E2").Value = "New" Then

If Len(wksAudit.Range("D2").Value) = 0 Then
With wksAudit
.Range("A2").Value = Application.UserName
.Range("B2").Value = Date
.Range("C2").Value = Time
Randomize
.Range("D2").Value = Rnd()
End With

wksAudit.Protect Password:="A" & Int(Rnd() * 10000000000#)
wksAudit.Visible = xlHidden
End If

Sheets("Profiles").Select

End Sub

I created an audit sheet object, which is not strictly necessary but it does
make the coding a little faster and easier.

HTH


"Geoff C" wrote:

> Thanks for the suggestion, but the sheet is visible. I tried it anyway -
> after I put the line in, none of the code worked. Took it out, and the value
> settings worked again, but not the sheet selection, protection or hiding.
>
>
> "Jim Thomlinson" wrote:
>
> > Is the sheet hidden when the workbook opens. You can not select a sheet that
> > is not visible. Add this to the beginning
> >
> > sheets("Audit").visible = xlvisible
> >
> > HTH
> >
> > "Geoff C" wrote:
> >
> > > Hi there, I was here asking questions yesterday about my workbook_open macro
> > > (correctly placed in ThisWorkbook). The code is below, and I'm stumped. Some
> > > parts work and some don't - and previously the parts that don't work did,
> > > while the parts that do didn't.
> > >
> > > Is there something about this special macro that I'm just not getting? The
> > > book contains two worksheets, "Audit" and "Profiles". The code is supposed to
> > > activate the Audit worksheet, record the user, the time and date that a new
> > > workbook is created from a template, enter a random number that determines
> > > whether the workbook is QC'd later on, protect the Audit worksheet with a
> > > random password, hide it, and then return to the Profiles worksheet. If the
> > > sheet is not new (i.e. contains the random number), it should bypass all that.
> > >
> > > I changed it a bit yesterday following advice from the group, and now the
> > > lines I've folowed with a * don't work anymore, but in older versions have
> > > functioned perfectly well. I'm working in Excel 2000.
> > >
> > > -------------
> > > Private Sub Workbook_Open()
> > >
> > > Sheets("Audit").Select
> > > *used to work (as far as I know)
> > >
> > > ' If Sheets("Audit").Range("E2").Value = "New" Then
> > > *Commented out yesterday - but putting it back doesn't help!
> > >
> > > If Len(Sheets("Audit").Range("D2").Value) = 0 Then
> > > Sheets("Audit").Range("A2").Value = Application.UserName
> > > Sheets("Audit").Range("B2").Value = Date
> > > Sheets("Audit").Range("C2").Value = Time
> > > Randomize
> > > Sheets("Audit").Range("D2").Value = Rnd()
> > > *
> > > 'ActiveSheet.Protect Password:="A" & Int(Rnd() * 10000000000#)
> > > 'ActiveSheet.Visible = False
> > > Sheets("Audit").Protect Password:="A" & Int(Rnd() * 10000000000#)
> > > Sheets("Audit").Visible = False
> > > *neither of these code variants work, the code previous contained the first
> > > two lines, and worked fine
> > > End If
> > >
> > > Sheets("Profiles").Select
> > >
> > > End Sub
> > > ------------------------
> > >
> > > Feeling dim, and grateful for some advice,
> > > Geoff.

Thanks for trying Jim, but it was no different. I even tried moving the macro
into a module and calling it from ThisWorkbook, with exactly the same
results. But the same macro then works perfectly when called directly after
opening the workbook.

Clearly it's not the code itself that is failing. I'm going to have to chalk
it up to "Corrupt file", and try to redraft from scratch.

It drives me spare that something so useful can be so flaky, but there you
go... Thanks for the tip on layout, much neater.

"Jim Thomlinson" wrote:

> Try this code. It never selects the Audit sheet. It just manipulates it as
> necessary...
>
> Private Sub Workbook_Open()
> Dim wksAudit As Worksheet
>
> Set wksAudit = Sheets("Audit")
>
> ' If Sheets("Audit").Range("E2").Value = "New" Then
>
> If Len(wksAudit.Range("D2").Value) = 0 Then
> With wksAudit
> .Range("A2").Value = Application.UserName
> .Range("B2").Value = Date
> .Range("C2").Value = Time
> Randomize
> .Range("D2").Value = Rnd()
> End With
>
> wksAudit.Protect Password:="A" & Int(Rnd() * 10000000000#)
> wksAudit.Visible = xlHidden
> End If
>
> Sheets("Profiles").Select
>
> End Sub
>
> I created an audit sheet object, which is not strictly necessary but it does
> make the coding a little faster and easier.
>
> HTH
>
>
> "Geoff C" wrote:
>
> > Thanks for the suggestion, but the sheet is visible. I tried it anyway -
> > after I put the line in, none of the code worked. Took it out, and the value
> > settings worked again, but not the sheet selection, protection or hiding.
> >
> >
> > "Jim Thomlinson" wrote:
> >
> > > Is the sheet hidden when the workbook opens. You can not select a sheet that
> > > is not visible. Add this to the beginning
> > >
> > > sheets("Audit").visible = xlvisible
> > >
> > > HTH
> > >
> > > "Geoff C" wrote:
> > >
> > > > Hi there, I was here asking questions yesterday about my workbook_open macro
> > > > (correctly placed in ThisWorkbook). The code is below, and I'm stumped. Some
> > > > parts work and some don't - and previously the parts that don't work did,
> > > > while the parts that do didn't.
> > > >
> > > > Is there something about this special macro that I'm just not getting? The
> > > > book contains two worksheets, "Audit" and "Profiles". The code is supposed to
> > > > activate the Audit worksheet, record the user, the time and date that a new
> > > > workbook is created from a template, enter a random number that determines
> > > > whether the workbook is QC'd later on, protect the Audit worksheet with a
> > > > random password, hide it, and then return to the Profiles worksheet. If the
> > > > sheet is not new (i.e. contains the random number), it should bypass all that.
> > > >
> > > > I changed it a bit yesterday following advice from the group, and now the
> > > > lines I've folowed with a * don't work anymore, but in older versions have
> > > > functioned perfectly well. I'm working in Excel 2000.
> > > >
> > > > -------------
> > > > Private Sub Workbook_Open()
> > > >
> > > > Sheets("Audit").Select
> > > > *used to work (as far as I know)
> > > >
> > > > ' If Sheets("Audit").Range("E2").Value = "New" Then
> > > > *Commented out yesterday - but putting it back doesn't help!
> > > >
> > > > If Len(Sheets("Audit").Range("D2").Value) = 0 Then
> > > > Sheets("Audit").Range("A2").Value = Application.UserName
> > > > Sheets("Audit").Range("B2").Value = Date
> > > > Sheets("Audit").Range("C2").Value = Time
> > > > Randomize
> > > > Sheets("Audit").Range("D2").Value = Rnd()
> > > > *
> > > > 'ActiveSheet.Protect Password:="A" & Int(Rnd() * 10000000000#)
> > > > 'ActiveSheet.Visible = False
> > > > Sheets("Audit").Protect Password:="A" & Int(Rnd() * 10000000000#)
> > > > Sheets("Audit").Visible = False
> > > > *neither of these code variants work, the code previous contained the first
> > > > two lines, and worked fine
> > > > End If
> > > >
> > > > Sheets("Profiles").Select
> > > >
> > > > End Sub
> > > > ------------------------
> > > >
> > > > Feeling dim, and grateful for some advice,
> > > > Geoff.