Get control of a Userform by the Name

[Joe]

I want to change the text of a form.
I know the name of the form and the name of the control in it.

How to get the control of a form if I know the Name?

Below post refers to adding a form. But I already have a form, just
need the control of that!
http://groups.google.co.in/group/mi...hl=en&lnk=gst&q=form+by+name#f2df993ded867dd4

Thanks a lot in advance

Regards'
Joe

 

[joel]

If you are talking about a VBA user from in VBA window press F4 (Vie
properties window) and click on the control. The name will be in th
Name property.

If you have a worksheet user form you should be able to click on th
object and the name will be in the box next to the formula box (fx)

 

[Joe]

But I need it in runtime !

 

[Joe]

I want to change the text of a form.
I know the name of the form and the name of the control in it.

How to get the control of a form if I know the Name?

Below post refers to adding a form. But I already have a form, just
need the control of that!http://groups.google.co.in/group/microsoft.public.excel.programming/b...

Thanks a lot in advance

Regards'
Joe

I got a very Nice Link...
http://www.cpearson.com/excel/ShowAnyForm.htm

I am trying it out...

 

[Rick Rothstein]

Your description is somewhat confusing... can you give more detail about
what you are actually trying to do?

 

[Chip Pearson]

I want to change the text of a form.

What do you mean by the "text of a form"? I will assume you mean the
Caption that appears on the title bar of the form.

Try some code like the following:

Sub ChangeCaption()
Dim FormName As String
Dim NewCaption As String
Dim UF As UserForm
Dim N As Long
Dim B As Boolean
FormName = "UserForm2"
NewCaption = "This Is New"
Load UserForm1

VBA.UserForms.Add FormName

For N = 0 To VBA.UserForms.Count - 1
If StrComp(VBA.UserForms(N).Name, FormName, _
vbTextCompare) = 0 Then
VBA.UserForms(N).Caption = NewCaption
B = True
Exit For
End If
Next N
If B = True Then
VBA.UserForms(N).Show
End If
End Sub


The form whose caption you want to change must already be loaded into
memory, but not necessarily visible. To load a form into memory
without making it visible, use either of

Load UserForm2

' OR

VBA.UserForms.Add "UserForm2"


Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com