Solved

Handlers for dynamically created buttons

Posted on 2007-03-29
5
143 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
  • 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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

760 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now