Variable to automatically create a useform layout

[NicoCaps]

Hi all,

I have userform where users can input a variable which represents a quantity
of "X" products. I want this variable to be used as an input for another
userform which will display the associated "X" number of text fields.

How can this second userform layout be automatically designed (position of
text fields, alignment, etc)?

I have no code at the moment (excpet for the variable storage in the first
userform) and am looking for a similar post where this subject has already
been treated.

Tanks for your help

Nicolas

 

[Jan De Messemaeker]

Hi,

You can use the add method for controls. I paste the help below because it
took me quite some time to find it.

=====================================================================
Add Method

Adds or inserts a Tab or Page in a TabStrip or MultiPage, or adds a control
by its programmatic identifier (ProgID) to a page or form.

Syntax

For MultiPage, TabStrip
Set Object = object.Add( [ Name [, Caption [, index]]])

For other controls
Set Control = object.Add( ProgID [, Name [, Visible]])

The Add method syntax has these parts:

Part Description
object Required. A valid object name.
Name Optional. Specifies the name of the object being added. If a name
is not specified, the system generates a default name based on the rules of
the application where the form is used.
Caption Optional. Specifies the caption to appear on a tab or a
control. If a caption is not specified, the system generates a default
caption based on the rules of the application where the form is used.
index Optional. Identifies the position of a page or tab within a
Pages or Tabs collection. If an index is not specified, the system appends
the page or tab to the end of the Pages or Tabs collection and assigns the
appropriate index value.
ProgID Required. Programmatic identifier. A text string with no spaces
that identifies an object class. The standard syntax for a ProgID is
<Vendor>.<Component>.<Version>. A ProgID is mapped to a class identifier
(CLSID).
Visible Optional. True if the object is visible (default). False if
the object is hidden.

Settings

ProgID values for individual controls are:

CheckBox Forms.CheckBox.1
ComboBox Forms.ComboBox.1
CommandButton Forms.CommandButton.1
Frame Forms.Frame.1
Image Forms.Image.1
Label Forms.Label.1
ListBox Forms.ListBox.1
MultiPage Forms.MultiPage.1
OptionButton Forms.OptionButton.1
ScrollBar Forms.ScrollBar.1
SpinButton Forms.SpinButton.1
TabStrip Forms.TabStrip.1
TextBox Forms.TextBox.1
ToggleButton Forms.ToggleButton.1

Remarks

For a MultiPage control, the Add method returns a Page object. For a
TabStrip, it returns a Tab object. The index value for the first Page or Tab
of a collection is 0, the value for the second Page or Tab is 1, and so on.

For the Controls collection of an object, the Add method returns a control
corresponding to the specified ProgID. The AddControl event occurs after the
control is added.

You can add a control to a user form's Controls collection at design time,
but you must use the Designer property of the Microsoft Visual Basic for
Applications Extensibility Library to do so. The Designer property returns
the UserForm object.

The following syntax will return the Text property of the specified control:

userform1.thebox.text

If you add a control at run time, you must use the exclamation syntax to
reference properties of that control. For example, to return the Text
property of a control added at run time, use the following syntax:

userform1!thebox.text


Note
You can change a control's Name property at run time only if you added
that control at run time with the Add method.

=======================================================================
--
Jan De Messemaeker
Microsoft Project Most Valuable Professional
+32 495 300 620
For availability check:
http://users.online.be/prom-ade/Calendar.pdf

 

[NicoCaps]

Jan thanks for your quick reply,

As I am pretty illiterate in programming I only adapt solutions already
existing to my specific needs, therefore I have trouble starting from blank
code.

Here's what I came up with so far:
Option Explicit
Dim txtbx() As TextBox

Private Sub Form_Load()
Dim i As Integer


For i = 0 To i = 10
ReDim Preserve txtbx(i)
Set txtbx(i) = Me.Controls.Add("Forms.TextBox.1", "TextBox" & i, Me)
txtbx(i).Caption = "Enter Date"
If i = 0 Then
txtbx(i).Top = 350
Else
txtbx(i).Top = txtbx(i - 1).Top + 350
End If
txtbx(i).Visible = True
i = i + 1
Next

End Sub

this is very basic version, not dynamic as plan, but my goal is to create 10
text fields in a row. but nothing happens...

If I can make this first piece of code work then I'll be able to duplicate
it for each of my fields.

Can you tell me what's wrong there?

thanks

Nico

Hi,

You can use the add method for controls. I paste the help below because it
took me quite some time to find it.

=====================================================================
Add Method

Adds or inserts a Tab or Page in a TabStrip or MultiPage, or adds a control
by its programmatic identifier (ProgID) to a page or form.

