When I add controls to an array in a For Each loop can I be sure that the
order of these controls in the array is
always the same?

They will be added like for example this:

Sub AddTextBoxesToArray(oForm As UserForm, lStart)

Dim ctl As MSForms.Control
Dim i As Long

i = lStart

For Each ctl In oForm.Controls
If TypeName(ctl) = "TextBox" Then
Set TextBoxes(i).objTextBox = ctl
i = i + 1
End If
Next

End Sub

I can be sure the start element is always the same as this is a Public
constant, but how about the rest?


RBS

Hi Bart,

I don't quite follow what you are asking, however the controls index order
is always the same as the order controls were added to the form at design
stage, continuing with any that might have been added at runtime, also in
the order they were subsequently added.

The order cannot be changed, and not related to tab order (which can be
changed).

Like you, I add controls to withevents classes. However I know the order
specific controls were added and ensure I add batches of controls to the
form that I'm going to class sequentially. So I loop form say i = 11 to 20,
knowing already exactly what these 10 controls are.

The problem though is during design if I later want to include an extra
control, the entire form needs rebuilding with controls added in correct and
known order. That's why I build my form from a table in cells with
"Designer". The same little app gets all the constants I use in loops to
identify index locations.

Not sure if this encompasses your question.

Regards,
Peter T


"RB Smissaert" wrote in message
...
When I add controls to an array in a For Each loop can I be sure that the
order of these controls in the array is
always the same?

They will be added like for example this:

Sub AddTextBoxesToArray(oForm As UserForm, lStart)

Dim ctl As MSForms.Control
Dim i As Long

i = lStart

For Each ctl In oForm.Controls
If TypeName(ctl) = "TextBox" Then
Set TextBoxes(i).objTextBox = ctl
i = i + 1
End If
Next

End Sub

I can be sure the start element is always the same as this is a Public
constant, but how about the rest?


RBS

Hi Peter,


Yes, thanks. That is what I thought and that is what I need.
I just needed to be sure that this order would always be the same unless new
controls were added.
I think I have this all worked out now, so now have every control of every
form added to either
a control array or a control collection and I have can repond to all the
needed events (mainly a mouse
right-click) of all these controls.
Had some trouble with the fact that the Mouse-Down event fired twice, but
solved this by just using
the Mouse-Up event.
Your tip about Property Let came in very handy as the classes now know the
index of the control.

This is the Sub that adds all the controls to the arrays and the one
collection:

Sub AddControlsToArray(oForm As UserForm, _
lStart As Long, _
strControlType As String, _
Optional bDestroy As Boolean = False)

Dim ctl As MSForms.Control
Dim i As Long

i = lStart

'not sure this is needed anymore
On Error Resume Next

Select Case strControlType
Case "Label"
If bDestroy Then
For Each ctl In oForm.Controls
If TypeName(ctl) = strControlType Then
Set Labels(i) = Nothing
i = i + 1
End If
Next
Else
For Each ctl In oForm.Controls
If TypeName(ctl) = strControlType Then
Set Labels(i) = New LabelClass
Set Labels(i).objLabel = ctl
i = i + 1
End If
Next
End If
Case "CheckBox"
If bDestroy Then
For Each ctl In oForm.Controls
If TypeName(ctl) = strControlType Then
Set CheckBoxes(i) = Nothing
i = i + 1
End If
Next
Else
For Each ctl In oForm.Controls
If TypeName(ctl) = strControlType Then
Set CheckBoxes(i) = New CheckBoxClass
Set CheckBoxes(i).objCheckBox = ctl
i = i + 1
End If
Next
End If
Case "OptionButton"
If bDestroy Then
For Each ctl In oForm.Controls
If TypeName(ctl) = strControlType Then
Set OptionButtons(i) = Nothing
i = i + 1
End If
Next
Else
For Each ctl In oForm.Controls
If TypeName(ctl) = strControlType Then
Set OptionButtons(i) = New OptionButtonClass
Set OptionButtons(i).objOptionButton = ctl
i = i + 1
End If
Next
End If
Case "Frame"
If bDestroy Then
For Each ctl In oForm.Controls
If TypeName(ctl) = strControlType Then
Set Frames(i) = Nothing
i = i + 1
End If
Next
Else
For Each ctl In oForm.Controls
If TypeName(ctl) = strControlType Then
Set Frames(i) = New FrameClass
Set Frames(i).objFrame = ctl
i = i + 1
End If
Next
End If
Case "TextBox"
If bDestroy Then
For Each ctl In oForm.Controls
If TypeName(ctl) = strControlType Then
Set TextBoxes(i) = Nothing
i = i + 1
End If
Next
Else
For Each ctl In oForm.Controls
If TypeName(ctl) = strControlType Then
Set TextBoxes(i) = New TextboxClass
Set TextBoxes(i).objTextBox = ctl
TextBoxes(i).propIndex = i
i = i + 1
End If
Next
End If
Case "CommandButton"
If objButtons Is Nothing Then
Set objButtons = New ButtonsClass
End If
For Each ctl In oForm.Controls
If TypeName(ctl) = strControlType Then
objButtons.Add ctl
End If
Next
End Select

