commandbuttons won't trigger code.

  • Thread starter Thread starter Bert
  • Start date Start date
B

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
 
Get to the Immediate Window within your VBE
and type

Application.EnableEvents = True << and press the Enter Key
 
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.
 
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.
 
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?
 
You're right. In the code, it's actually as you have it corrected.
....still won't run, though.
 
Hi Bert,

=============
Dave Peterson:
I don't know why it's happening, but as an alternative...
Click to expand...
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
Click to expand...

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
'<<=========
 
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.
 
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
 
Hi Bert,
Your suggestion seems like a simple, straightforward solution.
Thanks
Click to expand...

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


---
Regards.
Norman


Bert said:
Your suggestion seems like a simple, straightforward solution.
Thanks.
Click to expand...
 
Back
Top