Hi;

I wasn't sure which was more appropriate -- continue with the older thread
or start a new one? So, I am starting a new one. If this is contrary to
netequite et me know.

My friend has Excel version 9.0 . I
have version 10. The correct version numbers on both machines are being
returned. (In the VBE Immediate window and through MsgBox.). The only way
that I can test this problem is directly on his machine.
I have the following in my startup code:

If Val(Application.Version) >= 10 Then
MembSheet.Protect _
Password:="bill", _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True, _
UserInterfaceOnly:=True, _
AllowFormattingCells:=True, _
AllowFormattingColumns:=False, _
AllowFormattingRows:=False, _
AllowInsertingColumns:=False, _
AllowInsertingRows:=True, _
AllowInsertingHyperlinks:=True, _
AllowDeletingColumns:=False, _
AllowDeletingRows:=True, _
AllowSorting:=True, _
AllowFiltering:=True, _
AllowUsingPivotTables:=True
Else
MembSheet.Protect _
Password:="bill", _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True, _
UserInterfaceOnly:=True
End If

It works perfectly on my version 10 machine. If I use 11 as a test for
example the implementation jumps to the 'Else' condition as it should.

His keeps giving an error at compile time; the debugger shows that his
machine is not making the jump over the 'Then' statement to the 'Else' and
his error always comes about when his compiler tries to read the version 10
Protect block at the ' AllowFormattingCells:=True, _' line. I didn't
write down his exact error message but it was something like "argument not
found".

I have tried (in my amateur way) to use compiler directives and that hasn't
worked.
I have both programs working by taking the additional 'Protect' code out of
both versions, but other people I will be sharing with will have version 10
and the additional 'Protect' code is a nice feature for what I want to do.

Any and all suggestions gratefully received.

Regards Bill

Bill,
Put the protection code in separate Subs and put each Sub in a new module.
The new module(s) aren't compiled until their subs are called...

If Val(Application.Version) >= 10 Then
Call OtherSubFor10_version
Else
Call OtherSubFor9_version
End if
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Bill Case" <(E-Mail Removed)>
wrote in message
Hi;
I wasn't sure which was more appropriate -- continue with the older thread
or start a new one? So, I am starting a new one. If this is contrary to
netequite et me know.

My friend has Excel version 9.0 . I
have version 10. The correct version numbers on both machines are being
returned. (In the VBE Immediate window and through MsgBox.). The only way
that I can test this problem is directly on his machine.
I have the following in my startup code:

If Val(Application.Version) >= 10 Then
MembSheet.Protect _
Password:="bill", _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True, _
UserInterfaceOnly:=True, _
AllowFormattingCells:=True, _
AllowFormattingColumns:=False, _
AllowFormattingRows:=False, _
AllowInsertingColumns:=False, _
AllowInsertingRows:=True, _
AllowInsertingHyperlinks:=True, _
AllowDeletingColumns:=False, _
AllowDeletingRows:=True, _
AllowSorting:=True, _
AllowFiltering:=True, _
AllowUsingPivotTables:=True
Else
MembSheet.Protect _
Password:="bill", _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True, _
UserInterfaceOnly:=True
End If

It works perfectly on my version 10 machine. If I use 11 as a test for
example the implementation jumps to the 'Else' condition as it should.

His keeps giving an error at compile time; the debugger shows that his
machine is not making the jump over the 'Then' statement to the 'Else' and
his error always comes about when his compiler tries to read the version 10
Protect block at the ' AllowFormattingCells:=True, _' line. I didn't
write down his exact error message but it was something like "argument not
found".

I have tried (in my amateur way) to use compiler directives and that hasn't
worked.
I have both programs working by taking the additional 'Protect' code out of
both versions, but other people I will be sharing with will have version 10
and the additional 'Protect' code is a nice feature for what I want to do.
Any and all suggestions gratefully received.
Regards Bill

Also, don't Compile the project manually.

--
Regards,
Tom Ogilvy

