CommandButton not responding

[Carim]

Hi,

Based on John's tip 44 to handle several commandbuttons with the same
routine :
http://www.j-walk.com/ss/excel/tips/tip44.htm
I am facing a strange problem since a single commandbutton refuses to
respond ...

Any hints ...?
Thanks in advance for your comments
Cheers

 

[Rick Rothstein]

What do you mean by "a single commandbutton refuses to respond"... that you
have several buttons, but one doesn't respond... or that you only have that
one button and it doesn't repond?

Also, did you modify the code at all? If so, show us the code you are using
(and tell us which button is not responding).

 

[Carim]

What do you mean by "a single commandbutton refuses to respond"... that you
have several buttons, but one doesn't respond... or that you only have that
one button and it doesn't repond?

Also, did you modify the code at all? If so, show us the code you are using
(and tell us which button is not responding).

--
Rick (MVP - Excel)








- Show quoted text -

Hi Rick,

You are right ... 20 Buttons and one of them refuses to respond ...???
Since the code is absolutely identical for all 20 CommandButtons,
would assume
the problem is elsewhere ... all properties are obviously identical
too ...
What would be your intuition ... ?
Cheers

 

[Rick Rothstein]

Which of the 20 CommandButtons is the one that isn't responding.

And you didn't answer.... did you change the posted code in any way? If you
did, post your code.

--
Rick (MVP - Excel)

What do you mean by "a single commandbutton refuses to respond"... that
you
have several buttons, but one doesn't respond... or that you only have
that
one button and it doesn't repond?

Also, did you modify the code at all? If so, show us the code you are
using
(and tell us which button is not responding).

--
Rick (MVP - Excel)








- Show quoted text -

Hi Rick,

You are right ... 20 Buttons and one of them refuses to respond ...???
Since the code is absolutely identical for all 20 CommandButtons,
would assume
the problem is elsewhere ... all properties are obviously identical
too ...
What would be your intuition ... ?
Cheers

 

[Carim]

Which of the 20 CommandButtons is the one that isn't responding.

And you didn't answer.... did you change the posted code in any way? If  you
did, post your code.

--
Rick (MVP - Excel)






Hi Rick,

You are right ... 20 Buttons and one of them refuses to respond ...???
Since the code is absolutely identical for all 20 CommandButtons,
would assume
the problem is elsewhere ... all properties are obviously identical
too ...
What would be your intuition ... ?
Cheers- Hide quoted text -

- Show quoted text -

Well, it is CommandButton10 out of the sequence which goes from
CommandButton6
to CommandButton25 ...
And I have not touched the code whatsoever ....

 

[Carim]

Well, it is CommandButton10 out of the sequence which goes from
CommandButton6
to CommandButton25 ...
And I have not touched the code whatsoever ....- Hide quoted text -

- Show quoted text -

I am totally lost ...
Should I remove this UserForm and build a brand new one from
scratch ... ???

 

[JLGWhiz]

Guess I am a little dense but I don't understand why the 20 buttons running
the same code from a click event. Or did you modify the event code to do
different things? Anyhow, there should be no need to redo the entire form.
Simply fix the one button by giving it its own click event code. I wonder if
you have checked to see if it has the code for that particular button. In
JWalks code he excluded the OK button. Did you exclude a button and forget
to later assign the code to it? You need to shake off the frustration and
think rationally of what your options are.

 

[Carim]

Guess I am a little dense but I don't understand why the 20 buttons running
the same code from a click event.  Or did you modify the event code to do
different things?  Anyhow, there should be no need to redo the entire form.  
Simply fix the one button by giving it its own click event code.  I wonder if
you have checked to see if it has the code for that particular button.  In
JWalks code he excluded the OK button.  Did you exclude a button and forget
to later assign the code to it?  You need to shake off the frustration and
think rationally of what your options are.






- Show quoted text -

Hi JLGWhiz,

