Using Userform object in Excel via automation from VB 6

  • Thread starter Thread starter Howard Kaikow
  • Start date Start date
H

Howard Kaikow

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?
 
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
 
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).Show '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
 
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.
Click to expand...

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
 
Stephen Bullen said:
Hi Howard,


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?
Click to expand...

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?
Click to expand...

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.
Click to expand...

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
Click to expand...

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.
 
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
 
Hi Howard,
It has to be a Userform that is run via Automation.
Click to expand...

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.
Click to expand...

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.
Click to expand...

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
 
Hi Howard,
Just curious, but why? It's not something I've ever come across
before.
Click to expand...

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.
 
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.
Click to expand...

Nope. VSTO just uses the same COM interface that all other automation
clients use.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.BMSLtd.co.uk
 
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/colo/CellMastersLink.htm
mailto:[email protected]

/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
 
Back
Top