End Sub


RBS



"Peter T" <peter_t@discussions wrote in message
...
Hi Bart,

I don't quite follow what you are asking, however the controls index order
is always the same as the order controls were added to the form at design
stage, continuing with any that might have been added at runtime, also in
the order they were subsequently added.

The order cannot be changed, and not related to tab order (which can be
changed).

Like you, I add controls to withevents classes. However I know the order
specific controls were added and ensure I add batches of controls to the
form that I'm going to class sequentially. So I loop form say i = 11 to
20,
knowing already exactly what these 10 controls are.

The problem though is during design if I later want to include an extra
control, the entire form needs rebuilding with controls added in correct
and
known order. That's why I build my form from a table in cells with
"Designer". The same little app gets all the constants I use in loops to
identify index locations.

Not sure if this encompasses your question.

Regards,
Peter T


"RB Smissaert" wrote in message
...
When I add controls to an array in a For Each loop can I be sure that the
order of these controls in the array is
always the same?

They will be added like for example this:

Sub AddTextBoxesToArray(oForm As UserForm, lStart)

Dim ctl As MSForms.Control
Dim i As Long

i = lStart

For Each ctl In oForm.Controls
If TypeName(ctl) = "TextBox" Then
Set TextBoxes(i).objTextBox = ctl
i = i + 1
End If
Next

End Sub

I can be sure the start element is always the same as this is a Public
constant, but how about the rest?


RBS

Hi Bart,

I also set (ie let) the index or id of controls as a property in each class.
Took me ages to figure that simple thing. Prior to that I used to set id's
in control tags with which to identify the control in the class.

To clarify what I said about the control index order never changing,
obviously if you delete controls "later" control indexes would reduce (if
indeed you do that).

Just a couple of comments in a quick glance of your code, second guessing
what else you have -

I assume your arrays & collection are not public in a normal module but in
your form module (unlike the Forms array you had in your nearby post).
Otherwise I can see confusion in the index orders if using the same arrays
with your multiple forms.

If you are destroying the entire array or collection of class's you don't
need to loop through each, simply

Erase myArrayOfControlClass
Set myControlCollection = Nothing

You can confirm for yourself this works by putting in a temporary terminate
event in the class and debugging. The erase array line would error though if
the array elements have not been initialized.

'not sure this is needed anymore
On Error Resume Next

At a glance nothing should error, but if it does would suggest something
fundamentally wrong. I also use "On Error Resume Next", too much, has been
known to mask my bad code!

Re the Case "CommandButton" routine - I assume objButtons is a collection
(objButtons.Add ctl) but I can't equate that with "Set objButtons = New
ButtonsClass", or follow how all this works, but I assume it does!

To be really pedantic, some suggest not to use reserved names like
TextBoxes, CheckBoxes, Labels etc. for variables, even though it doesn't
confuse vba.

Glad you've got it all working.

Regards,
Peter T


"RB Smissaert" wrote in message
...
Hi Peter,


