Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Userform object in Excel via automation from VB 6
Pages 804-805 of John Walkenbach's Excel 2002 Power Programming book has an
example of how to programmatically create a Userform. I've done that in both Excel and word, but I'm now trying to create the Userform via automation in VB 6. No problem creating/removing the Userforms, however, how does one display the Userform when running the code from within VB 6? The VBA library for VB does not have a Userform object so the following fails: VBA.UserForms.Add(TempForm.Name).Show Thee following does output the correct Userform name, where ExcelProject is set as wkbExcel.VBProject, where wkbExcel is an Excel Workbook. Debug.Print ExcelProject.VBComponents(TempForm.Name).Name Any pointers to info? -- http://www.standards.com/; See Howard Kaikow's web site. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Userform object in Excel via automation from VB 6
Howard -
I'd think that this would show the form: ExcelProject.VBComponents(TempForm.Name).Show You're treading on thin ice; I always fall through when doing this kind of thing. I've also found that for most of my needs, keeping a dummy form in the Excel project is easier than creating a new form. This form has the generic buttons that any form might need (Okay, Close), and it's easy enough to add controls to this one as it is to a brand new one. Regardless, in VB6, in addition to setting your reference to Excel/Word, I think you should set one to MS VBA Extensibility, which should give you access to the object model of Excel/Word's VBA. - Jon ------- Jon Peltier, Microsoft Excel MVP http://www.geocities.com/jonpeltier/Excel/index.html _______ Howard Kaikow wrote: Pages 804-805 of John Walkenbach's Excel 2002 Power Programming book has an example of how to programmatically create a Userform. I've done that in both Excel and word, but I'm now trying to create the Userform via automation in VB 6. No problem creating/removing the Userforms, however, how does one display the Userform when running the code from within VB 6? The VBA library for VB does not have a Userform object so the following fails: VBA.UserForms.Add(TempForm.Name).Show Thee following does output the correct Userform name, where ExcelProject is set as wkbExcel.VBProject, where wkbExcel is an Excel Workbook. Debug.Print ExcelProject.VBComponents(TempForm.Name).Name Any pointers to info? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Userform object in Excel via automation from VB 6
Thanx.
I do have a reference to the extensibility lib. The code I have is given below. What I really wanted to do was: With objExcelProject.VBComponents(objUserform.Name) .Caption = "bye bye!" .Show End With However, the Caption does not get changed. So my crude workaround was to generate a module that has the needed code, then use Application.Run to run the code in the module. I cannot use a pre-built Userform because the VB 6 code is going to be in a standalone .exe and cannot rely on the existence of any pre-built critters. -------------------------------------------------- Option Explicit Private appExcel As Excel.Application Private wkbExcel As Excel.Workbook Sub Main() RunMakeUserform End Sub Sub RunMakeUserform() Set appExcel = CreateObject("Excel.Application") With appExcel .Visible = True Set wkbExcel = .Workbooks.Add End With MakeUserform wkbExcel.Close SaveChanges:=False appExcel.Quit Set appExcel = Nothing Set wkbExcel = Nothing End Sub Sub MakeUserform() Dim cmdButton As Msforms.CommandButton Dim intLine As Integer Dim objExcelProject As Object ' VBProject Dim objModule As Object ' VBComponent Dim objUserform As Object ' VBComponent Dim strName As String On Error Resume Next Set objExcelProject = wkbExcel.VBProject If Err.Number < 0 Then MsgBox "Your security settings do not allow this macro to run.", vbCritical Exit Sub End If On Error GoTo 0 appExcel.VBE.MainWindow.Visible = False Set objUserform = objExcelProject.VBComponents.Add(3) 'vbext_ct_MSForm With objUserform .Properties("Caption") = "This Userform was created via VB 6" .Properties("Width") = 200 .Properties("Height") = 100 End With Set cmdButton = objUserform.Designer.Controls ..Add("forms.CommandButton.1") With cmdButton .Caption = "Click Me" .Left = 60 .Top = 40 End With With objUserform .Name = "frmHello" With .CodeModule intLine = .CountOfLines .InsertLines intLine + 1, "Sub CommandButton1_Click()" .InsertLines intLine + 2, vbTab & "MsgBox ""Hello!""" .InsertLines intLine + 3, vbTab & "Unload Me" .InsertLines intLine + 4, "End Sub" End With End With Set objModule = objExcelProject.VBComponents.Add(1) 'vbext_ct_StdModule With objModule .Name = "modRunUserform" With .CodeModule intLine = .CountOfLines .InsertLines intLine + 1, "Public Sub RunUserform()" .InsertLines intLine + 2, vbTab & "with frmHello" .InsertLines intLine + 3, vbTab & vbTab & ".caption = " _ & Chr$(34) & "Bye bye!" & Chr$(34) .InsertLines intLine + 4, vbTab & vbTab & ".Show" .InsertLines intLine + 5, vbTab & "end with" .InsertLines intLine + 6, "End Sub" End With End With ' objExcelProject.VBComponents(objUserform.Name).Sho w 'Works ' With objExcelProject.VBComponents(objUserform.Name) ' .Caption = "bye bye!" ' Does not change caption ' .Show ' End With strName = wkbExcel.Name & "!" & "RunUserform" appExcel.Run strName With objExcelProject.VBComponents .Remove objUserform .Remove objModule End With Set objExcelProject = Nothing Set objModule = Nothing Set objUserform = Nothing Set cmdButton = Nothing End Sub -- http://www.standards.com/; See Howard Kaikow's web site. "Jon Peltier" wrote in message ... Howard - I'd think that this would show the form: ExcelProject.VBComponents(TempForm.Name).Show You're treading on thin ice; I always fall through when doing this kind of thing. I've also found that for most of my needs, keeping a dummy form in the Excel project is easier than creating a new form. This form has the generic buttons that any form might need (Okay, Close), and it's easy enough to add controls to this one as it is to a brand new one. Regardless, in VB6, in addition to setting your reference to Excel/Word, I think you should set one to MS VBA Extensibility, which should give you access to the object model of Excel/Word's VBA. - Jon ------- Jon Peltier, Microsoft Excel MVP http://www.geocities.com/jonpeltier/Excel/index.html _______ Howard Kaikow wrote: Pages 804-805 of John Walkenbach's Excel 2002 Power Programming book has an example of how to programmatically create a Userform. I've done that in both Excel and word, but I'm now trying to create the Userform via automation in VB 6. No problem creating/removing the Userforms, however, how does one display the Userform when running the code from within VB 6? The VBA library for VB does not have a Userform object so the following fails: VBA.UserForms.Add(TempForm.Name).Show Thee following does output the correct Userform name, where ExcelProject is set as wkbExcel.VBProject, where wkbExcel is an Excel Workbook. Debug.Print ExcelProject.VBComponents(TempForm.Name).Name Any pointers to info? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Userform object in Excel via automation from VB 6
Hi Howard,
What I really wanted to do was: With objExcelProject.VBComponents(objUserform.Name) .Caption = "bye bye!" .Show End With However, the Caption does not get changed. I probably don't want to know <g, but why are you trying to create the form within the workbook, show it and delete it? Why don't you just have a form in the VB6 project? Referencing the VBComponent as you've done returns a VBComponent object, not a UserForm object, so you can't set the properties directly. However, the VBComponent does have a Properties collection that contains all the properties you see in the Properties window when you click on a userform. Hence, the following works for me: With objExcelProject.VBComponents(objUserform.Name) .Properties("Caption") = "bye bye!" .Show End With Though I haven't investigated what would happen if that was run in a non-English version of Excel! Regards Stephen Bullen Microsoft MVP - Excel www.BMSLtd.co.uk |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Userform object in Excel via automation from VB 6
"Stephen Bullen" wrote in message
... Hi Howard, What I really wanted to do was: With objExcelProject.VBComponents(objUserform.Name) .Caption = "bye bye!" .Show End With However, the Caption does not get changed. I probably don't want to know <g, but why are you trying to create the form within the workbook, show it and delete it? I was using the example in John Walkenbach's book for purposes of discussion. Why don't you just have a form in the VB6 project? It has to be a Userform that is run via Automation. Referencing the VBComponent as you've done returns a VBComponent object, not a UserForm object, so you can't set the properties directly. Is there a way to reference the created Userform directly? I 've not found a way to do that. However, the VBComponent does have a Properties collection that contains all the properties you see in the Properties window when you click on a userform. Hence, the following works for me: With objExcelProject.VBComponents(objUserform.Name) .Properties("Caption") = "bye bye!" .Show End With The .Show still does not work. If I cannot directly access the Userform object, and, more importantly, the controls in the VB code, then I'll do things by generating code for Userform_Initialize, etc. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Userform object in Excel via automation from VB 6
Howard -
I was going to ask Stephen's question next: why not use a VB6 form. It's part of the whole automation that the VB is doing, and the user won't care whose form is showing. You will, because VB forms and VBA forms are different animals, but if I can figure it out, so can you. - Jon ------- Jon Peltier, Microsoft Excel MVP http://www.geocities.com/jonpeltier/Excel/index.html _______ Howard Kaikow wrote: "Stephen Bullen" wrote in message ... Hi Howard, What I really wanted to do was: With objExcelProject.VBComponents(objUserform.Name) .Caption = "bye bye!" .Show End With However, the Caption does not get changed. I probably don't want to know <g, but why are you trying to create the form within the workbook, show it and delete it? I was using the example in John Walkenbach's book for purposes of discussion. Why don't you just have a form in the VB6 project? It has to be a Userform that is run via Automation. Referencing the VBComponent as you've done returns a VBComponent object, not a UserForm object, so you can't set the properties directly. Is there a way to reference the created Userform directly? I 've not found a way to do that. However, the VBComponent does have a Properties collection that contains all the properties you see in the Properties window when you click on a userform. Hence, the following works for me: With objExcelProject.VBComponents(objUserform.Name) .Properties("Caption") = "bye bye!" .Show End With The .Show still does not work. If I cannot directly access the Userform object, and, more importantly, the controls in the VB code, then I'll do things by generating code for Userform_Initialize, etc. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Userform object in Excel via automation from VB 6
In this case, I need a VBA Userform.
-- http://www.standards.com/; See Howard Kaikow's web site. "Jon Peltier" wrote in message ... Howard - I was going to ask Stephen's question next: why not use a VB6 form. It's part of the whole automation that the VB is doing, and the user won't care whose form is showing. You will, because VB forms and VBA forms are different animals, but if I can figure it out, so can you. - Jon ------- Jon Peltier, Microsoft Excel MVP http://www.geocities.com/jonpeltier/Excel/index.html _______ Howard Kaikow wrote: "Stephen Bullen" wrote in message ... Hi Howard, What I really wanted to do was: With objExcelProject.VBComponents(objUserform.Name) .Caption = "bye bye!" .Show End With However, the Caption does not get changed. I probably don't want to know <g, but why are you trying to create the form within the workbook, show it and delete it? I was using the example in John Walkenbach's book for purposes of discussion. Why don't you just have a form in the VB6 project? It has to be a Userform that is run via Automation. Referencing the VBComponent as you've done returns a VBComponent object, not a UserForm object, so you can't set the properties directly. Is there a way to reference the created Userform directly? I 've not found a way to do that. However, the VBComponent does have a Properties collection that contains all the properties you see in the Properties window when you click on a userform. Hence, the following works for me: With objExcelProject.VBComponents(objUserform.Name) .Properties("Caption") = "bye bye!" .Show End With The .Show still does not work. If I cannot directly access the Userform object, and, more importantly, the controls in the VB code, then I'll do things by generating code for Userform_Initialize, etc. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Userform object in Excel via automation from VB 6
Hi Howard,
It has to be a Userform that is run via Automation. Just curious, but why? It's not something I've ever come across before. Is there a way to reference the created Userform directly? I 've not found a way to do that. The 'Inside' of the userform - i.e. everything in the 'client' part of the window can be accessed via the VBComponent's Designer property, which returns an object of type UserForm, for you to access all the controls on the form, etc. The 'Outside' of the userform - i.e. the size, caption etc can only (to my knowledge) be controlled using the VBComponent's Properties collection. The .Show still does not work. In the same way the we can't show a form in one project from code in a different project, I don't think we can directly show a VBA userform using automation. The best we can do is to add a standard module, add code to it to display the form, then use Application.Run to call it, as you've done. Regards Stephen Bullen Microsoft MVP - Excel www.BMSLtd.co.uk |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Userform object in Excel via automation from VB 6
Hi Howard,
It has to be a Userform that is run via Automation. Just curious, but why? It's not something I've ever come across before. One reason is to facilitate using extant VBA code via Automation from VB 6. It's too expensive to redo all Userforms, especially the underlying code. All these issues will go away if MSFT ever fully .NET-izes Office so everything uses windoze forms, but we are several years from reaching that point. Perhaps VSTO is smarter at dealing with VBA Userforms. I have my doubts. I have not yet installed VSTO because I can't take the ensuing distraction until I finish a few other tasks. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Userform object in Excel via automation from VB 6
Hi Howard,
Perhaps VSTO is smarter at dealing with VBA Userforms. I have my doubts. I have not yet installed VSTO because I can't take the ensuing distraction until I finish a few other tasks. Nope. VSTO just uses the same COM interface that all other automation clients use. Regards Stephen Bullen Microsoft MVP - Excel www.BMSLtd.co.uk |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using Userform object in Excel via automation from VB 6
Hi all,
I don't have VB now and I don't know the original post so what I wrote may wide of the mark... It is a way for showing a userform from the from name(string). '---------------------------------------- VBA.UserForms.Add(objUserform.Name).Show '---------------------------------------- Excuse me for disturbing you. :D Kind Regards Colo /_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/ Colo of 'The Road of The Cell Masters' :) URL:http://www.interq.or.jp/sun/puremis/...astersLink.htm /_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/ "Howard Kaikow" wrote in message ... "Stephen Bullen" wrote in message ... Hi Howard, What I really wanted to do was: With objExcelProject.VBComponents(objUserform.Name) .Caption = "bye bye!" .Show End With However, the Caption does not get changed. I probably don't want to know <g, but why are you trying to create the form within the workbook, show it and delete it? I was using the example in John Walkenbach's book for purposes of discussion. Why don't you just have a form in the VB6 project? It has to be a Userform that is run via Automation. Referencing the VBComponent as you've done returns a VBComponent object, not a UserForm object, so you can't set the properties directly. Is there a way to reference the created Userform directly? I 've not found a way to do that. However, the VBComponent does have a Properties collection that contains all the properties you see in the Properties window when you click on a userform. Hence, the following works for me: With objExcelProject.VBComponents(objUserform.Name) .Properties("Caption") = "bye bye!" .Show End With The .Show still does not work. If I cannot directly access the Userform object, and, more importantly, the controls in the VB code, then I'll do things by generating code for Userform_Initialize, etc. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Excel with automation | Excel Discussion (Misc queries) | |||
Ole automation with Excel 10 from Vb.net | Excel Programming | |||
unable to create embedded object on a userform | Excel Programming | |||
Automation Error: The Object Invoked Has Disconnected from Its Clients (Excel) | Excel Programming | |||
Automation Error: The Object Invoked Has Disconnected from Its Clients | Excel Programming |