• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 178
  • Last Modified:

How can i create a event for a array of buttons (VBA)

I have created a array of togglebuttons as follows in VBA/Excel :

For X = 0 To 20

    Set Button( X ) = _
    ButtonForm.Controls.Add("Forms.Togglebutton.1")
    With Button(X)
        .Width = 25
        .Height = 18
        .Left = 6
        .Top = 60 + ( X * 20)
        .Caption = ""
        .Enabled = True
        .Visible = True
        .Font.Bold = True
    End With

Next

When these togglebuttons change or clicked they all must run the proc: ButtonChangedProc.
(OnAction,AddEventProc won't work) ?

Regards,

Matthias Preuter
0
matthias_preuter
Asked:
matthias_preuter
1 Solution
 
zivfCommented:
1. if you will place one control with property index set to a number, you will have it refered to by VB as an array.
2. at runtime, use Load cmdButtonName(cmdButtonName.UBound+1) to add items to the array.
3. if the control is already set an Index number, when you choose it in the objects list in the coed window, the first argument in all events is the specific control's index. this way you can know which one exactly was it and refer to it.
0
 
matthias_preuterAuthor Commented:
I don't think you understood the question!

I want to give my, already declared, buttons a event or macro-procedure (this is VB for Applications for EXCEL97)

Greetz,

Matthias
0
 
wfordCommented:
You cannot use the click event on this control?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
vboukharCommented:
Look at:
Sub test()
Worksheets(1).Activate
Dim mButton(10) As Button
Dim X As Integer
For X = 1 To 10
Set mButton(X) = ActiveSheet.Buttons.Add(6, 60 + (X * 20), 25, 18)
With mButton(X)
Caption = ""
Enabled = True
Visible = True
Font.Bold = True
OnAction = "my_sub"
End With
Next X
End Sub

Sub my_sub()
 MsgBox "press " & Application.Caller
End Sub
or I misunderstood something? (for Excel 95)
0
 
TomKid2004Commented:
When you create an array of buttons, the clik event is al you need.  Just format the click event like this:

Sub Buttons_Click(Index As Integer)
                 'VB automatically checks what the index is
     Select Case Index
          Case 1
               'Code for first button
          Case 2
               'Code for second buttton
          Case...  'etc.
     End Select
End sub

Hope this works.
0
 
matthias_preuterAuthor Commented:
This code is not working in VBA.

We have already change the code so that we don't use array of buttons.
But i keep asking myself, why can i make a array of controls and can't create events for it?!! In other object-languages (Delphi, Visual dBASE) it does work!

Greetz,

Matthias Preuter


0
 
vboukharCommented:
Have you check my previous comment?
0
 
matthias_preuterAuthor Commented:
VBoukhar,

Yes i have, but this doesn't work in Excel97, The OnAction event does only work on a 'form-sheet', and not in a form created in the VBA-editor.

Thanx anyway,
Matthias Preuter

0
 
rondeaujCommented:
you can create a custom event for your program but you will have to tell VB when to fire the event like this

private event whatever()
'put your code here
end event

to fire this events you will use this code:

raiseevent whatever

put the code where you what the event to raise

0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now