Syntax

For MultiPage, TabStrip
Set Object = object.Add( [ Name [, Caption [, index]]])

For other controls
Set Control = object.Add( ProgID [, Name [, Visible]])

The Add method syntax has these parts:

Part Description
object Required. A valid object name.
Name Optional. Specifies the name of the object being added. If a name
is not specified, the system generates a default name based on the rules of
the application where the form is used.
Caption Optional. Specifies the caption to appear on a tab or a
control. If a caption is not specified, the system generates a default
caption based on the rules of the application where the form is used.
index Optional. Identifies the position of a page or tab within a
Pages or Tabs collection. If an index is not specified, the system appends
the page or tab to the end of the Pages or Tabs collection and assigns the
appropriate index value.
ProgID Required. Programmatic identifier. A text string with no spaces
that identifies an object class. The standard syntax for a ProgID is
<Vendor>.<Component>.<Version>. A ProgID is mapped to a class identifier
(CLSID).
Visible Optional. True if the object is visible (default). False if
the object is hidden.

Settings

ProgID values for individual controls are:

CheckBox Forms.CheckBox.1
ComboBox Forms.ComboBox.1
CommandButton Forms.CommandButton.1
Frame Forms.Frame.1
Image Forms.Image.1
Label Forms.Label.1
ListBox Forms.ListBox.1
MultiPage Forms.MultiPage.1
OptionButton Forms.OptionButton.1
ScrollBar Forms.ScrollBar.1
SpinButton Forms.SpinButton.1
TabStrip Forms.TabStrip.1
TextBox Forms.TextBox.1
ToggleButton Forms.ToggleButton.1

Remarks

For a MultiPage control, the Add method returns a Page object. For a
TabStrip, it returns a Tab object. The index value for the first Page or Tab
of a collection is 0, the value for the second Page or Tab is 1, and so on.

For the Controls collection of an object, the Add method returns a control
corresponding to the specified ProgID. The AddControl event occurs after the
control is added.

You can add a control to a user form's Controls collection at design time,
but you must use the Designer property of the Microsoft Visual Basic for
Applications Extensibility Library to do so. The Designer property returns
the UserForm object.

The following syntax will return the Text property of the specified control:

userform1.thebox.text

If you add a control at run time, you must use the exclamation syntax to
reference properties of that control. For example, to return the Text
property of a control added at run time, use the following syntax:

userform1!thebox.text


Note
You can change a control's Name property at run time only if you added
that control at run time with the Add method.

=======================================================================
--
Jan De Messemaeker
Microsoft Project Most Valuable Professional
+32 495 300 620
For availability check:
http://users.online.be/prom-ade/Calendar.pdf

Hi all,

I have userform where users can input a variable which represents a
quantity
of "X" products. I want this variable to be used as an input for another
userform which will display the associated "X" number of text fields.

How can this second userform layout be automatically designed (position of
text fields, alignment, etc)?

I have no code at the moment (excpet for the variable storage in the first
userform) and am looking for a similar post where this subject has already
been treated.

Tanks for your help

Nicolas

 

[Jan De Messemaeker]

Hi,

I have never seen anything like this; if you get an example the better.
I am very curious about this and I want to crack the problem.
I have the feeling I'm terribly close but I have some urgent matters for
this evening (19:16 here now) so I'll have to come back tomorrow.

--
Jan De Messemaeker
Microsoft Project Most Valuable Professional
+32 495 300 620
For availability check:
http://users.online.be/prom-ade/Calendar.pdf

Jan thanks for your quick reply,

As I am pretty illiterate in programming I only adapt solutions already
existing to my specific needs, therefore I have trouble starting from
blank
code.

Here's what I came up with so far:
Option Explicit
Dim txtbx() As TextBox

Private Sub Form_Load()
Dim i As Integer


For i = 0 To i = 10
ReDim Preserve txtbx(i)
Set txtbx(i) = Me.Controls.Add("Forms.TextBox.1", "TextBox" & i, Me)
txtbx(i).Caption = "Enter Date"
If i = 0 Then
txtbx(i).Top = 350
Else
txtbx(i).Top = txtbx(i - 1).Top + 350
End If
txtbx(i).Visible = True
i = i + 1
Next

End Sub

this is very basic version, not dynamic as plan, but my goal is to create
10
text fields in a row. but nothing happens...

If I can make this first piece of code work then I'll be able to duplicate
it for each of my fields.

Can you tell me what's wrong there?

thanks

Nico

Hi,

You can use the add method for controls. I paste the help below because
it
took me quite some time to find it.


=====================================================================
Add Method