"Jim Cone" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Bill,
> Put the protection code in separate Subs and put each Sub in a new module.
> The new module(s) aren't compiled until their subs are called...
>
> If Val(Application.Version) >= 10 Then
> Call OtherSubFor10_version
> Else
> Call OtherSubFor9_version
> End if
> --
> Jim Cone
> San Francisco, USA
> http://www.realezsites.com/bus/primitivesoftware
>
>
> "Bill Case" <(E-Mail Removed)>
> wrote in message
> Hi;
> I wasn't sure which was more appropriate -- continue with the older thread
> or start a new one? So, I am starting a new one. If this is contrary to
> netequite et me know.
>
> My friend has Excel version 9.0 . I
> have version 10. The correct version numbers on both machines are being
> returned. (In the VBE Immediate window and through MsgBox.). The only
> way
> that I can test this problem is directly on his machine.
> I have the following in my startup code:
>
> If Val(Application.Version) >= 10 Then
> MembSheet.Protect _
> Password:="bill", _
> DrawingObjects:=True, _
> Contents:=True, _
> Scenarios:=True, _
> UserInterfaceOnly:=True, _
> AllowFormattingCells:=True, _
> AllowFormattingColumns:=False, _
> AllowFormattingRows:=False, _
> AllowInsertingColumns:=False, _
> AllowInsertingRows:=True, _
> AllowInsertingHyperlinks:=True, _
> AllowDeletingColumns:=False, _
> AllowDeletingRows:=True, _
> AllowSorting:=True, _
> AllowFiltering:=True, _
> AllowUsingPivotTables:=True
> Else
> MembSheet.Protect _
> Password:="bill", _
> DrawingObjects:=True, _
> Contents:=True, _
> Scenarios:=True, _
> UserInterfaceOnly:=True
> End If
>
> It works perfectly on my version 10 machine. If I use 11 as a test for
> example the implementation jumps to the 'Else' condition as it should.
>
> His keeps giving an error at compile time; the debugger shows that his
> machine is not making the jump over the 'Then' statement to the 'Else' and
> his error always comes about when his compiler tries to read the version
> 10
> Protect block at the ' AllowFormattingCells:=True, _' line. I
> didn't
> write down his exact error message but it was something like "argument not
> found".
>
> I have tried (in my amateur way) to use compiler directives and that
> hasn't
> worked.
> I have both programs working by taking the additional 'Protect' code out
> of
> both versions, but other people I will be sharing with will have version
> 10
> and the additional 'Protect' code is a nice feature for what I want to do.
> Any and all suggestions gratefully received.
> Regards Bill
>

I think as well as not compiling, if 'MembSheet' is declared as Worksheet
would need to split that sheet protection code into two separate functions
and call the appropriate one depending on the version. Though no problem if
'MembSheet' is passed as Object. Also sometimes worth putting code designed
exclusively for later versions in the last inserted module.

Regards,
Peter T

"Tom Ogilvy" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Also, don't Compile the project manually.
>
> --
> Regards,
> Tom Ogilvy
>
> "Jim Cone" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Bill,
> > Put the protection code in separate Subs and put each Sub in a new
module.
> > The new module(s) aren't compiled until their subs are called...
> >
> > If Val(Application.Version) >= 10 Then
> > Call OtherSubFor10_version
> > Else
> > Call OtherSubFor9_version
> > End if
> > --
> > Jim Cone
> > San Francisco, USA
> > http://www.realezsites.com/bus/primitivesoftware
> >
> >
> > "Bill Case" <(E-Mail Removed)>
> > wrote in message
> > Hi;
> > I wasn't sure which was more appropriate -- continue with the older
thread
> > or start a new one? So, I am starting a new one. If this is contrary
to
> > netequite et me know.
> >
> > My friend has Excel version 9.0 . I
> > have version 10. The correct version numbers on both machines are being
> > returned. (In the VBE Immediate window and through MsgBox.). The only
> > way
> > that I can test this problem is directly on his machine.
> > I have the following in my startup code:
> >
> > If Val(Application.Version) >= 10 Then
> > MembSheet.Protect _
> > Password:="bill", _
> > DrawingObjects:=True, _
> > Contents:=True, _
> > Scenarios:=True, _
> > UserInterfaceOnly:=True, _
> > AllowFormattingCells:=True, _
> > AllowFormattingColumns:=False, _
> > AllowFormattingRows:=False, _
> > AllowInsertingColumns:=False, _
> > AllowInsertingRows:=True, _
> > AllowInsertingHyperlinks:=True, _
> > AllowDeletingColumns:=False, _
> > AllowDeletingRows:=True, _
> > AllowSorting:=True, _
> > AllowFiltering:=True, _
> > AllowUsingPivotTables:=True
> > Else
> > MembSheet.Protect _
> > Password:="bill", _
> > DrawingObjects:=True, _
> > Contents:=True, _
> > Scenarios:=True, _
> > UserInterfaceOnly:=True
> > End If
> >
> > It works perfectly on my version 10 machine. If I use 11 as a test for
> > example the implementation jumps to the 'Else' condition as it should.
> >
> > His keeps giving an error at compile time; the debugger shows that his
> > machine is not making the jump over the 'Then' statement to the 'Else'
and
> > his error always comes about when his compiler tries to read the version
> > 10
> > Protect block at the ' AllowFormattingCells:=True, _' line. I
> > didn't
> > write down his exact error message but it was something like "argument
not
> > found".
> >
> > I have tried (in my amateur way) to use compiler directives and that
> > hasn't
> > worked.
> > I have both programs working by taking the additional 'Protect' code out
> > of
> > both versions, but other people I will be sharing with will have version
> > 10
> > and the additional 'Protect' code is a nice feature for what I want to
do.
> > Any and all suggestions gratefully received.
> > Regards Bill
> >
>
>

