[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 1998-11-26
9
Medium Priority
?
176 Views
Last Modified: 2010-05-03
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
Comment
Question by:matthias_preuter
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 1

Expert Comment

by:zivf
ID: 1446891
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
 

Author Comment

by:matthias_preuter
ID: 1446892
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
 
LVL 1

Expert Comment

by:wford
ID: 1446893
You cannot use the click event on this control?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 5

Expert Comment

by:vboukhar
ID: 1446894
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
 

Expert Comment

by:TomKid2004
ID: 1446895
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
 

Author Comment

by:matthias_preuter
ID: 1446896
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
 
LVL 5

Expert Comment

by:vboukhar
ID: 1446897
Have you check my previous comment?
0
 

Author Comment

by:matthias_preuter
ID: 1446898
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
 
LVL 1

Accepted Solution

by:
rondeauj earned 100 total points
ID: 1446899
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question