Is there a way to use a VBA feature in Excel 2000 when running in Excel
2000, but have the system not us it when running in Excel 97?

I've created a modeless dialog box for my application. I want it to run in
Excel 2000 (and it does) but when I try to run the app in Excel 97 I get a
compile error because Excel 97 does not accept the vbModeless parameter
after the Form.Show command.

I've tried containing it in a If statment, but that doesn't solve the
problem.

Is there anything equivalent to the precompiler statements in C that I can
use here?

Thanks.

ExcelMan,

Have you tried

If Application.Version 8 Then
UserForm1.Show vbModeless
Else
UserForm1.Show
End If


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"ExcelMan" wrote in message
...
Is there a way to use a VBA feature in Excel 2000 when running in Excel
2000, but have the system not us it when running in Excel 97?

I've created a modeless dialog box for my application. I want it to run
in
Excel 2000 (and it does) but when I try to run the app in Excel 97 I get a
compile error because Excel 97 does not accept the vbModeless parameter
after the Form.Show command.

I've tried containing it in a If statment, but that doesn't solve the
problem.

Is there anything equivalent to the precompiler statements in C that I can
use here?

Thanks.

Hi Bob

It errs on 97 because vbModeless is unknown. The trick is to place that into an isolated
sub that won't be called in 97:

Sub Main()
Select Case Val(Application.Version)
Case 8
UserForm1.Show
Case 9 To 99
Call Modeles
Case Else
End Select
End Sub

Private Sub Modeles()
UserForm1.Show vbModeless
End Sub

--
HTH. Best wishes Harald
Excel MVP

Followup to newsgroup only please.

"Bob Phillips" wrote in message
...
ExcelMan,

Have you tried

If Application.Version 8 Then
UserForm1.Show vbModeless
Else
UserForm1.Show
End If


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"ExcelMan" wrote in message
...
Is there a way to use a VBA feature in Excel 2000 when running in Excel
2000, but have the system not us it when running in Excel 97?

I've created a modeless dialog box for my application. I want it to run
in
Excel 2000 (and it does) but when I try to run the app in Excel 97 I get a
compile error because Excel 97 does not accept the vbModeless parameter
after the Form.Show command.

I've tried containing it in a If statment, but that doesn't solve the
problem.

Is there anything equivalent to the precompiler statements in C that I can
use here?

Thanks.

You are right Bob

I use this also in the Google Add-in


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Bob Phillips" wrote in message ...
ExcelMan,

Can't check this myself as I don't have Excel97 on this laptop, but I think
you can do what you suggest in the later part of the post, by using
conditional compilation. If I am right, Excel 2000 and above use VBA6, Excel
97 doesn't, so you could also try

#If VBA6 Then
Userform1.Show vbModeless
#Else
Userform1.Show
#End If



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Bob Phillips" wrote in message
...
ExcelMan,

Have you tried

If Application.Version 8 Then
UserForm1.Show vbModeless
Else
UserForm1.Show
End If


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"ExcelMan" wrote in message
...
Is there a way to use a VBA feature in Excel 2000 when running in Excel
2000, but have the system not us it when running in Excel 97?

I've created a modeless dialog box for my application. I want it to run
in
Excel 2000 (and it does) but when I try to run the app in Excel 97 I get
a
compile error because Excel 97 does not accept the vbModeless parameter
after the Form.Show command.

I've tried containing it in a If statment, but that doesn't solve the
problem.

Is there anything equivalent to the precompiler statements in C that I
can
use here?

Thanks.

Harald,

Thanks for showing me that. I don't have 97 on this machine to test, and
although what you say is obvious (once someone tells you that is <vbg), I
just strung together logical code. Ain't that always the way, logical code
doesn't always work. I guess that is why we spend so much on testing!

Regards

Bob

"Harald Staff" wrote in message
...
Hi Bob

It errs on 97 because vbModeless is unknown. The trick is to place that
into an isolated
sub that won't be called in 97:

Sub Main()
Select Case Val(Application.Version)
Case 8
UserForm1.Show
Case 9 To 99
Call Modeles
Case Else
End Select
End Sub