Thanks for helping me calming down ...and for the invitation to think
rationally.
After a night of not-so-good sleep, and for the sake of future
readers, I found
a solution : include two additional Locked CommandButtons ( lower and
upper bounds )
when building the group ... for the Class module to recognize all the
20 Buttons in-between !!!
Do not ask me why it is working ..., I suspect the couple Excel 2000
SR1 with XP has not yet
solved all of their ActiveX controls handlings ...
Cheers

 

[Peter T]

Hello again Carim,

I found a solution : include two additional Locked CommandButtons
( lower and upper bounds ) when building the group ...
for the Class module to recognize all the 20 Buttons in-between !!!
Do not ask me why it is working ..., I suspect the couple Excel 2000
SR1 with XP has not yet solved all of their ActiveX controls handlings ...

The reason your code was not working correctly before is because you had
done something wrong. It is not possible to suggest what you did wrong
because you have not given the details Rick asked you for, two times.

Although you may have fixed it, for now, I am sure the reason is not due to
what you say you suspect.

Regards,
Peter T

 

[Carim]

Hello again Carim,


The reason your code was not working correctly before is because you had
done something wrong. It is not possible to suggest what you did wrong
because you have not given the details Rick asked you for, two times.

Although you may have fixed it, for now, I am sure the reason is not due to
what you say you suspect.

Regards,
Peter T

Hi Peter,

Sorry but I I gave the details Rick asked for ...
1. One Button out of a sequence of 20 Buttons is not responding ...
2. The Code has not been changed ... and working with a Class, all 20
Buttons call
the very same code in the unique class ...
Very honestly, I wish I could understand what I have done wrong ...
cause it is important to keep on progressing ...
I trust you when you say " I am sure the reason is not due to
what you say you suspect ... "
What would be your best guess ... ?
Regards

 

[Peter T]

You haven't given any information that helps others to suggest what you
might have done wrong.

Have a go with the following, an empty userform and a class named
clsBtnEvnts. Run the form and check events in all 20 buttons respond.

'''' code in class named 'clsBtnEvnts'

Public WithEvents btn As MSForms.CommandButton
Public id As Long

Private Sub btn_Click()
btn.BackColor = Int(Rnd() * vbWhite)

btn.Parent.Caption = btn.Caption & " ID : " & id
If id = 20 Then
MsgBox "Bye"
Unload btn.Parent
End If

End Sub


'''' end code in class

Option Explicit
Private arrClsBtn(1 To 20) As clsBtnEvnts

Private Sub UserForm_Initialize()
Dim i As Long, r As Long, c As Long
Dim cb As MSForms.CommandButton
Const cGAP As Single = 3, cHT As Long = 18, cWD As Long = 66

For r = 0 To 4
For c = 0 To 3
i = i + 1
Set arrClsBtn(i) = New clsBtnEvnts
Set arrClsBtn(i).btn = Me.Controls.Add("Forms.CommandButton.1")
arrClsBtn(i).id = i
With arrClsBtn(i).btn
.Left = cGAP + c * (cWD + cGAP)
.Top = cGAP + r * (cHT + cGAP)
.Width = cWD
.Height = cHT
.Visible = True
If i = 20 Then
.Caption = "Close"
Else
.Caption = "btn - " & Chr(64 + i)
End If
End With

Next
Next

End Sub

Regards,
Peter T

Hello again Carim,


The reason your code was not working correctly before is because you had
done something wrong. It is not possible to suggest what you did wrong
because you have not given the details Rick asked you for, two times.

Although you may have fixed it, for now, I am sure the reason is not due
to
what you say you suspect.

Regards,
Peter T

Hi Peter,

Sorry but I I gave the details Rick asked for ...
1. One Button out of a sequence of 20 Buttons is not responding ...
2. The Code has not been changed ... and working with a Class, all 20
Buttons call
the very same code in the unique class ...
Very honestly, I wish I could understand what I have done wrong ...
cause it is important to keep on progressing ...
I trust you when you say " I am sure the reason is not due to
what you say you suspect ... "
What would be your best guess ... ?
Regards

 

[Carim]

You haven't given any information that helps others to suggest what you
might have done wrong.