Yes, thanks. That is what I thought and that is what I need.
I just needed to be sure that this order would always be the same unless
new
controls were added.
I think I have this all worked out now, so now have every control of every
form added to either
a control array or a control collection and I have can repond to all the
needed events (mainly a mouse
right-click) of all these controls.
Had some trouble with the fact that the Mouse-Down event fired twice, but
solved this by just using
the Mouse-Up event.
Your tip about Property Let came in very handy as the classes now know the
index of the control.

This is the Sub that adds all the controls to the arrays and the one
collection:

Sub AddControlsToArray(oForm As UserForm, _
lStart As Long, _
strControlType As String, _
Optional bDestroy As Boolean = False)

Dim ctl As MSForms.Control
Dim i As Long

i = lStart

'not sure this is needed anymore
On Error Resume Next

Select Case strControlType
Case "Label"
If bDestroy Then
For Each ctl In oForm.Controls
If TypeName(ctl) = strControlType Then
Set Labels(i) = Nothing
i = i + 1
End If
Next
Else
For Each ctl In oForm.Controls
If TypeName(ctl) = strControlType Then
Set Labels(i) = New LabelClass
Set Labels(i).objLabel = ctl
i = i + 1
End If
Next
End If
Case "CheckBox"
If bDestroy Then
For Each ctl In oForm.Controls
If TypeName(ctl) = strControlType Then
Set CheckBoxes(i) = Nothing
i = i + 1
End If
Next
Else
For Each ctl In oForm.Controls
If TypeName(ctl) = strControlType Then
Set CheckBoxes(i) = New CheckBoxClass
Set CheckBoxes(i).objCheckBox = ctl
i = i + 1
End If
Next
End If
Case "OptionButton"
If bDestroy Then
For Each ctl In oForm.Controls
If TypeName(ctl) = strControlType Then
Set OptionButtons(i) = Nothing
i = i + 1
End If
Next
Else
For Each ctl In oForm.Controls
If TypeName(ctl) = strControlType Then
Set OptionButtons(i) = New OptionButtonClass
Set OptionButtons(i).objOptionButton = ctl
i = i + 1
End If
Next
End If
Case "Frame"
If bDestroy Then
For Each ctl In oForm.Controls
If TypeName(ctl) = strControlType Then
Set Frames(i) = Nothing
i = i + 1
End If
Next
Else
For Each ctl In oForm.Controls
If TypeName(ctl) = strControlType Then
Set Frames(i) = New FrameClass
Set Frames(i).objFrame = ctl
i = i + 1
End If
Next
End If
Case "TextBox"
If bDestroy Then
For Each ctl In oForm.Controls
If TypeName(ctl) = strControlType Then
Set TextBoxes(i) = Nothing
i = i + 1
End If
Next
Else
For Each ctl In oForm.Controls
If TypeName(ctl) = strControlType Then
Set TextBoxes(i) = New TextboxClass
Set TextBoxes(i).objTextBox = ctl
TextBoxes(i).propIndex = i
i = i + 1
End If
Next
End If
Case "CommandButton"
If objButtons Is Nothing Then
Set objButtons = New ButtonsClass
End If
For Each ctl In oForm.Controls
If TypeName(ctl) = strControlType Then
objButtons.Add ctl
End If
Next
End Select

End Sub


RBS



"Peter T" <peter_t@discussions wrote in message
...
Hi Bart,

I don't quite follow what you are asking, however the controls index
order
is always the same as the order controls were added to the form at
design
stage, continuing with any that might have been added at runtime, also
in
the order they were subsequently added.

The order cannot be changed, and not related to tab order (which can be
changed).

Like you, I add controls to withevents classes. However I know the order
specific controls were added and ensure I add batches of controls to the
form that I'm going to class sequentially. So I loop form say i = 11 to
20,
knowing already exactly what these 10 controls are.

The problem though is during design if I later want to include an extra
control, the entire form needs rebuilding with controls added in correct
and
known order. That's why I build my form from a table in cells with
"Designer". The same little app gets all the constants I use in loops to
identify index locations.

Not sure if this encompasses your question.