Private Sub Modeles()
UserForm1.Show vbModeless
End Sub

--
HTH. Best wishes Harald
Excel MVP

Followup to newsgroup only please.

"Bob Phillips" wrote in message
...
ExcelMan,

Have you tried

If Application.Version 8 Then
UserForm1.Show vbModeless
Else
UserForm1.Show
End If


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"ExcelMan" wrote in message
...
Is there a way to use a VBA feature in Excel 2000 when running in
Excel
2000, but have the system not us it when running in Excel 97?

I've created a modeless dialog box for my application. I want it to
run
in
Excel 2000 (and it does) but when I try to run the app in Excel 97 I
get a
compile error because Excel 97 does not accept the vbModeless
parameter
after the Form.Show command.

I've tried containing it in a If statment, but that doesn't solve the
problem.

Is there anything equivalent to the precompiler statements in C that I
can
use here?

Thanks.

What's a Mac <vbg

Bob

PS Actually I had an Apple IIe back in 1980, then one of the early Macs. I
wanted a Lisa, but could never afford it.


"J.E. McGimpsey" wrote in message
...
And of course, if you want to allow your code to run cross-platform:

Public Sub Main()
If (Application.Version 8) And _
(Left(Application.OperatingSystem, 7) = "Windows") Then
Call Modeless
Else
UserForm1.Show
End If
End Sub

or, to reduce run-time checking:

Public Sub Main()
#If Mac Then
Userform1.Show
#Else
If Application.Version 8 Then
Call Modeless
Else
UserForm1.Show
End If
#End If
End Sub


In article ,
"Harald Staff" wrote:

Hi Bob

It errs on 97 because vbModeless is unknown. The trick is to place that
into
an isolated
sub that won't be called in 97:

Sub Main()
Select Case Val(Application.Version)
Case 8
UserForm1.Show
Case 9 To 99
Call Modeles
Case Else
End Select
End Sub

Private Sub Modeles()
UserForm1.Show vbModeless
End Sub

--
HTH. Best wishes Harald
Excel MVP

Hi Bob

Except for the brilliant conditional compilation, I think isolating it is the only way
around it. This was one of the colorful debates back in the Hawley days, that's why I
remember it so well:
http://makeashorterlink.com/?M2C4167F5

quote: "I have not tested this but if the UserForm ShowModal Property is set to False, it
will simply show as Modal in pre 2000 versions and show as not Modal in 97 versions. So
is there any need to test?"

:-)

HTH. Best wishes Harald
Excel MVP

Followup to newsgroup only please.

"Bob Phillips" wrote in message
...
Harald,

Had a wonderful bright idea after my last response, don't use the vbModeless
constant but it's value instead.

Great idea, yes? But thinking some more I guess it's not such a good idea.
Presumably, as modeless forms were only introduced in 2000, the 97 version
would not even support the arguments to the Show method, so it would still
err on that line if not isolated as you suggest. I don't have 97 to hand, so
can you confirm that?

Regards

Bob

"Harald Staff" wrote in message
...
Hi Bob

It errs on 97 because vbModeless is unknown. The trick is to place that
into an isolated
sub that won't be called in 97:

Sub Main()
Select Case Val(Application.Version)
Case 8
UserForm1.Show
Case 9 To 99
Call Modeles
Case Else
End Select
End Sub

Private Sub Modeles()
UserForm1.Show vbModeless
End Sub

--
HTH. Best wishes Harald
Excel MVP

Followup to newsgroup only please.

"Bob Phillips" wrote in message
...
ExcelMan,

Have you tried

If Application.Version 8 Then
UserForm1.Show vbModeless
Else
UserForm1.Show
End If


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"ExcelMan" wrote in message
...
Is there a way to use a VBA feature in Excel 2000 when running in
Excel
2000, but have the system not us it when running in Excel 97?

I've created a modeless dialog box for my application. I want it to
run
in
Excel 2000 (and it does) but when I try to run the app in Excel 97 I
get a
compile error because Excel 97 does not accept the vbModeless
parameter
after the Form.Show command.

I've tried containing it in a If statment, but that doesn't solve the
problem.