Adds or inserts a Tab or Page in a TabStrip or MultiPage, or adds a
control
by its programmatic identifier (ProgID) to a page or form.

Syntax

For MultiPage, TabStrip
Set Object = object.Add( [ Name [, Caption [, index]]])

For other controls
Set Control = object.Add( ProgID [, Name [, Visible]])

The Add method syntax has these parts:

Part Description
object Required. A valid object name.
Name Optional. Specifies the name of the object being added. If a
name
is not specified, the system generates a default name based on the rules
of
the application where the form is used.
Caption Optional. Specifies the caption to appear on a tab or a
control. If a caption is not specified, the system generates a default
caption based on the rules of the application where the form is used.
index Optional. Identifies the position of a page or tab within a
Pages or Tabs collection. If an index is not specified, the system
appends
the page or tab to the end of the Pages or Tabs collection and assigns
the
appropriate index value.
ProgID Required. Programmatic identifier. A text string with no
spaces
that identifies an object class. The standard syntax for a ProgID is
<Vendor>.<Component>.<Version>. A ProgID is mapped to a class identifier
(CLSID).
Visible Optional. True if the object is visible (default). False if
the object is hidden.

Settings

ProgID values for individual controls are:

CheckBox Forms.CheckBox.1
ComboBox Forms.ComboBox.1
CommandButton Forms.CommandButton.1
Frame Forms.Frame.1
Image Forms.Image.1
Label Forms.Label.1
ListBox Forms.ListBox.1
MultiPage Forms.MultiPage.1
OptionButton Forms.OptionButton.1
ScrollBar Forms.ScrollBar.1
SpinButton Forms.SpinButton.1
TabStrip Forms.TabStrip.1
TextBox Forms.TextBox.1
ToggleButton Forms.ToggleButton.1

Remarks

For a MultiPage control, the Add method returns a Page object. For a
TabStrip, it returns a Tab object. The index value for the first Page or
Tab
of a collection is 0, the value for the second Page or Tab is 1, and so
on.

For the Controls collection of an object, the Add method returns a
control
corresponding to the specified ProgID. The AddControl event occurs after
the
control is added.

You can add a control to a user form's Controls collection at design
time,
but you must use the Designer property of the Microsoft Visual Basic for
Applications Extensibility Library to do so. The Designer property
returns
the UserForm object.

The following syntax will return the Text property of the specified
control:

userform1.thebox.text

If you add a control at run time, you must use the exclamation syntax to
reference properties of that control. For example, to return the Text
property of a control added at run time, use the following syntax:

userform1!thebox.text


Note
You can change a control's Name property at run time only if you
added
that control at run time with the Add method.

=======================================================================
--
Jan De Messemaeker
Microsoft Project Most Valuable Professional
+32 495 300 620
For availability check:
http://users.online.be/prom-ade/Calendar.pdf

Hi all,

I have userform where users can input a variable which represents a
quantity
of "X" products. I want this variable to be used as an input for
another
userform which will display the associated "X" number of text fields.

How can this second userform layout be automatically designed (position
of
text fields, alignment, etc)?

I have no code at the moment (excpet for the variable storage in the
first
userform) and am looking for a similar post where this subject has
already
been treated.

Tanks for your help

Nicolas

 

[NicoCaps]

Hi Jan,

here's what I came up with so far:

On my first form I enter the values and save them, I have two different
techniques available to save them :
Private Sub btnV_Click()
If txtBoxNbCV.Value < 1 Then MsgBox "La valeur minimum est 1",
vbExclamation, "": txtBoxNbCV.Value = 1
SaveSetting "MyApp", "TxtBoxs", "NbTRANSFORMER", txtBoxNbTR
SaveSetting "MyApp", "TxtBoxs", "NbCONVERTISSEUR", txtBoxNbCV
SaveSetting "MyApp", "TxtBoxs", "NbAIRCOOLER", txtBoxNbAC
SaveSetting "MyApp", "TxtBoxs", "NbMOTORS", txtBoxNbMO
SaveSetting "MyApp", "TxtBoxs", "NbAUXILIARYEQUIPMENT", txtBoxNbAUX
SaveSetting "MyApp", "TxtBoxs", "NbSWITCHGEAR", txtBoxNbSW
SaveSetting "MyApp", "TxtBoxs", "NbCONTROLCUBICLE ", txtBoxNbCC
SaveSetting "MyApp", "TxtBoxs", "NbUPS", txtBoxNbUPS
SaveSetting "MyApp", "TxtBoxs", "NbMCC", txtBoxNbMCC
SaveSetting "MyApp", "TxtBoxs", "NbCONTAINER", txtBoxNbCTN
Unload Me
End Sub
Private Sub Save_Click()

