Solved

Handlers for dynamically created buttons

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

911 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

19 Experts available now in Live!

Get 1:1 Help Now