Wrox Programmer Forums

Need to download code?

View our list of code downloads.

| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old May 30th, 2008, 11:49 AM
Registered User
 
Join Date: May 2008
Location: , , .
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:
  VBA.UserForms.Add("UserForm1").Show
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!

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 11:25 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.