ExcelBanter - Classes Excel and Access

Hi,

I know this is off-topic, but have posted in microsoft.public.access.forms
with no success so I thought someone here might be able to point me in the
right direction.

The class module was an early example from J Walkenbach 'Excel 97 Power
Programming with VBA'. As you can see, tried to adapt this to Access forms,
but no luck.

Original Post

I am trying to automate the behaviour of many buttons in Access at once.
Something like a control array in VB. I can do this in Excel, but can't
figure out if there is an Access equivalent. Has anyone any pointers for me?

Firstly I built a class module called BtnClass with the following code;

Option Compare Database

' Declaration for Access
Public WithEvents ButtonGroup As CommandButton
' Declaration for Excel
' Public WithEvents ButtonGroup As MSForms.CommandButton

Private Sub ButtonGroup_Click()
MsgBox "You clicked - " & ButtonGroup.Name
End Sub

Then I built Form1, placed two button on it, and added the following code;

Dim Buttons() As New BtnClass

Private Sub Form_Load()
Dim ButtonCount As Integer
Dim ctl As Control

ButtonCount = 0

For Each ctl In Me.Controls
If TypeName(ctl) = "CommandButton" Then
ButtonCount = ButtonCount + 1
ReDim Preserve Buttons(1 To ButtonCount)
Set Buttons(ButtonCount).ButtonGroup = ctl
End If
Next ctl

End Sub

If I run the form, the Form_Load procedure works fine, but the button events
do not fire.

Now, this works in Excel except that in Excel a CommandButton is a member of
MSForms, whereas in Access CommandButtons are members of Access.

Is it possible to make this work in Access?

Ed Ferrero
http://edferrero.m6.net