PDA

View Full Version : Creating a UserForm Programatically, with Frame



smaccabe
08-04-2010, 12:41 PM
I'm trying to create a macro that will create a userform which works fine, except when I use a frame. I can't find a way of creating controls inside the frame that has been created.

This is my code:


Sub CreateForms()
Dim TempForm As Object
Dim NewLabel As MSForms.Label
Dim NewFrame As MSForms.Frame
Dim Line As Integer
Dim wkBook As Workbook

Set wkBook = ActiveWorkbook

Application.VBE.MainWindow.Visible = False

Set TempForm = wkBook.VBProject.VBComponents.Add(3)

With TempForm
.Properties("Caption") = "Progress"
.Properties("Name") = "ProgBar"
.Properties("Width") = 220.5
.Properties("Height") = 78.75
.Properties("BackColor") = &HFF0000
End With

'Create Label
Set NewLabel = TempForm.Designer.Controls.Add("Forms.label.1")
With NewLabel
.Name = "ProgBarDesc"
.Caption = ""
.Top = 3.75
.Left = 6
.Width = 114
.Height = 13.5
.Font.Size = 8
.Font.Name = "Tahoma"
.BackColor = &HFF0000
.BorderStyle = fmBorderStyleNone
End With

'Create Frame
Set NewFrame = TempForm.Designer.Controls.Add("Forms.frame.1")
With NewFrame
.Name = "ProgBarFrame"
.Caption = "0%"
.Top = 19.24
.Left = 6
.Width = 204
.Height = 28
.Font.Size = 8
.Font.Name = "Tahoma"
.BackColor = &HFF0000
.BorderStyle = fmBorderStyleNone
.SpecialEffect = fmSpecialEffectEtched
End With

'Create Frame Label
Set NewLabel = TempForm.Designer.Controls.Add("Forms.label.1")
With NewLabel
.Name = "ProgBarLabel"
.Caption = ""
.Top = 5
.Left = 2.3
.Width = 20
.Height = 13
.BackColor = &H8080FF
.BorderStyle = fmBorderStyleNone
.SpecialEffect = fmSpecialEffectBump
End With

End Sub
I want the ProgBarLabel label inside the Frame, does anyone know how to do it?

Thanks
Steve

scottdk
11-22-2011, 07:50 PM
I want the ProgBarLabel label inside the Frame, does anyone know how to do it?



I know this question has been open from quite some time, but I thought I would make it complete as it keeps coming up in Google when I search for the answer myself.
I've been trying to work this out for the last two days :banghead: and I finally got it, thanks to ericc in another thread (vbaexpress.com/forum/showthread.php?t=26167)

Please change

'Create Frame Label
Set NewLabel = TempForm.Designer.Controls.Add("Forms.label.1")
to

'Create Frame Label
Set NewLabel = NewFrame.Controls.Add("Forms.label.1")

Manipulation at design time of my 80 identical frames full of controls lined up over a 5 Page MultiPage has just become a LOT easier... All they needed was one new label each.....

Cheers
Scott

Bob Phillips
11-22-2011, 08:32 PM
Scott,

Whilst I think you are mad to have a multpage userform with 80 multi-control frames :), it is appreciated you updated the archive. This could now help someone else.

scottdk
11-22-2011, 09:11 PM
Thanks!

Indeed, fixing applications built by power-users has driven me certifiably insane :bug: , and if I wasn't already, then adding and lining up a text boxes and command buttons to 80 frames and inserting event code all done manually (as had probably been done in the past) would have tipped me over the edge.

Without time or scope to overhaul it and build the form at run-time, at least now I can quickly implement design-time changes with a few lines of code.

It's all good. Messes that need fixing keep us developers employed.

Cheers!

Bob Phillips
11-22-2011, 09:33 PM
Have you not tried adding an event class for those controls?

scottdk
11-22-2011, 10:25 PM
Sweet! I didn't know about Event Classes before now. One piece of code to determine which button was clicked

