commandbuttons won't trigger code.

[Bert]

In Excel 2003, I've created a userform. Then through macros, I add
commandbuttons. I've already added in the Userform code (using the editor)
which should execute when the buttons are clicked. (There's one subroutine
for each button, e.g.:
Private Sub CommandButton1_Clicked()
Call Bclicked(1)
End Sub
The problem is, when I click on the commandbuttons generated by the macro,
the none of the subroutines execute. (I do have two commandbuttons that are
predefined, and they work fine.)
Any suggestions why this might be happening?
Thanks

 

[Jim May]

Get to the Immediate Window within your VBE
and type

Application.EnableEvents = True << and press the Enter Key

 

[JLGWhiz]

If it is written as posted it won't fire because the sytax is in error.

Private Sub CommandButton1_Clicked() <<<This is in error
Call Bclicked(1)
End Sub

Correct syntax is Private Sub CommandButton1_Click()

The macro call for Bclicked correctly has an argument in parentheses but is
the argument valid? If the argument is not valid then that macro will not
run and you should be getting an error message.

 

[Dave Peterson]

I don't know why it's happening, but as an alternative...

How about adding all the commandbuttons you need, but hide the ones you don't
need until later. I've always found making something visible is a lot easier to
do.

 

[Bert]

Interesting suggestion. If I make the buttons invisible, can I resize the
Userform to so that I don't have half of it seem empty?

 

[Bert]

You're right. In the code, it's actually as you have it corrected.
....still won't run, though.

 

[Norman Jones]

Hi Bert,

=============
Dave Peterson:

I don't know why it's happening, but as an alternative...

How about adding all the commandbuttons you need, but hide the ones you
don't
need until later. I've always found making something visible is a lot
easier to

Interesting suggestion. If I make the buttons invisible, can I resize the
Userform to so that I don't have half of it seem empty?
=============

As a schematic example. consider:

'=========>>
Option Explicit

'------------->>
Private Sub UserForm_Initialize()
With Me
.Height = 130
.CommandButton1.Visible = False
.CommandButton2.Visible = False
End With
End Sub

'------------->>
Sub CommandButton1_Click()
MsgBox "Hi from CommmandButton1"
End Sub

'------------->>
Sub CommandButton2_Click()
MsgBox "Hi from CommmandButton2"
End Sub

'------------->>
Private Sub CommandButton3_Click()
With Me
.CommandButton1.Visible = True
.CommandButton2.Visible = True
.Height = .Height + .CommandButton1.Height + 10
End With
End Sub
'<<=========

 

[JLGWhiz]

It might help to post the code that creates the buttons, plus the click event
code behind the buttons that don't fire. Otherwise, we are whistling in the
wind here.

 

[Bert]

Here's the code that the click event should activate:
Private Sub CommandButton1_Click()
Call BClicked(1)
End Sub

Private Sub CommandButton2_Click()
Call BClicked(2)
End Sub
....and so on. A minimum of 20 buttons, possibly as many as 40.



Here's the code that creates rows and columns of buttons:

Sub Add_Buttons2(aRows, aCols)
bHeight = 42
bWidth = 42
VOffset = bHeight + 3
HOffset = bWidth + 3
StartLeft = (-1 * HOffset) + 2
StartTop = (-1 * VOffset) + 40
a = 1
w = (aCols * (HOffset + 1.3))
If w < 278 Then ' make sure the form is wider than label1 and the two
predefined buttons
w = 278
HOffset = (278 / aCols) - 2
StartLeft = StartLeft - 2
End If
UserForm4.Width = w
UserForm4.Height = (aRows * (VOffset + 6.4)) + 38 ' the 38 accounts for the
yes/no prompt at top of form

Dim myUF As UserForm
Set myUF = UserForm4

Dim myBtn As Control

For b = 1 To aRows '
CurTop = StartTop + (VOffset * b)
For c = 1 To aCols
Set myBtn = UserForm4.Controls.Add("Forms.CommandButton.1")
With myBtn
.Left = StartLeft + (HOffset * c)
.Top = CurTop
.Width = bWidth
.Height = bHeight
.FontSize = 26
.Caption = a
End With
Call Add_Code(Trim$(Str$(a)))
If a >= TotalItems Then
Exit Sub
End If
a = a + 1
Next c
Next b
End Sub

 

[Bert]

Your suggestion seems like a simple, straightforward solution.
Thanks.

 

[Norman Jones]

Hi Bert,

Your suggestion seems like a simple, straightforward solution.
Thanks

In fact the suggestion was made by
Dave and, therefore, the thanks are
due to him.


---
Regards.
Norman

Your suggestion seems like a simple, straightforward solution.
Thanks.