Help to loop Commandbuttons vertically

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

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

Hope this idea works

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

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

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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" <[email protected]> wrote in
> message news:[email protected]...
> >
> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in
> > message news:[email protected]...
> > >
> > > 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

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

--
Regards,
Tom Ogilvy

"Peter T" <peter_t@discussions> wrote in message
news:%[email protected]...
> > 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote
in
> > > message news:[email protected]...
> > > >
> > > > 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
>
>

"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> The Knowledge base article back in the days of xl97 quoted 411 controls as
I
> recall.
>
> --
> Regards,
> Tom Ogilvy
>
> "Peter T" <peter_t@discussions> wrote in message
> news:%[email protected]...
> > > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote
> in
> > > > message news:[email protected]...
> > > > >
> > > > > 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
> >
> >
>
>

Just tried 500 controls in xl97 - no problem!

Regards,
Peter T

"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> The Knowledge base article back in the days of xl97 quoted 411 controls as
I
> recall.
>
> --
> Regards,
> Tom Ogilvy
>
> "Peter T" <peter_t@discussions> wrote in message
> news:%[email protected]...
> > > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote
> in
> > > > message news:[email protected]...
> > > > >
> > > > > 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
> >
> >
>
>

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

Peter T wrote:
>
> Just tried 500 controls in xl97 - no problem!
>
> Regards,
> Peter T
>
> "Tom Ogilvy" <[email protected]> wrote in message
> news:[email protected]...
> > The Knowledge base article back in the days of xl97 quoted 411 controls as
> I
> > recall.
> >
> > --
> > Regards,
> > 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

--
Regards,
Tom Ogilvy



"Peter T" <peter_t@discussions> wrote in message
news:[email protected]...
> Just tried 500 controls in xl97 - no problem!
>
> Regards,
> Peter T
>
> "Tom Ogilvy" <[email protected]> wrote in message
> news:[email protected]...
> > The Knowledge base article back in the days of xl97 quoted 411 controls
as
> I
> > recall.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Peter T" <peter_t@discussions> wrote in message
> > news:%[email protected]...
> > > > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]>
wrote
> > in
> > > > > message
news:[email protected]...
> > > > > >
> > > > > > 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
> > >
> > >
> >
> >
>
>

> 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

Hi all,

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

Davidm.

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