So I take it at run time, I will create a new instance of the event class for each command button I place at run time (or have placed at design time - as I've already written the code and modified the VBProject).

What's "best practice" for this? An array of Event Classes perhaps at the form level? I suppose that is irrelevant as long as the class is instantiated for each control whose events I want to capture. I could just create a new object and throw it in a collection I suppose...

For anyone else who might be following this, I can't post links yet, but an example of an Event Class is at vbaexpress.com/kb/getarticle.php?kb_id=327#instr

scottdk
11-22-2011, 11:56 PM
It works perfectly! I wrote approximately 10 lines of code to handle all 80 Command Buttons. Thanks so much!!!

The contents of my Class module are below. When the form is initialized, for each of my Buttons (I use raised labels as they take less space) I put a new instance of this class into a collection, setting lblLabelEvents to the label whose click I want to capture. I also point to a Text Box on the form so I can capture its value when the label is clicked (the click opens up a text editing form...)

Option Explicit

Public WithEvents lblLabelEvents As MSForms.Label
Public txtFormMemo As MSForms.TextBox

Private Sub lblLabelEvents _Click()
lblLabelEvents .SpecialEffect = fmSpecialEffectSunken
Set frmEditMemo.txtOriginalText = txtFormMemo
frmEditMemo.show
lblLabelEvents .SpecialEffect = fmSpecialEffectRaised
End Sub
mil gracias
Scott

smaccabe
11-23-2011, 12:43 AM
Thanks for coming back with an answer Scottdk.

I completely forgot I'd posted here as it's been so long, but must have found an answer somewhere as I've been using that macro for a while now.

I'll have a look at what I've done and let you know how I got round it myself later.

And an 80 frame form, you're nuts lol

Bob Phillips
11-23-2011, 02:57 AM
It works perfectly! I wrote approximately 10 lines of code to handle all 80 Command Buttons. Thanks so much!!!

You could have just called a generic button handler from ecah of the individual buttons saving space, but event classes are better even though not all events are exposed to the class.

But it is good, isn't it?

Bob Phillips
11-23-2011, 02:58 AM
And an 80 frame form, you're nuts lol

My thoughts exactly :yes

lionne
12-02-2011, 04:36 AM
Hi guys
nice to see such solution, I have the same problem. The only thing - it doesn't work for me. Each time by pressing one button I create a form with variable number of fields. These fields I then need to group, and I give the frame to each group... Could it be, that it doesn't work for me, because I have always a variable numbers of fields and frames?
would be glad receiving any comment on that..
by the way, the VBA crashes here:

Set NewTextBox = NewFrame.designer.Controls.Add("Forms.TextBox.1")

and says:
438: The Object doesn't support this Property or Method

scottdk
12-02-2011, 05:01 AM
Hi guys
Each time by pressing one button I create a form with variable number of fields. These fields I then need to group, and I give the frame to each group... Could it be, that it doesn't work for me, because I have always a variable numbers of fields and frames?

Set NewTextBox = NewFrame.designer.Controls.Add("Forms.TextBox.1")


Hi Lionne,

I would be suspicious that Intellisense didn't capitalise the word "designer" in your code...

From what I understand, when editing the Form, you reference Designer.

Set NewFrame = UserForm.Designer.Controls.Add("Forms.label.1")

Your NewFrame is already (somehow) in the "Designer" world, so you only need to reference NewFrame.Controls.Add. If you look at my first post (second post in this thread) I actually mentioned that :



Please change
'Create Frame Label
Set NewLabel = TempForm.Designer.Controls.Add("Forms.label.1")
to
'Create Frame Label
Set NewLabel = NewFrame.Controls.Add("Forms.label.1")


Am I right here everyone? (Can't test any code, I'm not on a PC at the moment)
To edit at design time:
Set NewFrame = UserForm1.Designer.Controls.Add("Forms.label.1")
Set NewLabel = NewFrame.Controls.Add("Forms.label.1")
To create at run time:
Set NewFrame = UserForm1.Controls.Add("Forms.label.1")
Set NewLabel = NewFrame.Controls.Add("Forms.label.1")

i.e. The code to insert the label into the frame is the same. The reference to the "Designer" is only a property of the Form? Please correct me if I'm wrong! I'm trying to understand this myself!!

lionne
12-02-2011, 05:08 AM
Hi Scott
unbelievable, it works!!! Thank you so much!
Now I've got another problem with heights and widths of frames, 'cause somehow after adding the labels and textboxes to them, everything has changed... but I have to play with it first to understand.
Regarding Designer - actually no idea, I am completely new to VBA, got a small project 2 months ago, so I am just reading the posts in case I get a problem... sorry for not being able to help you..
Lionne