Help to loop Commandbuttons vertically

[davidm]

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

 

[Ken Johnson]

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

 

[prabhuraaman]

Hope this idea works

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

 

[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

 

[Dave Peterson]

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

 

[Peter T]

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]

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

 

[Dave Peterson]

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.

 

[Peter T]

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

 

[Dave Peterson]

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]

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

 

[Tom Ogilvy]

The Knowledge base article back in the days of xl97 quoted 411 controls as I
recall.

--
Regards,
Tom Ogilvy

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

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?

the

user

guessing

for

but

 

[Peter T]

The Knowledge base article back in the days of xl97 quoted 411 controls as I
recall.

 

[Peter T]

Just tried 500 controls in xl97 - no problem!

Regards,
Peter T

The Knowledge base article back in the days of xl97 quoted 411 controls as I
recall.

 

[Dave Peterson]

I think you left off "YET". <bg>

Just tried 500 controls in xl97 - no problem!

Regards,
Peter T

 

[Tom Ogilvy]

For your reading pleasure:

http://support.microsoft.com/kb/177842/en-us
OFF97: Invalid Page Fault with More Than 411 Controls in UserForm

While it doesn't express a limit, it at least shows that if there is a limit
it is more than 400

 

[Peter T]

http://support.microsoft.com/kb/177842/en-us

OFF97: Invalid Page Fault with More Than 411 Controls in UserForm

While it doesn't express a limit, it at least shows that if there is a limit
it is more than 400

CAUSE (ie crash)

- More than 411 controls of any type are created on a UserForm. -and-

- Controls created after the 411th are addressed directly by name (such as
UserForm1.Label412) in a Visual Basic macro or procedure.


Anything for a challenge. I "designed" a form with 500 buttons and the
following worked -

Private Sub CommandButton500_Click()
'has caption "Y20"

MsgBox Me.CommandButton500.Name, , _
Me.CommandButton500.Caption
End Sub

Private Sub UserForm_Click()

ReCaption

''in a normal module
'Sub ReCaption()
'Static cap As Long
'cap = cap + 1
'UserForm1.CommandButton500.Caption = cap
'MsgBox UserForm1.CommandButton500.Caption
'End Sub

End Sub

Obviously minimal code & events in the form, perhaps the 411 limit is
related to having event code for each control or perhaps W95/NT as the
article refers to (I used W98 / XL97).

It is of course essential to add - no problems YET, for the benefit of Dave


Regards,
Peter T

PS For anyone mad enough to want to test - I "designed" my form with this

Sub MakeBigForm()
Dim r As Long, c As Long
Dim ctr As Control
Dim oVBComp As Object

Set oVBComp = ThisWorkbook.VBProject.VBComponents("Userform1")

With oVBComp
.Properties("Height") = 400
.Properties("Width") = 620
With .Designer
For c = 0 To 24
For r = 0 To 19
Set ctr = .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
Next
Next
End With
End With
End Sub

 

[davidm]

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

 

[Ken Johnson]

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