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
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
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
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
"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
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
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
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
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
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
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
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