Regards,
Peter T


"RB Smissaert" wrote in message
...
When I add controls to an array in a For Each loop can I be sure that
the
order of these controls in the array is
always the same?

They will be added like for example this:

Sub AddTextBoxesToArray(oForm As UserForm, lStart)

Dim ctl As MSForms.Control
Dim i As Long

i = lStart

For Each ctl In oForm.Controls
If TypeName(ctl) = "TextBox" Then
Set TextBoxes(i).objTextBox = ctl
i = i + 1
End If
Next

End Sub

I can be sure the start element is always the same as this is a Public
constant, but how about the rest?


RBS

Hi Peter,


I assume your arrays & collection are not public in a normal module

They are and I think they will have to be as one array hold the particular
controls of all the forms.
It is getting a bit complex and maybe I am overlooking something here.

I assume objButtons is a collection

Yes, it is.
I have 2 classes, called ButtonClass and ButtonClass.
Somehow I thought I couldn't have the same construction (an array holding
all the controls and only one class) for the buttons as for the other
controls. The buttons do some more complex things such as being hightlighted
when the
mouse over them. Maybe though I could do away with the 2 classes here and
just have the one class, just as for the other controls.
On the other hand a collection makes matters easier as you don't have to
worry about where controls go, that is what
element of the array.
Also, and I am not sure now why this is, I don't have to destroy controls in
the collection when a particular form is unloaded. Or to say it more
precisely, when I tried in the terminate event, I couldn't.

To be really pedantic, some suggest not to use reserved names like
TextBoxes, CheckBoxes etc.

I did think about this, but it works fine. Still, perhaps better to change
it.

Thanks again for your suggestions, particularly the one about Property Let.
I suppose there is an enormous amount
of simplification I could do in my app when I made it more object
orientated, but there are always other things to
sort out, such as at the moment the help file.


RBS



"Peter T" <peter_t@discussions wrote in message
...
Hi Bart,

I also set (ie let) the index or id of controls as a property in each
class.
Took me ages to figure that simple thing. Prior to that I used to set id's
in control tags with which to identify the control in the class.

To clarify what I said about the control index order never changing,
obviously if you delete controls "later" control indexes would reduce (if
indeed you do that).

Just a couple of comments in a quick glance of your code, second guessing
what else you have -

I assume your arrays & collection are not public in a normal module but in
your form module (unlike the Forms array you had in your nearby post).
Otherwise I can see confusion in the index orders if using the same arrays
with your multiple forms.

If you are destroying the entire array or collection of class's you don't
need to loop through each, simply

Erase myArrayOfControlClass
Set myControlCollection = Nothing

You can confirm for yourself this works by putting in a temporary
terminate
event in the class and debugging. The erase array line would error though
if
the array elements have not been initialized.

'not sure this is needed anymore
On Error Resume Next

At a glance nothing should error, but if it does would suggest something
fundamentally wrong. I also use "On Error Resume Next", too much, has been
known to mask my bad code!

Re the Case "CommandButton" routine - I assume objButtons is a collection
(objButtons.Add ctl) but I can't equate that with "Set objButtons = New
ButtonsClass", or follow how all this works, but I assume it does!

To be really pedantic, some suggest not to use reserved names like
TextBoxes, CheckBoxes, Labels etc. for variables, even though it doesn't
confuse vba.

Glad you've got it all working.

Regards,
Peter T


"RB Smissaert" wrote in message
...
Hi Peter,


Yes, thanks. That is what I thought and that is what I need.
I just needed to be sure that this order would always be the same unless
new
controls were added.
I think I have this all worked out now, so now have every control of
every
form added to either
a control array or a control collection and I have can repond to all the
needed events (mainly a mouse
right-click) of all these controls.
Had some trouble with the fact that the Mouse-Down event fired twice, but
solved this by just using
the Mouse-Up event.
Your tip about Property Let came in very handy as the classes now know
the
index of the control.

This is the Sub that adds all the controls to the arrays and the one
collection:

Sub AddControlsToArray(oForm As UserForm, _
lStart As Long, _
strControlType As String, _
Optional bDestroy As Boolean = False)

