Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Continuing Val(Application.Version) saga ?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Continuing Val(Application.Version) saga ?
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" 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Continuing Val(Application.Version) saga ?
Also, don't Compile the project manually.
-- Regards, Tom Ogilvy "Jim Cone" wrote in message ... 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" 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Continuing Val(Application.Version) saga ?
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" wrote in message ... Also, don't Compile the project manually. -- Regards, Tom Ogilvy "Jim Cone" wrote in message ... 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" 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Continuing Val(Application.Version) saga ?
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 ... 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" wrote in message ... Also, don't Compile the project manually. -- Regards, Tom Ogilvy "Jim Cone" wrote in message ... 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" 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Continuing Val(Application.Version) saga ?
On Sun, 8 Oct 2006 11:23:46 -0700, Jim Cone 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). |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Continuing Val(Application.Version) saga ?
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" 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Office application version does not match. | Excel Discussion (Misc queries) | |||
Application.VBE.Version on Excel XP | Excel Programming | |||
Conditional Compilation using Application.Version | Excel Programming | |||
Conditional Compilation using Application.Version | Excel Programming | |||
Help in using Application.Version | Excel Programming |