vbModal, VBA version, StartUpPosition Setting

[Shoji Kaku]

I've used the FormPositioner module from Chip Pearson to position the
userforms at a particular place on the spreadsheet. It works great with
Excel 2000. But, I found a problem when trying to use the same
application with Excel 97. The VBA version in Excel 97 doesn't seem to
be able to compile the vbModal argument that follows the frmXXXX.Show
statement. Secondly, the userform I had set the StartUpPosition
property to 0 - Manual in Excel 2000, somehow got reset to the default 1
- CenterOwner in Excel 97. I can get around these problems by
commenting out the vbModal and re-setting the StartUpPosition property
on the userform manually to 0 - Manual. However, this seems to mean
that I have to have two versions of this application. Is there a way
for me to handle this programmatically by using a some branch logic?
Related to that question, I have these other questions:
1. Is there a way for me to programmatically check the VBA version that
is on a version of Excel?
2. Can I programmatically set the userform StartUpPosition property to
0 - Manual?

Thanks in advance

 

[Bob Phillips]

Shoji,

Spookily enough, we had the same questionm earlier today. The solution is to
test the Application.Version property, and then call code appropriate to the
version. As Harald Staff pointed out to me, the trick is to place that into
an isolated sub that won't be called in 97 otherwise you get a compilation
error when running in 97.

Somthing like this, it only shows the for show, but the same principle
applies for StartupPosition.

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

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

 

[Tom Ogilvy]

for this property, Bob also suggested to the similar post (and I think it is
a better solution)

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

It was confirmed by Ron de Bruin that he is successfully using that
technique in his application. So it does work.

--------
startupposition isn't a readonly property.

? UserForm1.StartUpPosition
1

so load the form, then set the position and show it.
Actually, refering to the form loads it.

 

[Shoji Kaku]

Thanks for your help. Your solution does provide a way of bypassing the
vbModal argument compilation problem. However, I still have a problem
with that StartUpPosition property on the userform. I have the property
set to 0 - Manual in the Excel 2000 version. But, when I copy the same
file to Excel 97, that property reverts back to 1 - CenterOwner. This
would force me to maintain 2 versions of the application, unless I can
set the StartUpPosition value programatically. Thanks.

 

[Bob Phillips]

Shoji,

Frig it. Load the form, set the startupposition programmatically, then show
it.

Something like

#If VBA6 Then
With UserForm1
.Left = (Application.Width - .Width) / 4
.Top = Application.heigfht / 4
.Show vbModeless
End With
#Else
Load userformm1
With UserForm1
.startuppposition = 0
.Left = (Application.Width - .Width) / 4
.Top = Application.heigfht / 4
.Show
End With
#End If


--

HTH

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

 

[Harald Staff]

for this property, Bob also suggested to the similar post (and I think it is
a better solution)

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.

Yes, confirmed, that code runs fine on 97 for some strange reason (which I expect is the
compilation part of it).

Best wishes Harald
Excel MVP

Followup to newsgroup only please.