Have a go with the following, an empty userform and a class named
clsBtnEvnts. Run the form and check events in all 20 buttons respond.

'''' code in class named 'clsBtnEvnts'

Public WithEvents btn As MSForms.CommandButton
Public id As Long

Private Sub btn_Click()
    btn.BackColor = Int(Rnd() * vbWhite)

     btn.Parent.Caption = btn.Caption & "   ID : " & id
    If id = 20 Then
        MsgBox "Bye"
        Unload btn.Parent
    End If

End Sub

'''' end code in class

Option Explicit
Private arrClsBtn(1 To 20) As clsBtnEvnts

Private Sub UserForm_Initialize()
Dim i As Long, r As Long, c As Long
Dim cb As MSForms.CommandButton
Const cGAP As Single = 3, cHT As Long = 18, cWD As Long = 66

    For r = 0 To 4
        For c = 0 To 3
            i = i + 1
            Set arrClsBtn(i) = New clsBtnEvnts
            Set arrClsBtn(i).btn = Me.Controls.Add("Forms.CommandButton.1")
            arrClsBtn(i).id = i
            With arrClsBtn(i).btn
                .Left = cGAP + c * (cWD + cGAP)
                .Top = cGAP + r * (cHT + cGAP)
                .Width = cWD
                .Height = cHT
                .Visible = True
                If i = 20 Then
                    .Caption = "Close"
                Else
                    .Caption = "btn - " & Chr(64 + i)
                End If
            End With

        Next
    Next

End Sub

Regards,
Peter T







Hi Peter,

Sorry but I I gave the details Rick asked for ...
1. One Button out of a sequence of 20 Buttons is not responding ...
2. The Code has not been changed ... and working with a Class, all 20
Buttons call
the very same code in the unique class ...
Very honestly, I wish I could understand what I have done wrong ...
cause it is important to keep on progressing ...
I trust you when you say "  I am sure the reason is not due to
what you say you suspect ... "
What would be your best guess ... ?
Regards- Hide quoted text -

- Show quoted text -

Peter,

I could not agree more ...
Your code is working fine ...
What I am after since the very beginning is :
"What is it that I have done wrong ... which prevents only ONE button
out of 20 not to respond ...?"
At least, we both agree it has nothing to do with the code itself ...
since it is common to the 20 Buttons ...
So , in your opinion, What could that be ...?
Cheers

 

[Peter T]

What I am after since the very beginning is :
"What is it that I have done wrong ... which prevents only ONE button
out of 20 not to respond ...?"
At least, we both agree it has nothing to do with the code itself ...
since it is common to the 20 Buttons ...
So , in your opinion, What could that be ...?

I don't have any opinion because I don't know what you have, here are some
guesses:

- One of the controls is not a commandbutton
- For some reason the relavant class was not created
- For some reason the relavant class was created a ref to the class was not
stored
- For some reason the relavant class was created and stored but destroyed
- The class was created and stored fine but for some reason a reference in
the class to the button was not set
- Everything is working fine but for some reason you are not seeing the
event being triggered

It would be very easy for you to step through and debug the classes being
created and destroyed. Temporarily insert Initialize and Terminate events in
the class, in the latter include say debug.? btn.name, btn.Caption (change
'btn' to whatever object ref name you are using).

Regards,
Peter T

 

[Carim]

I don't have any opinion because I don't know what you have, here are some
guesses:

- One of the controls is not a commandbutton
- For some reason the relavant class was not created
- For some reason the relavant class was created a ref to the class was not
stored
- For some reason the relavant class was created and stored but destroyed
- The class was created and stored fine but for some reason a reference in
the class to the button was not set
- Everything is working fine but for some reason you are not seeing the
event being triggered

It would be very easy for you to step through and debug the classes being
created and destroyed. Temporarily insert Initialize and Terminate eventsin
the class, in the latter include say debug.? btn.name, btn.Caption (change
'btn' to whatever object ref name you are using).

Regards,
Peter T

I will go step by step through my six possible errors ...
Thanks a lot for your help and all your advice.
Cheers