Results 1 to 12 of 12

Thread: How to call a certain vba Form from vb6 ?

  1. #1

    Thread Starter
    Hyperactive Member Daniel Duta's Avatar
    Join Date
    Feb 2011
    Location
    Bucharest, Romania
    Posts
    396

    How to call a certain vba Form from vb6 ?

    Even I work with excel library from vb6 for many years today I realized that I don't know how to access a vba Form (and its properties) in runtime. I also activated the MS VB6 Extensibility but still I am stuck. I would appreciate any help.
    "VB code is practically pseudocode" - Tanner Helland
    "When you do things right, people won't be sure you've done anything at all" - Matt Groening
    "If you wait until you are ready, it is almost certainly too late" - Seth Godin
    "Believe nothing you hear, and only one half that you see" - Edgar Allan Poe

  2. #2
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,391

    Re: How to call a certain vba Form from vb6 ?

    https://www.tutorialandexample.com/u...ies-excel-vba/

    What exactly are you trying to accomplish? What properties do you need to access?

  3. #3

    Thread Starter
    Hyperactive Member Daniel Duta's Avatar
    Join Date
    Feb 2011
    Location
    Bucharest, Romania
    Posts
    396

    Re: How to call a certain vba Form from vb6 ?

    Quote Originally Posted by jdc2000 View Post
    https://www.tutorialandexample.com/u...ies-excel-vba/

    What exactly are you trying to accomplish? What properties do you need to access?
    I am looking to disable a Form that is part of an application written in vba Excel. I am able to access via vb6 all other functions or procedures from that Excel but how to call the userform class itself? There should be something about referencing the vba components. Just create a form in excel, insert a few controls and try to handle their properties from vb6.
    Last edited by Daniel Duta; Feb 5th, 2021 at 02:22 AM.
    "VB code is practically pseudocode" - Tanner Helland
    "When you do things right, people won't be sure you've done anything at all" - Matt Groening
    "If you wait until you are ready, it is almost certainly too late" - Seth Godin
    "Believe nothing you hear, and only one half that you see" - Edgar Allan Poe

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: How to call a certain vba Form from vb6 ?

    in its simplest form
    Code:
    Set p = ThisWorkbook.VBProject
    For Each m In p.vbcomponents
        Debug.Print m.Name
    Next
    change the workbook object to your vb6 variable object

    to access a control on an existing userform
    Code:
    Set u = p.vbcomponents("myuserform")
    Set c = u.Designer.Controls("textbox1")
    c.Text = "Hello WOrld"
    this should get you started, if you need more help, just post again

    NOTE in later versions of office /windows macros invoking the vbproject may require special macro security settings
    up to at least 2003 they would just work, after that i am not so sure, so deploying code of this type may involve the user changing macro security settings
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  5. #5

    Thread Starter
    Hyperactive Member Daniel Duta's Avatar
    Join Date
    Feb 2011
    Location
    Bucharest, Romania
    Posts
    396

    Re: How to call a certain vba Form from vb6 ?

    Thnk you for reply, westconn. Still something goes wrong. Below are the rows based on your suggestion:
    Code:
    Public objExcelApp  As Excel.Application
    Public objWorkBook  As Excel.Workbook
    
    Set objExcelApp = New Excel.Application
        objExcelApp.Workbooks.Open myExcel 'worked
    Set objWorkBook = objExcelApp.ActiveWorkbook.VBProject 'failed
    Attached Images Attached Images  
    "VB code is practically pseudocode" - Tanner Helland
    "When you do things right, people won't be sure you've done anything at all" - Matt Groening
    "If you wait until you are ready, it is almost certainly too late" - Seth Godin
    "Believe nothing you hear, and only one half that you see" - Edgar Allan Poe

  6. #6
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,382

    Re: How to call a certain vba Form from vb6 ?

    Did you read, what pete wrote?
    NOTE in later versions of office /windows macros invoking the vbproject may require special macro security settings
    up to at least 2003 they would just work, after that i am not so sure, so deploying code of this type may involve the user changing macro security settings
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  7. #7
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: How to call a certain vba Form from vb6 ?

    that would need to be fixed in excel, macro security
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  8. #8

    Thread Starter
    Hyperactive Member Daniel Duta's Avatar
    Join Date
    Feb 2011
    Location
    Bucharest, Romania
    Posts
    396

    Re: How to call a certain vba Form from vb6 ?

    I have modified the macro security and I was able to set the form I am interested in as follow :
    Code:
    Set p = objExcelApp.ActiveWorkbook.VBProject.VBComponents("frmMain")
    Now I would like to change in runtime the a Form property, let's say p.Caption= "new caption" but I receive an error (438 "Object doesn't support this property or method").
    "VB code is practically pseudocode" - Tanner Helland
    "When you do things right, people won't be sure you've done anything at all" - Matt Groening
    "If you wait until you are ready, it is almost certainly too late" - Seth Godin
    "Believe nothing you hear, and only one half that you see" - Edgar Allan Poe

  9. #9
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,382

    Re: How to call a certain vba Form from vb6 ?

    Have you checked the sub-object „Designer“ if it has a caption-property?
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  10. #10
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: How to call a certain vba Form from vb6 ?

    Code:
    p.Designer.Caption = "xxxzzc"
    not sure if this does what you actually want
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  11. #11

    Thread Starter
    Hyperactive Member Daniel Duta's Avatar
    Join Date
    Feb 2011
    Location
    Bucharest, Romania
    Posts
    396

    Re: How to call a certain vba Form from vb6 ?

    Thank you, westconn. It seems the Designer works for Properties but must of all I would be interested in the Hide method. For some reason Hide method should be called in a different way.
    "VB code is practically pseudocode" - Tanner Helland
    "When you do things right, people won't be sure you've done anything at all" - Matt Groening
    "If you wait until you are ready, it is almost certainly too late" - Seth Godin
    "Believe nothing you hear, and only one half that you see" - Edgar Allan Poe

  12. #12
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: How to call a certain vba Form from vb6 ?

    i would believe the only way you can do this is to write a vba sub in some module (or add a module) in the same workbook (can all be done in code), to hide the userform, then run the sub from vb6
    this may not work at all it the userform is shown modally, though testing, appears it will work in all cases

    same would apply to show a userform
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

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



Click Here to Expand Forum to Full Width