I have a matrix of 400 commandbuttons (20 by 20) on a Userform and wish
to loop through them, -not horizontally-, but vertically viz.
1,21,31...391, 2, 22, 32...392 through 380,390,400. Since the For each
.... Next construct sweeps horizontally, this popular loop fails. I have
attempted, to no avail, to coerce Excel using conventional For..Next
loop with 20 increments as in:

num=1
For n = 1 to 400 Step 20
Controls("CommandButton" & n).Caption = num
num = num +1
If n\400=4 Then n = (n-400)+1
Next

The above code is merely demonstrative as it only assign captions. The
real object is to get a handle to do the vertical cosine-curve looping.
Any ideas?

Thanks

David


--
davidm
------------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645
View this thread: http://www.excelforum.com/showthread...hreadid=505995

Hi David,
try...

For J = 1 To 20
For I = 1 To 400 Step 20
Controls("CommandButton" & n).Caption = I + J - 1
Next I
Next J
Ken Johnson

Sorry, make that...

For J = 1 To 20
For I = 1 To 400 Step 20
Controls("CommandButton" & I + J -1).Caption = I + J - 1
Next I
Next J

Hope this idea works

For a = 1 To 20
For b = a To 400 Step 20
'place your code here
Next
Next

I think...

Option Explicit
Sub testme02()
Dim iCtr As Long
Dim jCtr As Long
Dim n As Long

For iCtr = 0 To 19
For jCtr = 1 To 400 Step 20
n = iCtr + jCtr
Debug.Print Format(n, "000") & "-";
'Controls("CommandButton" & n).Caption = n
Next jCtr
Debug.Print
Next iCtr
End Sub

I used Debug.print and ended looping in this order (across, then down):

001-021-041-061-081-101-121-141-161-181-201-221-241-261-281-301-321-341-361-381
002-022-042-062-082-102-122-142-162-182-202-222-242-262-282-302-322-342-362-382
003-023-043-063-083-103-123-143-163-183-203-223-243-263-283-303-323-343-363-383
004-024-044-064-084-104-124-144-164-184-204-224-244-264-284-304-324-344-364-384
005-025-045-065-085-105-125-145-165-185-205-225-245-265-285-305-325-345-365-385
006-026-046-066-086-106-126-146-166-186-206-226-246-266-286-306-326-346-366-386
007-027-047-067-087-107-127-147-167-187-207-227-247-267-287-307-327-347-367-387
008-028-048-068-088-108-128-148-168-188-208-228-248-268-288-308-328-348-368-388
009-029-049-069-089-109-129-149-169-189-209-229-249-269-289-309-329-349-369-389
010-030-050-070-090-110-130-150-170-190-210-230-250-270-290-310-330-350-370-390
011-031-051-071-091-111-131-151-171-191-211-231-251-271-291-311-331-351-371-391
012-032-052-072-092-112-132-152-172-192-212-232-252-272-292-312-332-352-372-392
013-033-053-073-093-113-133-153-173-193-213-233-253-273-293-313-333-353-373-393
014-034-054-074-094-114-134-154-174-194-214-234-254-274-294-314-334-354-374-394
015-035-055-075-095-115-135-155-175-195-215-235-255-275-295-315-335-355-375-395
016-036-056-076-096-116-136-156-176-196-216-236-256-276-296-316-336-356-376-396
017-037-057-077-097-117-137-157-177-197-217-237-257-277-297-317-337-357-377-397
018-038-058-078-098-118-138-158-178-198-218-238-258-278-298-318-338-358-378-398
019-039-059-079-099-119-139-159-179-199-219-239-259-279-299-319-339-359-379-399
020-040-060-080-100-120-140-160-180-200-220-240-260-280-300-320-340-360-380-400



davidm wrote:

I have a matrix of 400 commandbuttons (20 by 20) on a Userform and wish
to loop through them, -not horizontally-, but vertically viz.
1,21,31...391, 2, 22, 32...392 through 380,390,400. Since the For each
.. Next construct sweeps horizontally, this popular loop fails. I have
attempted, to no avail, to coerce Excel using conventional For..Next
loop with 20 increments as in:

num=1
For n = 1 to 400 Step 20
Controls("CommandButton" & n).Caption = num
num = num +1
If n\400=4 Then n = (n-400)+1
Next

The above code is merely demonstrative as it only assign captions. The
real object is to get a handle to do the vertical cosine-curve looping.
Any ideas?

Thanks

David

--
davidm
------------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645
View this thread: http://www.excelforum.com/showthread...hreadid=505995

--

Dave Peterson

Hi David,

I assume with 400 buttons you would be using a withevents type class to trap
events. If so you could store them as a 2D array