Now why on earth didn't I read Jim's post properly!
Sorry for the duplication !!

Peter T

"Peter T" <peter_t@discussions> wrote in message
news:#(E-Mail Removed)...
> I think as well as not compiling, if 'MembSheet' is declared as Worksheet
> would need to split that sheet protection code into two separate functions
> and call the appropriate one depending on the version. Though no problem
if
> 'MembSheet' is passed as Object. Also sometimes worth putting code
designed
> exclusively for later versions in the last inserted module.
>
> Regards,
> Peter T
>
> "Tom Ogilvy" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Also, don't Compile the project manually.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Jim Cone" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > Bill,
> > > Put the protection code in separate Subs and put each Sub in a new
> module.
> > > The new module(s) aren't compiled until their subs are called...
> > >
> > > If Val(Application.Version) >= 10 Then
> > > Call OtherSubFor10_version
> > > Else
> > > Call OtherSubFor9_version
> > > End if
> > > --
> > > Jim Cone
> > > San Francisco, USA
> > > http://www.realezsites.com/bus/primitivesoftware
> > >
> > >
> > > "Bill Case" <(E-Mail Removed)>
> > > wrote in message
> > > Hi;
> > > I wasn't sure which was more appropriate -- continue with the older
> thread
> > > or start a new one? So, I am starting a new one. If this is contrary
> to
> > > netequite et me know.
> > >
> > > My friend has Excel version 9.0 . I
> > > have version 10. The correct version numbers on both machines are
being
> > > returned. (In the VBE Immediate window and through MsgBox.). The
only
> > > way
> > > that I can test this problem is directly on his machine.
> > > I have the following in my startup code:
> > >
> > > If Val(Application.Version) >= 10 Then
> > > MembSheet.Protect _
> > > Password:="bill", _
> > > DrawingObjects:=True, _
> > > Contents:=True, _
> > > Scenarios:=True, _
> > > UserInterfaceOnly:=True, _
> > > AllowFormattingCells:=True, _
> > > AllowFormattingColumns:=False, _
> > > AllowFormattingRows:=False, _
> > > AllowInsertingColumns:=False, _
> > > AllowInsertingRows:=True, _
> > > AllowInsertingHyperlinks:=True, _
> > > AllowDeletingColumns:=False, _
> > > AllowDeletingRows:=True, _
> > > AllowSorting:=True, _
> > > AllowFiltering:=True, _
> > > AllowUsingPivotTables:=True
> > > Else
> > > MembSheet.Protect _
> > > Password:="bill", _
> > > DrawingObjects:=True, _
> > > Contents:=True, _
> > > Scenarios:=True, _
> > > UserInterfaceOnly:=True
> > > End If
> > >
> > > It works perfectly on my version 10 machine. If I use 11 as a test
for
> > > example the implementation jumps to the 'Else' condition as it should.
> > >
> > > His keeps giving an error at compile time; the debugger shows that his
> > > machine is not making the jump over the 'Then' statement to the 'Else'
> and
> > > his error always comes about when his compiler tries to read the
version
> > > 10
> > > Protect block at the ' AllowFormattingCells:=True, _' line. I
> > > didn't
> > > write down his exact error message but it was something like "argument
> not
> > > found".
> > >
> > > I have tried (in my amateur way) to use compiler directives and that
> > > hasn't
> > > worked.
> > > I have both programs working by taking the additional 'Protect' code
out
> > > of
> > > both versions, but other people I will be sharing with will have
version
> > > 10
> > > and the additional 'Protect' code is a nice feature for what I want to
> do.
> > > Any and all suggestions gratefully received.
> > > Regards Bill
> > >
> >
> >
>
>