Dim Ctl As Control

For Each Ctl In Me.Controls

If (TypeOf Ctl Is TextBox) Or (TypeOf Ctl Is ComboBox) Then
SaveSetting NmApp, Ctr, CStr(Ctl.TabIndex), Ctl.Text


Next
UserForm5.Show
End Sub

then, this is where I am stuck....

I'd like userform5 to load those values and create dinamically as many
textfields as needed (sort of like a table where a row appears only if the
value is different from 0 and the number of columns depends on the value
entered). Heres a code that works but I haven't started adapting to my need,
not even managed to load the saved data in my registry...

Option Explicit

Dim TexteBox() As New GrTextBox
Dim Nb As Integer 'nb total de textboxs
Dim nbE As Integer, nbP As Integer 'nb de lignes et de colonnes de textboxs
Dim Champ As Range 'champ de cellules de travail
'
'conversion point/mm
Const PointParMm As Single = 2.86 '(1 point=1/72 pouce et 1 pouce=25,4 mm)
Private Sub btnV_Click()
'écrit sur feuille les valeurs des textboxs
Dim monTab(), i As Integer, j As Integer, n As Integer
'on va recueillir les valeurs des textboxs dans un tableau, ainsi que les
titres
ReDim monTab(nbE, nbP)
n = 1
For i = 0 To nbE
For j = 0 To nbP
If i = 0 Then
If j <> 0 Then monTab(i, j) = "Pièce n° " & j
Else
If j = 0 Then
monTab(i, j) = "Échantillon n° " & i
Else
monTab(i, j) = TexteBox(n).Valeur 'la propriété "Valeur" à
été définie dans le module de classe GrTextBox
n = n + 1
End If
End If
Next
Next
'on copie le tableau dans la feuille et on remet le titre principal
With ActiveSheet
.Range("Tableau").CurrentRegion.ClearContents
.Range("Tableau").Resize(nbE + 1, nbP + 1).Value = monTab
.Range("Pièces_Échantillons").Copy Destination:=Range("Tableau")
.Range("Tableau").CurrentRegion.Resize(1, nbP + 1).Font.Bold = True
.Range("Tableau").CurrentRegion.Resize(nbE + 1, 1).Font.Bold = True
End With
End Sub

thanks for your help I am currently working on checking if wether or not I
save correctly those values entered in the first userform...

Nico

 

[Jan De Messemaeker]

Hi,

Here's an approach that has the only merit that it works.
It's not necessarily the most elegant
1. In a normal module, declare
Public CTRL As Control
Public CtrlCtr As Integer
(Public declarations on top of the module code)
Then this sub
Sub AddMore()
Dim Ctr As Integer
CtrlCtr = 2
Load TestForm
TestForm.Show
For Ctr = 1 To CtrlCtr
Debug.Print TestForm.Controls(Ctr).Value
Next Ctr
Unload TestForm
End Sub

(Obviously CtrlCtr is the number you will get from your other form, 2 is
only there for testing)
Instead of debug.print you can insert whatever data handling you need

Now create the form (in my example TestForm) with already One control there,
an OK button with as asociated code only testform.hide

And put the following in the form's code

Private Sub UserForm_Initialize()
Dim Ct As Integer
Dim Nm As String
For Ct = 1 To CtrlCtr
Nm = "CTRL" & CStr(Ct)
Set CTRL = TestForm.Controls.Add(bstrprogid:="Forms.TextBox.1", Name:=Nm,
Visible:=True)
CTRL.Top = 100 * (Ct - 0.5)
CTRL.Left = 100
CTRL.Enabled = True
Next Ct
End Sub

That works, so you can probaly adapt it as per your needs.

Note. I personally would never have taken this route. I would have created a
form with the maximum need of text boxed and I would have made the ones not
necessary invisible.

Hope this helps,

--
Jan De Messemaeker
Microsoft Project Most Valuable Professional
+32 495 300 620
For availability check:
http://users.online.be/prom-ade/Calendar.pdf

Jan thanks for your quick reply,


As I am pretty illiterate in programming I only adapt solutions already
existing to my specific needs, therefore I have trouble starting from
blank
code.

Here's what I came up with so far:
Option Explicit
Dim txtbx() As TextBox

Private Sub Form_Load()
Dim i As Integer


For i = 0 To i = 10
ReDim Preserve txtbx(i)
Set txtbx(i) = Me.Controls.Add("Forms.TextBox.1", "TextBox" & i, Me)
txtbx(i).Caption = "Enter Date"
If i = 0 Then
txtbx(i).Top = 350
Else
txtbx(i).Top = txtbx(i - 1).Top + 350
End If
txtbx(i).Visible = True
i = i + 1
Next

