Results 1 to 12 of 12
  1. #1
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Retrieving UserForm Properties (Office 2000 & later)

    Hi All,

    I have an 'applet' I've developed as a UserForm in Excel, and I'd like to turn it into a stand-alone executable via VB. Other than referencing the MS Forms Object Library, I'm not using any Office-specific code - the form doesn't even read or write anything to the worksheet and the code runs just as happily in Word. Since it seems I can't simply convert a UserForm to a proper VB form, I was hoping I could extract the properties of all the (200+) controls on my userform and, perhaps, programatically re-create the beast using VB with the correct calls.

    Is either of these feasible? So far, I haven't found a simple way to read all the control properties on my userform - it seems you have to know which properties go with which type of control before reading their values.

    Any ideas?
    Cheers,

    Paul Edstein
    [MS MVP - Word]

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

    Re: Retrieving UserForm Properties (Office 2000 & later)

    You'd have to loop through the controls and use a Select Case to handle each type of control. It might be faster to recreate the form manually...

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retrieving UserForm Properties (Office 2000 & later)

    I may be missing something here, but I tried creating a UserForm in XL with a scattering of controls. I used Export File... to save it as UserForm1.frm. When I imported that into VB6, it appeared to be an exact replica. ???

    Alan
    Attached Images Attached Images

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

    Re: Retrieving UserForm Properties (Office 2000 & later)

    Too easy! <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15>

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

    Re: Retrieving UserForm Properties (Office 2000 & later)

    Yes, but it does not become a native VB6 form, instead it shows up as a Designer object and does not have the same functionality as a native VB6 form. It does work however.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Retrieving UserForm Properties (Office 2000 & later)

    Hi Alan,

    I don't have VB6 and was thinking more in terms of converting to VB.NET code and compiling that, for which I based my understanding on:
    http://msdn2.microsoft.com/en-us/library/a...office.11).aspx
    and
    http://msdn2.microsoft.com/en-us/library/a...office.11).aspx
    Maybe I should try to track down a copy of VB6. It seems that would make life easier.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  7. #7
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Retrieving UserForm Properties (Office 2000 & later)

    Hi Jan,

    Does that also mean the user would need to have MS Office installed to use it, since the vba version of the user form requires the MS Office Forms Object Library?
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  8. #8
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Retrieving UserForm Properties (Office 2000 & later)

    I didn't realize you needed to go as deep as the coding. The simple test I did only went as far as preserving the control properties/ attributes... then again, this might arguably be the most labour-intensive part of the conversion. ???

    Alan

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

    Re: Retrieving UserForm Properties (Office 2000 & later)

    Well, the simplest check is to remove the reference to the forms lib from the project's reference I guess?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #10
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Retrieving UserForm Properties (Office 2000 & later)

    Ah, but I can't do that since I don't have VB6 - yet - and I didn't want to invest in it only to find I'm no better off!
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  11. #11
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Retrieving UserForm Properties (Office 2000 & later)

    Hi Alan,

    I've got 1500 lines of code attached to this form, so it would be important that the two would still work.

    I've managed to cobble together some code to extract the various control properties, but getting a complete listing will take a fair bit more effort. But then the as yet unanswered question becomes: can this then be translated into an equivalent set of VB6/VB.NET controls.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

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

    Re: Retrieving UserForm Properties (Office 2000 & later)

    Well, the project I have -which contains an imported userform- has no reference to the msforms library. No idea if it runs without, because this is a tool directed at Excel anyway, so no sigar when Office isn't on the machine anyway.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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