On Sun, 8 Oct 2006 11:23:46 -0700, Jim Cone <(E-Mail Removed)> wrote:

First I would simplify it to (which seem to be the only ones that are not
the default):

If Val(Application.Version) >= 10 Then
MembSheet.Protect _
Password:="bill", _
UserInterfaceOnly:=True, _
AllowUsingPivotTables:=True
Else
MembSheet.Protect _
Password:="bill", _
UserInterfaceOnly:=True
End If

Then what will work is using this construct:

If Val(Application.Version) >= 10 Then
Sheets("sheetname").Protect _
Password:="bill", _
UserInterfaceOnly:=True, _
AllowUsingPivotTables:=True
Else
Sheets("sheetname").Protect _
Password:="bill", _
UserInterfaceOnly:=True
End If

Don <www.donwiss.com> (e-mail link at home page bottom).

Thanks Jim et al;

Worked like a charm -- on the first go.

I would consider it an important lesson learned about how VBA compiles.

For Don Wiss; I realize I can take all the agruments out. I left them in
for the time being while I experimented with the best possible configuration.

Regards Bill

"Jim Cone" wrote:

> Bill,
> Put the protection code in separate Subs and put each Sub in a new module.
> The new module(s) aren't compiled until their subs are called...
>
> If Val(Application.Version) >= 10 Then
> Call OtherSubFor10_version
> Else
> Call OtherSubFor9_version
> End if
> --
> Jim Cone
> San Francisco, USA
> http://www.realezsites.com/bus/primitivesoftware
>
>
> "Bill Case" <(E-Mail Removed)>
> wrote in message
> Hi;
> I wasn't sure which was more appropriate -- continue with the older thread
> or start a new one? So, I am starting a new one. If this is contrary to
> netequite et me know.
>
> My friend has Excel version 9.0 . I
> have version 10. The correct version numbers on both machines are being
> returned. (In the VBE Immediate window and through MsgBox.). The only way
> that I can test this problem is directly on his machine.
> I have the following in my startup code:
>
> If Val(Application.Version) >= 10 Then
> MembSheet.Protect _
> Password:="bill", _
> DrawingObjects:=True, _
> Contents:=True, _
> Scenarios:=True, _
> UserInterfaceOnly:=True, _
> AllowFormattingCells:=True, _
> AllowFormattingColumns:=False, _
> AllowFormattingRows:=False, _
> AllowInsertingColumns:=False, _
> AllowInsertingRows:=True, _
> AllowInsertingHyperlinks:=True, _
> AllowDeletingColumns:=False, _
> AllowDeletingRows:=True, _
> AllowSorting:=True, _
> AllowFiltering:=True, _
> AllowUsingPivotTables:=True
> Else
> MembSheet.Protect _
> Password:="bill", _
> DrawingObjects:=True, _
> Contents:=True, _
> Scenarios:=True, _
> UserInterfaceOnly:=True
> End If
>
> It works perfectly on my version 10 machine. If I use 11 as a test for
> example the implementation jumps to the 'Else' condition as it should.
>
> His keeps giving an error at compile time; the debugger shows that his
> machine is not making the jump over the 'Then' statement to the 'Else' and
> his error always comes about when his compiler tries to read the version 10
> Protect block at the ' AllowFormattingCells:=True, _' line. I didn't
> write down his exact error message but it was something like "argument not
> found".
>
> I have tried (in my amateur way) to use compiler directives and that hasn't
> worked.
> I have both programs working by taking the additional 'Protect' code out of
> both versions, but other people I will be sharing with will have version 10
> and the additional 'Protect' code is a nice feature for what I want to do.
> Any and all suggestions gratefully received.
> Regards Bill
>
>