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
