Adding Controls to a userform 2

[peterpcu]

Hi There,

Just a simple Excel/VBA question which I hope the forum can answer.

What is the VBA code that will add a new control such as a commandbutton or a textbox to a userform?

Thanks in advance.

PCU

 

[TonyJollans]

It can be done but it would be better to have the control(s) you want on the form anyway and just not visible and then to make them visible when you want them.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[PHV]

Anyway, feel free to play with the macrorecorder...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TonyJollans]

PH - How do you record actions in the VBE?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[PHV]

Sorry Tony, I obviously can't :~/

 

[peterpcu]

Hi,

Many thanks to PHV and Tony for attempting to answer my question.

What I really want to do is to start with a blank userform the write some code that will place a brand new commandbutton or textbox or checkbox or dropdown box on the userform. I also want to name the object with a predefined name and caption.

Am I expecting too much from good old VBA?

Regards

PCU

 

[TonyJollans]

Hi PCU,

As I said, it can be done. Here's something to get you going although I would like to know why you can't go the easy route:

[blue]Dim frm As VBComponent
Dim btn As MSForms.CommandButton

Set frm = ActiveDocument.VBProject.VBComponents("UserForm1")
Set btn = frm.Designer.Controls.Add("Forms.CommandButton.1", "YourButtonName")

With btn
    .Caption = "Click Me!"
    .Left = 10 [green]'wherever you want it[/green]
    .Top = 10 [green]'wherever you want it[/green]
End With[/blue]

You will need a reference to the Microsoft Visual Basic for Applications Extensibility library for the VBComponent object type.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[fumei]

What I really want to do is to start with a blank userform the write some code that will place a brand new commandbutton or textbox or checkbox or dropdown box on the userform. I also want to name the object with a predefined name and caption.

I am with Tony on this, asking .....why?

WHY are you doing this? What is the purpose and reason for doing this?

Gerry

 

[peterpcu]

Hi Tony,

The reason why I asked my question was:-

I have a number of userforms (with underlying code) to create which are similar but not quite the same.
One way to do this is to generate the first one then copy the objects to new userforms.

The VBA code needs to be modified for each new userform (which is easy with replace) but the problem is that all the objects need to be renamed as each must have its own unique name.

I did try Tony's suggested code which would have been the answer but the code in my version of Excel [2002], fails around the vbcomponent area.

Any thoughts would be much appreciated.

Regards

PCU

 

[TonyJollans]

Hi PCU,

What was the failure? Can you be a bit more specific?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[peterpcu]

Tony,

I have put your code within the usual Sub, End sub stuff and put it in the module area. I have also created a new userform.

The error I get on the line:-

Dim frm As VBComponent

is a msgbox saying:-

compile error:
User defined type not defined

Thanks for your help

PCU

 

[TonyJollans]

Hi PCU,

You need to add a reference to the VBIDE file - see at the end of my post with the code.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[peterpcu]

Hi Tony,
I added the extensibility reference Ok and made sure that a userform named UserForm1 was created. the code now stops at the line:-

Set frm = ActiveDocument.VBProject.VBComponents("UserForm1")

with the error message Run Time error '424' object required.

Getting Closer!!

Pete

 

[TonyJollans]

Hi Pete,

Sorry, for Excel change ActiveDocument to ActiveWorkbook

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[FaneDuru]

This code creates a form and a button on it (including its code):

Dim TempForm As Object ' VBComponent Form - 3       
Dim NewButton As MSForms.CommandButton
 Set TempForm = ThisWorkbook.VBProject.VBComponents.Add(3) 
    With TempForm
        .Properties("Caption") = "Test"
        .Properties("Width") = 200
        .Properties("Height") = 100
    End With
    Set NewButton = TempForm.Designer.Controls _
      .Add("forms.CommandButton.1")
    With NewButton
        .Caption = "Greetings!"
        .Left = 60
        .Top = 40
   End With
    With TempForm.CodeModule
        X = .CountOfLines
        .InsertLines X + 1, "Sub CommandButton1_Click()"
        .InsertLines X + 2, "MsgBox ""Hello!"""
        .InsertLines X + 3, "Unload Me"
        .InsertLines X + 4, "End Sub"
    End With
  VBA.UserForms.Add(TempForm.Name).Show

You don't need Extensibility library reference...
Fane Duru

 

[peterpcu]

Hi Fane Duru,

Excellent, many thanks the code works a treat.

Thanks also to Tony for your help.

Regards

Pete