Control Order on User Form
 

I have created a UserForm with approx. 30 check boxes on
it. Each checkbox represents s page to be printed. I have
created VBA code to cycle through each checkbox control
and print the pages where the boxes are checked.

For Each ctrl In PrintControlForm.Controls
If TypeOf ctrl Is MSForms.CheckBox Then
If ctrl.Object.Value = True Then

Of course I would like to control the order in which the
pages are printed. I assumed the TabIndex Property would
be the key, but that does not seem to work. I am close but
am not sure what is driving the order at this time.

Allan

Control Order on User Form
 

I think that as you are accessing each member of the control collection
you are doing this in the same order as the control index number.
This is set at the time that the controls are added to the form,
starting at one, and incrementing for each new control.

I think it is possible to construct code to reorder the controls within
the collection, however it probably will require you to delete and
recreate the controls. That sounds like a lot of work.

It may be easier to test the value of each control in your desired
sequence than to reorder.


---
Message posted from http://www.ExcelForum.com/

Control Order on User Form
 

I've had the same problem and i done something like that:

Private Sub CommandButton1_Click()
Dim i As Long, j As Long
Dim ctr As Control

On Error Resume Next

For i = 1 To 30
Set ctr = PrintControlForm.Controls("CheckBox" & i)
If ctr.Value Then MsgBox i
Next i

Set ctr = Nothing

Unload Me
End Sub



-----Original Message-----
I have created a UserForm with approx. 30 check boxes on
it. Each checkbox represents s page to be printed. I have
created VBA code to cycle through each checkbox control
and print the pages where the boxes are checked.

For Each ctrl In PrintControlForm.Controls
If TypeOf ctrl Is MSForms.CheckBox Then
If ctrl.Object.Value = True Then

Of course I would like to control the order in which the
pages are printed. I assumed the TabIndex Property would
be the key, but that does not seem to work. I am close
but
am not sure what is driving the order at this time.

Allan
.

Control Order on User Form
 

Allan,

Excel stores the conrols contained in
a UserForm in a random order that has
nothing to do with their alphanumeric
name or Tabindex. The trick is therefore
to assign them names CheckBox01 through CheckBox50, reflecting the order
you would like to print them out in. Then
to print them to sequential file#1, say, do the following:

Dim K as Variant
For K = 1 to 50
Print#1, PrintControlForm.Controls("CheckBox" & Format(K, "00").Value
Next

You can use the above method for
printing the contents of any collection of same-type controls, by replacing,
for example, "CheckBox" by "TextBox" and remembering to change .Value to .Text.
The method works like a charm.

-- Dennis Eisen

Control Order on User Form
 

Thanks All

I opted to deleted and re-create each of the checkboxes on
the form. I did this mostly because the rest of the code
was already written and was contingent upon a certain
naming convention for the chckboxes.

I completed the re-creation and tested it and it worked
fine. It would seem that Microsoft would use the TabIndex
Property rather than the Control Index Number to allow for
control by the developer.

Allan
-----Original Message-----
I think that as you are accessing each member of the
control collection
you are doing this in the same order as the control index
number.
This is set at the time that the controls are added to
the form,
starting at one, and incrementing for each new control.

I think it is possible to construct code to reorder the
controls within
the collection, however it probably will require you to
delete and
recreate the controls. That sounds like a lot of work.

It may be easier to test the value of each control in
your desired
sequence than to reorder.


---
Message posted from http://www.ExcelForum.com/

.