End Sub

this is very basic version, not dynamic as plan, but my goal is to create
10
text fields in a row. but nothing happens...

If I can make this first piece of code work then I'll be able to duplicate
it for each of my fields.

Can you tell me what's wrong there?

thanks

Nico

Hi,

You can use the add method for controls. I paste the help below because
it
took me quite some time to find it.


=====================================================================
Add Method

Adds or inserts a Tab or Page in a TabStrip or MultiPage, or adds a
control
by its programmatic identifier (ProgID) to a page or form.

Syntax

For MultiPage, TabStrip
Set Object = object.Add( [ Name [, Caption [, index]]])

For other controls
Set Control = object.Add( ProgID [, Name [, Visible]])

The Add method syntax has these parts:

Part Description
object Required. A valid object name.
Name Optional. Specifies the name of the object being added. If a
name
is not specified, the system generates a default name based on the rules
of
the application where the form is used.
Caption Optional. Specifies the caption to appear on a tab or a
control. If a caption is not specified, the system generates a default
caption based on the rules of the application where the form is used.
index Optional. Identifies the position of a page or tab within a
Pages or Tabs collection. If an index is not specified, the system
appends
the page or tab to the end of the Pages or Tabs collection and assigns
the
appropriate index value.
ProgID Required. Programmatic identifier. A text string with no
spaces
that identifies an object class. The standard syntax for a ProgID is
<Vendor>.<Component>.<Version>. A ProgID is mapped to a class identifier
(CLSID).
Visible Optional. True if the object is visible (default). False if
the object is hidden.

Settings

ProgID values for individual controls are:

CheckBox Forms.CheckBox.1
ComboBox Forms.ComboBox.1
CommandButton Forms.CommandButton.1
Frame Forms.Frame.1
Image Forms.Image.1
Label Forms.Label.1
ListBox Forms.ListBox.1
MultiPage Forms.MultiPage.1
OptionButton Forms.OptionButton.1
ScrollBar Forms.ScrollBar.1
SpinButton Forms.SpinButton.1
TabStrip Forms.TabStrip.1
TextBox Forms.TextBox.1
ToggleButton Forms.ToggleButton.1

Remarks

For a MultiPage control, the Add method returns a Page object. For a
TabStrip, it returns a Tab object. The index value for the first Page or
Tab
of a collection is 0, the value for the second Page or Tab is 1, and so
on.

For the Controls collection of an object, the Add method returns a
control
corresponding to the specified ProgID. The AddControl event occurs after
the
control is added.

You can add a control to a user form's Controls collection at design
time,
but you must use the Designer property of the Microsoft Visual Basic for
Applications Extensibility Library to do so. The Designer property
returns
the UserForm object.

The following syntax will return the Text property of the specified
control:

userform1.thebox.text

If you add a control at run time, you must use the exclamation syntax to
reference properties of that control. For example, to return the Text
property of a control added at run time, use the following syntax:

userform1!thebox.text


Note
You can change a control's Name property at run time only if you
added
that control at run time with the Add method.

=======================================================================
--
Jan De Messemaeker
Microsoft Project Most Valuable Professional
+32 495 300 620
For availability check:
http://users.online.be/prom-ade/Calendar.pdf

Hi all,

I have userform where users can input a variable which represents a
quantity
of "X" products. I want this variable to be used as an input for
another
userform which will display the associated "X" number of text fields.

How can this second userform layout be automatically designed (position
of
text fields, alignment, etc)?

I have no code at the moment (excpet for the variable storage in the
first
userform) and am looking for a similar post where this subject has
already
been treated.

Tanks for your help

Nicolas

 

[NicoCaps]

Hi again,

thanks for your quick reply, I amtrying it out right now.

Well I chose this route blindedly, as I have just basic programming
knowledge I didn't think of making invisible or write unabled the text box
valued as 0 on my first userform.

Let's say that those textboxes will be maximum 10 rows and 4 columns.

How can I get something running that will get the unnecessary textboxes
invisible?

thanks again for your time, and patience!

 

[Jan De Messemaeker]

Hi,

For instance

TB24.visible=false

--
Jan De Messemaeker
Microsoft Project Most Valuable Professional
+32 495 300 620
For availability check:
http://users.online.be/prom-ade/Calendar.pdf

 

[NicoCaps]

OK got your preceding code working.

I guess I'll try out making the txtbox invisible depending on the quantity
given in my first form using if loops.

Thanks