' In a class named Class1

Public WithEvents cbt As msforms.CommandButton
Public rw As Long, col As Long, idx As Long


Private Sub cbt_MouseDown(ByVal Button As Integer, _
ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
Dim n As Long
If Button = 1 Then
MsgBox rw & " " & col, , cbt.Caption
ElseIf Button = 2 Then 'rt-click

MsgBox cbt.Parent.Controls(idx).Name, , cbt.Name
End If

End Sub

'in a userform, with or without any existing controls

Dim nFirstBTN As Long
Dim clsButtons(1 To 20, 1 To 20) As New Class1

Private Sub UserForm_Click()
With clsButtons(2, 5).cbt ' (col, row)
MsgBox .Name, , .Caption
End With
End Sub

Private Sub UserForm_Initialize()
Dim ctr As msforms.CommandButton
Dim nBtns As Long
Dim r As Long, c As Long

Me.Height = 400: Me.Width = 500 'size to suit at design stage
nFirstBTN = Me.Controls.Count

For c = 0 To 19
For r = 0 To 19
Set ctr = Me.Controls.Add("Forms.CommandButton.1")
With ctr
.Left = c * 24 + 10
.Top = r * 18 + 10
.Height = 18
.Width = 24
.Caption = Chr(65 + c) & r + 1
End With
Set clsButtons(r + 1, c + 1).cbt = ctr
clsButtons(r + 1, c + 1).rw = r + 1
clsButtons(r + 1, c + 1).col = c + 1
clsButtons(r + 1, c + 1).idx = c * 20 + r + nFirstBTN
Next
Next
End Sub

Refer to your buttons using the array eg
clsButtons(c, r).cbt

perhaps by index

for i = For i = nFirstBTN To nFirstBTN + 399
Debug.Print Me.Controls(i).Caption
Next

Regards,
Peter T

"davidm" wrote in
message ...

I have a matrix of 400 commandbuttons (20 by 20) on a Userform and wish
to loop through them, -not horizontally-, but vertically viz.
1,21,31...391, 2, 22, 32...392 through 380,390,400. Since the For each
.. Next construct sweeps horizontally, this popular loop fails. I have
attempted, to no avail, to coerce Excel using conventional For..Next
loop with 20 increments as in:

num=1
For n = 1 to 400 Step 20
Controls("CommandButton" & n).Caption = num
num = num +1
If n\400=4 Then n = (n-400)+1
Next

The above code is merely demonstrative as it only assign captions. The
real object is to get a handle to do the vertical cosine-curve looping.
Any ideas?

Thanks

David


--
davidm
------------------------------------------------------------------------
davidm's Profile:
http://www.excelforum.com/member.php...o&userid=20645
View this thread: http://www.excelforum.com/showthread...hreadid=505995

Many thanks Ken, Prat, Dave and Peter. Collectively, you've made my day!
The piggy-backed looping idea does the trick and how nifty is that!

To Peter:

While I did not feel like clogging my request with detail, you have
truly anticipated what I am grappling with by harking to Class event.
My project makes use of this but rather than building a 2-D array,as
you demonstrated, I constructed a 1-D array which is not quite as
amenable. I took your cue and had great results.


Once again, thanks all.


David


--
davidm
------------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645
View this thread: http://www.excelforum.com/showthread...hreadid=505995

Not to cause too much heartache, but I don't think I've ever seen a userform
with 400 buttons. Are you sure that buttons are the best way for the user to
interact?

It just seems like a lot to me.

davidm wrote:

Many thanks Ken, Prat, Dave and Peter. Collectively, you've made my day!
The piggy-backed looping idea does the trick and how nifty is that!

To Peter:

While I did not feel like clogging my request with detail, you have
truly anticipated what I am grappling with by harking to Class event.
My project makes use of this but rather than building a 2-D array,as
you demonstrated, I constructed a 1-D array which is not quite as
amenable. I took your cue and had great results.

Once again, thanks all.

David

--
davidm
------------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645
View this thread: http://www.excelforum.com/showthread...hreadid=505995

--

Dave Peterson

David,

Glad there was something useful there. Looking at what I posted the logic of
variables is a bit askew (after testing I changed things around!). Might
want to re-arrange things, incl the dimensions as rows x columns.

Dave,

Not to cause too much heartache, but I don't think I've ever seen a
userform
with 400 buttons. Are you sure that buttons are the best way for the user
to
interact?

Must admit I wondered about that, but I had done enough second guessing for
one day!

FWIW I've read reports of problems with over 256 controls in a form but it
seems to work OK.

Regards,
Peter T

"davidm" wrote in
message ...

Many thanks Ken, Prat, Dave and Peter. Collectively, you've made my day!
The piggy-backed looping idea does the trick and how nifty is that!

To Peter:

While I did not feel like clogging my request with detail, you have
truly anticipated what I am grappling with by harking to Class event.
My project makes use of this but rather than building a 2-D array,as
you demonstrated, I constructed a 1-D array which is not quite as
amenable. I took your cue and had great results.


Once again, thanks all.


David


--
davidm
------------------------------------------------------------------------
davidm's Profile:
http://www.excelforum.com/member.php...o&userid=20645
View this thread: http://www.excelforum.com/showthread...hreadid=505995

I've never approached that many controls and if others have reported problems,
it might be a good thing for DavidM to keep an eye on.

Maybe to alleviate the threat a bit: What version of excel did you use when you
tested your gigantic(!) userform with no problems?

Peter T wrote:

David,

Glad there was something useful there. Looking at what I posted the logic of
variables is a bit askew (after testing I changed things around!). Might
want to re-arrange things, incl the dimensions as rows x columns.

Dave,

Not to cause too much heartache, but I don't think I've ever seen a
userform
with 400 buttons. Are you sure that buttons are the best way for the user
to
interact?

Must admit I wondered about that, but I had done enough second guessing for
one day!

FWIW I've read reports of problems with over 256 controls in a form but it
seems to work OK.

Regards,
Peter T

"davidm" wrote in
message ...

Many thanks Ken, Prat, Dave and Peter. Collectively, you've made my day!
The piggy-backed looping idea does the trick and how nifty is that!

To Peter:

While I did not feel like clogging my request with detail, you have
truly anticipated what I am grappling with by harking to Class event.
My project makes use of this but rather than building a 2-D array,as
you demonstrated, I constructed a 1-D array which is not quite as
amenable. I took your cue and had great results.


Once again, thanks all.


David


--
davidm
------------------------------------------------------------------------
davidm's Profile:
http://www.excelforum.com/member.php...o&userid=20645
View this thread: http://www.excelforum.com/showthread...hreadid=505995


--

Dave Peterson

What version of excel did you use when you
tested your gigantic(!) userform with no problems?

Originally in xl2000, but following your question I've just tried same code
(exactly as posted) in Xl97. Works fine in these versions including the
class events.

I don't think there's a fixed limit of 256 controls per form, reported
problems might be related to other things such as volume of code - I don't
know.

Regards,
Peter T

"Dave Peterson" wrote in message
...
I've never approached that many controls and if others have reported
problems,
it might be a good thing for DavidM to keep an eye on.

Maybe to alleviate the threat a bit: What version of excel did you use
when you
tested your gigantic(!) userform with no problems?

Peter T wrote:

David,

Glad there was something useful there. Looking at what I posted the
logic of
variables is a bit askew (after testing I changed things around!). Might
want to re-arrange things, incl the dimensions as rows x columns.

Dave,

Not to cause too much heartache, but I don't think I've ever seen a
userform
with 400 buttons. Are you sure that buttons are the best way for the
user
to
interact?

Must admit I wondered about that, but I had done enough second guessing
for
one day!

FWIW I've read reports of problems with over 256 controls in a form but
it
seems to work OK.

Regards,
Peter T

"davidm" wrote in
message ...

Many thanks Ken, Prat, Dave and Peter. Collectively, you've made my
day!
The piggy-backed looping idea does the trick and how nifty is that!

To Peter:

While I did not feel like clogging my request with detail, you have
truly anticipated what I am grappling with by harking to Class event.
My project makes use of this but rather than building a 2-D array,as
you demonstrated, I constructed a 1-D array which is not quite as
amenable. I took your cue and had great results.


Once again, thanks all.


David


--
davidm

------------------------------------------------------------------------
davidm's Profile:
http://www.excelforum.com/member.php...o&userid=20645
View this thread:
http://www.excelforum.com/showthread...hreadid=505995


--

Dave Peterson

Hi all,

I couldn't imagine that the thought of generating and using 40
commandbuttons would raise eyebrows. I had to employ such staggerin
number to design a Cross-Word Puzzle Maker which has a feature tha
checks and authencates words formed-horizontally and vertically. A
might be expected, it is not a commercial product but serves it
purpose within Excel functionality.

Davidm

--
david
-----------------------------------------------------------------------
davidm's Profile: http://www.excelforum.com/member.php...fo&userid=2064
View this thread: http://www.excelforum.com/showthread.php?threadid=50599

Hi Davidm,
I like the sound of your crossword puzzle maker. What are the chances
of a copy?
Ken Johnson