Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Mar 2003
    Location
    Sydney, New South Wales, Australia
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel VBA (Excel 2003)

    Does anyone know of a way to use the modeless userform feature in VBA such that the code will still work when used with Excel 97?

    regards
    Mark

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel VBA (Excel 2003)

    There is a trick to make a userform modeless in Excel 97, but Excel MVP Stephen Bullen, who discovered it, warns that you shouldn't rely on it. See Office Automation Ltd. - Stephen Bullen's Excel Page, look for FormFun.zip and ModelessForm.zip.

    As far as I know, there is no way to make a modeless Excel 2000 + userform work in Excel 97.

  3. #3
    Star Lounger
    Join Date
    Mar 2003
    Location
    Sydney, New South Wales, Australia
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA (Excel 2003)

    Thanks Hans,

    I don't care if the form is modal in Excel 97. I just want it to work without producing an error message.

    If you set a form to be modeless, it still seems to work OK, as a modal form, in Excel 97, but you have to put up with an error message first. I would like the code to work in 97 but without the error message. Is this possible?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel VBA (Excel 2003)

    As far as I know, you'll have to set the ShowModal property of the userform to True if you want the workbook to be used both in Excel 2000+ and in Excel 97. You can use conditional compilation to show it modeless in Excel 2000+:

    #IF VBA6 Then
    UserForm1.Show 0
    #Else
    UserForm1.Show
    #End If

    Look up conditional compilation and compiler constants in the online VBA help for more info.

  5. #5
    Star Lounger
    Join Date
    Mar 2003
    Location
    Sydney, New South Wales, Australia
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA (Excel 2003)

    Thanks for your help. That is exactly what I wanted to know.
    regards

  6. #6
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA (Excel 2003)

    Just to elaborate on Conditional Compilation, you can also compile using the Excel version number. For pre-Excel 2000 versions, this would be <code>If CInt(Left(Application.Version, 2)) < 9 Then......</code>. This may save on recoding with future Excel versions. HTH
    Gre

  7. #7
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Kontich, Vlaanderen, Belgium
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA (Excel 2003)

    Hans,

    the link to Office Automation Ltd. - Stephen Bullen's Excel page only shows a blank Title page <img src=/S/confused.gif border=0 alt=confused width=15 height=20> .

    Do you know what might have happened ? (I tried the link both in IE6 and Firefox 1.0.4)

    EDIT : I just found that the URL has changed to http://www.bmsltd.ie/Excel/SBXLPage.asp

    Regards

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA (Excel 2003)

    This is the correct link:
    http://www.oaltd.co.uk/Excel/
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Kontich, Vlaanderen, Belgium
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA (Excel 2003)

    Your right Jan Karel,

    I only found a "partial" link <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

    Regards

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •