Solved

Handlers for dynamically created buttons

Posted on 2007-03-29
5
153 Views
Last Modified: 2011-09-20
Dear Experts,

Im working on a VBA program where on the form I create some textboxes and buttons dynamically.
I want to handle events for the dynamically created objects.
I think Addhandler is the way to do this but I can't seem to figure out exactly how it works, even after extensive googling. Any help would be much appreciated.

Here is a test setup of my code:

On the form:

Public i As Integer
Private Sub Form_Load()
End Sub

Private Sub CommandButton1_Click()

Set cmbobject = UserForm1.Controls.Add("forms.commandbutton.1")
With cmbobject
.Name = "textbox" & i
.Caption = "text" & i
.Width = 50
.Left = 10
.Top = 10 + (i * 20)
.Height = 20
End With
   
    AddHandler cmbobject.Click, AddressOf cmbobject_Click

i = i + 1
End Sub


and in a separate module in the same project:


Public Sub cmdobject_click()
MsgBox ("test")
End Sub


The AddressOf keeps giving me the error message : expected sub, function or property
and if I change it to:
AddHandler cmbobject.Click, AddressOf Module1.cmdobject_click
it says: sub or function not defined

Thanks in advance

Andreas
0
Comment
Question by:FEWEB
[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
  • 3
  • 2
5 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 18815677
Hi,
AddHandler does not exist in VBA. In order to do what you want, you will need to create a class, add a commandbutton variable declared Public WithEvents and create the click event for it. Then when the form loads, you create a new instance of this class for each commandbutton you create and set the cb variable equal to the commandbutton you created. If you need totally different routines for each commandbutton, you may want to create separate classes for each.
HTH
Rory
0
 

Author Comment

by:FEWEB
ID: 18822362
Dear Rory,

I'm reading up on Class modules now as i am not familiar with them but if you could give me some pointers or a bit of sample code that would be awesome.

The routine for each commandbutton would be the same.

Thanks again

Andreas
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 18822435
Andreas,
I have put together a demonstration workbook and uploaded it here: https://filedb.experts-exchange.com/incoming/ee-stuff/3033-button_class.zip
Hopefully it is clear what is happening in the code, but if you have any questions, let me know.
Rory
0
 

Author Comment

by:FEWEB
ID: 18822446
Thank you very much!, I should be able to work it out from here on.

Thanks for the quick response as well!

Kind regards,

Andreas

0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 18822467
Happy to help - thanks for the grade.
Rory
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
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…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

730 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