Utilizing a Class Module

[Jim Franklin]

Using Excel 2002 - My active sheet has 20 command
buttons. I would like to use a class module to make their
events more efficient.

I have created a class module called "BtnClass". I've
added the following code to that module:

Public WithEvents ButtonGroup As CommandButton
Private Sub ButtonGroup_Click()
MsgBox ButtonGroup.Caption
End Sub

I have added a module called "module1" and have added this
code to it:

Dim Buttons() As New BtnClass
Sub EnableBtnEvents()
Dim ButtonCount As Integer
Dim Ctl As OLEObject
ButtonCount = 0
For Each Ctl In ActiveSheet.OLEObjects
ButtonCount = ButtonCount + 1
ReDim Preserve Buttons(1 To ButtonCount)
Set Buttons(ButtonCount).ButtonGroup = Ctl
Next Ctl
End Sub

I can't seem to get it to work. What am I doing wrong?
Thanks for your help.

 

[Dick Kusleika]

Jim

Change this line

Set Buttons(ButtonCount).ButtonGroup = Ctl

to

Set Buttons(ButtonCount).ButtonGroup = Ctl.Object

I assume you were getting a Type Mismatch error, but if it's something else,
say why it's not working.

 

[Jim Franklin]

Dick - that's got it going. Thanks so much.

-----Original Message-----
Jim

Change this line


to

Set Buttons(ButtonCount).ButtonGroup = Ctl.Object

I assume you were getting a Type Mismatch error, but if it's something else,
say why it's not working.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com


.