Is there anything equivalent to the precompiler statements in C that I
can
use here?

Thanks.

Thanks, Harald, you confirm what I expected, but that makes an interesting
read anyway.

I see what Dave was getting at, but a very loose phrasing certainly confused
a few folks.

By the way, you are Norwegian are you not? If so, I would have expected you
to spell colourful correctly <vbg.

Regards

Bob

"Harald Staff" wrote in message
...
Hi Bob

Except for the brilliant conditional compilation, I think isolating it is
the only way
around it. This was one of the colorful debates back in the Hawley days,
that's why I
remember it so well:
http://makeashorterlink.com/?M2C4167F5

quote: "I have not tested this but if the UserForm ShowModal Property is
set to False, it
will simply show as Modal in pre 2000 versions and show as not Modal in
97 versions. So
is there any need to test?"

:-)

HTH. Best wishes Harald
Excel MVP

Followup to newsgroup only please.

"Bob Phillips" wrote in message
...
Harald,

Had a wonderful bright idea after my last response, don't use the
vbModeless
constant but it's value instead.

Great idea, yes? But thinking some more I guess it's not such a good
idea.
Presumably, as modeless forms were only introduced in 2000, the 97
version
would not even support the arguments to the Show method, so it would
still
err on that line if not isolated as you suggest. I don't have 97 to
hand, so
can you confirm that?

Regards

Bob

"Harald Staff" wrote in message
...
Hi Bob

It errs on 97 because vbModeless is unknown. The trick is to place
that
into an isolated
sub that won't be called in 97:

Sub Main()
Select Case Val(Application.Version)
Case 8
UserForm1.Show
Case 9 To 99
Call Modeles
Case Else
End Select
End Sub

Private Sub Modeles()
UserForm1.Show vbModeless
End Sub

--
HTH. Best wishes Harald
Excel MVP

Followup to newsgroup only please.

"Bob Phillips" wrote in message
...
ExcelMan,

Have you tried

If Application.Version 8 Then
UserForm1.Show vbModeless
Else
UserForm1.Show
End If


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"ExcelMan" wrote in message
...
Is there a way to use a VBA feature in Excel 2000 when running in
Excel
2000, but have the system not us it when running in Excel 97?

I've created a modeless dialog box for my application. I want it
to
run
in
Excel 2000 (and it does) but when I try to run the app in Excel 97
I
get a
compile error because Excel 97 does not accept the vbModeless
parameter
after the Form.Show command.

I've tried containing it in a If statment, but that doesn't solve
the
problem.

Is there anything equivalent to the precompiler statements in C
that I
can
use here?

Thanks.

My Add-in is working correct in Office 2003 Tom.
So It must be VBA6 also

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Tom Ogilvy" wrote in message ...
or (the distinction in VBA6 until 2003 comes out - don't know what it
defines VBA6 as).


Public Sub Main()
#If VBA6 Then
Userform1.Show vbModeless
#Else
UserForm1.Show
#End If
End Sub

perhaps.

--
Regards,
Tom Ogilvy


J.E. McGimpsey wrote in message
...
And of course, if you want to allow your code to run cross-platform:

Public Sub Main()
If (Application.Version 8) And _
(Left(Application.OperatingSystem, 7) = "Windows") Then
Call Modeless
Else
UserForm1.Show
End If
End Sub

or, to reduce run-time checking:

Public Sub Main()
#If Mac Then
Userform1.Show
#Else
If Application.Version 8 Then
Call Modeless
Else
UserForm1.Show
End If
#End If
End Sub


In article ,
"Harald Staff" wrote:

Hi Bob

It errs on 97 because vbModeless is unknown. The trick is to place that
into
an isolated
sub that won't be called in 97:

Sub Main()
Select Case Val(Application.Version)
Case 8
UserForm1.Show
Case 9 To 99
Call Modeles
Case Else
End Select
End Sub

Private Sub Modeles()
UserForm1.Show vbModeless
End Sub

--
HTH. Best wishes Harald
Excel MVP

In article ,
"Bob Phillips" wrote:

What's a Mac <vbg

It's what Excel was written for, years before there was a "Windows".
<g