Wrox Programmer Forums
BOOK: Excel 2007 VBA Programmer's Reference ISBN: 978-0-470-04643-2
This is the forum to discuss the Wrox book Excel 2007 VBA Programmer's Reference by John Green, Stephen Bullen, Rob Bovey, Michael Alexander; ISBN: 9780470046432
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Excel 2007 VBA Programmer's Reference ISBN: 978-0-470-04643-2 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old May 30th, 2008, 11:49 AM
Registered User
Join Date: May 2008
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Adding User Forms at Run time

I am trying to create a new user at run time (on-the-fly) but not having any success at showing the new form.

I have used the discussion in Chapter 26 as a basis for my code and also Chapter 28 of "Excel 2007 Power Programming with VBA" by John Walkenback.

I start by creating a new workbook to which to add the form to, add the form and a couple of controls, and then destroy the workbook (and form) at the end. Here is the code:
Public Sub CreateRunTimeForm()
    'Create a temporary workbook for the new form
    Dim tempWorkbook As Workbook
    Set tempWorkbook = Workbooks.Add

    Dim myLabel As String
    myLabel = "My Option Button"

    'Add user form
    Dim myNewForm As Object
    Set myNewForm = tempWorkbook.VBProject.VBComponents.Add(vbext_ct_M SForm)

    Dim myFrame As MSForms.Frame
    Set myFrame = myNewForm.Designer.Controls.Add("Forms.Frame.1")

    Dim myButton As MSForms.OptionButton
    Set myButton = myFrame.Controls.Add("Forms.OptionButton.1")

    Dim labelWidth As Double

    With myButton
        .Caption = myLabel
        .left = 6
        .top = 6
        .AutoSize = True
        labelWidth = .width
    End With

    With myFrame
        .left = 6
        .top = 6
        .Height = 36
        .width = labelWidth + 12
        .Caption = "My Frame"
    End With

    With myNewForm
        .Properties("Top") = 0
        .Properties("Left") = 0
        .Properties("Height") = 72.75
        .Properties("Width") = 34.5 + labelWidth
        .Properties("Caption") = "My Form"
        .Properties("Name") = "MyNewForm"
    End With

    'TODO: Add code for form

    MsgBox "Form created"
    Debug.Print "Form created"

    'Show form
'This does not work
' myNewForm.Show
'This method of adding the form to the UserForms collection
'then showing it does not work either
' VBA.UserForms.Add("MyNewForm").Show

    Set myButton = Nothing
    Set myFrame = Nothing
    Set myNewForm = Nothing

    'Close temporary workbook without saving
    tempWorkbook.Close SaveChanges:=False
    Set tempWorkbook = Nothing
End Sub
end code

In Walkenbach's book he states:
begin quote
If you'd like to show the form after adding the controls at design time, you need to add the following instruction right before the End Sub statement:
It took me quite a while to figure out how to actually display the UserForm. When VBA generates the 100-button UserForm, it indeed exists in VBA's memory, but it isn't officially part of the project yet. So you need the Add method to formally enroll UserForm1 into the collection of UserForms. The return value of this method is a reference to the form itself, which is why the Show method can be appended to the end of the Add method. So, as a rule, the UserForm must be added to the UserForms collection before it can be used.
end quote

I know the workbook and form are being created properly as I can see it while in debug mode and if I comment out the closing of the workbook I can see the form in the Project Explorer.

Any ideas? Thanks!

Similar Threads
Thread Thread Starter Forum Replies Last Post
Adding button at run time msathyaprasad90 ASP.NET 1.0 and 1.1 Professional 8 August 9th, 2006 11:05 PM
Adding Text boxes on run time jmss66 VB How-To 1 March 31st, 2006 03:32 PM
Updating controls on forms at run-time chipset VB How-To 5 September 25th, 2004 05:25 AM
Changing forms properties at run time Louisa VB.NET 2002/2003 Basics 5 January 15th, 2004 01:07 PM
Adding controls at run-time !! kishore_peddi C# 0 October 13th, 2003 06:52 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.