Dim ctl As MSForms.Control
Dim i As Long

i = lStart

'not sure this is needed anymore
On Error Resume Next

Select Case strControlType
Case "Label"
If bDestroy Then
For Each ctl In oForm.Controls
If TypeName(ctl) = strControlType Then
Set Labels(i) = Nothing
i = i + 1
End If
Next
Else
For Each ctl In oForm.Controls
If TypeName(ctl) = strControlType Then
Set Labels(i) = New LabelClass
Set Labels(i).objLabel = ctl
i = i + 1
End If
Next
End If
Case "CheckBox"
If bDestroy Then
For Each ctl In oForm.Controls
If TypeName(ctl) = strControlType Then
Set CheckBoxes(i) = Nothing
i = i + 1
End If
Next
Else
For Each ctl In oForm.Controls
If TypeName(ctl) = strControlType Then
Set CheckBoxes(i) = New CheckBoxClass
Set CheckBoxes(i).objCheckBox = ctl
i = i + 1
End If
Next
End If
Case "OptionButton"
If bDestroy Then
For Each ctl In oForm.Controls
If TypeName(ctl) = strControlType Then
Set OptionButtons(i) = Nothing
i = i + 1
End If
Next
Else
For Each ctl In oForm.Controls
If TypeName(ctl) = strControlType Then
Set OptionButtons(i) = New OptionButtonClass
Set OptionButtons(i).objOptionButton = ctl
i = i + 1
End If
Next
End If
Case "Frame"
If bDestroy Then
For Each ctl In oForm.Controls
If TypeName(ctl) = strControlType Then
Set Frames(i) = Nothing
i = i + 1
End If
Next
Else
For Each ctl In oForm.Controls
If TypeName(ctl) = strControlType Then
Set Frames(i) = New FrameClass
Set Frames(i).objFrame = ctl
i = i + 1
End If
Next
End If
Case "TextBox"
If bDestroy Then
For Each ctl In oForm.Controls
If TypeName(ctl) = strControlType Then
Set TextBoxes(i) = Nothing
i = i + 1
End If
Next
Else
For Each ctl In oForm.Controls
If TypeName(ctl) = strControlType Then
Set TextBoxes(i) = New TextboxClass
Set TextBoxes(i).objTextBox = ctl
TextBoxes(i).propIndex = i
i = i + 1
End If
Next
End If
Case "CommandButton"
If objButtons Is Nothing Then
Set objButtons = New ButtonsClass
End If
For Each ctl In oForm.Controls
If TypeName(ctl) = strControlType Then
objButtons.Add ctl
End If
Next
End Select

End Sub


RBS



"Peter T" <peter_t@discussions wrote in message
...
Hi Bart,

I don't quite follow what you are asking, however the controls index
order
is always the same as the order controls were added to the form at
design
stage, continuing with any that might have been added at runtime, also
in
the order they were subsequently added.

The order cannot be changed, and not related to tab order (which can be
changed).

Like you, I add controls to withevents classes. However I know the
order
specific controls were added and ensure I add batches of controls to
the
form that I'm going to class sequentially. So I loop form say i = 11 to
20,
knowing already exactly what these 10 controls are.

The problem though is during design if I later want to include an extra
control, the entire form needs rebuilding with controls added in
correct
and
known order. That's why I build my form from a table in cells with
"Designer". The same little app gets all the constants I use in loops
to
identify index locations.

Not sure if this encompasses your question.

Regards,
Peter T


"RB Smissaert" wrote in message
...
When I add controls to an array in a For Each loop can I be sure that
the
order of these controls in the array is
always the same?

They will be added like for example this:

Sub AddTextBoxesToArray(oForm As UserForm, lStart)

Dim ctl As MSForms.Control
Dim i As Long

i = lStart

For Each ctl In oForm.Controls
If TypeName(ctl) = "TextBox" Then
Set TextBoxes(i).objTextBox = ctl
i = i + 1
End If
Next

End Sub

I can be sure the start element is always the same as this is a Public
constant, but how about the rest?


RBS