It was easier than I though. You want Textbox1 and Spinbutton1 assigned
to the same class then Textbox2 and Spinbutton2. Make sure your names
of the associated textboxes and the Spinbutton have the same number
assigned. Then use the code I modified below. You need only one class
assigned for each set of textboxes and spinbuttons.


Option Explicit
Dim mcolEvents As Collection
Sub InitializeEvents()
Dim objTextBox As OLEObject
Dim objSpinButton As OLEObject
Dim osh As Worksheet
Dim clsEventsTB As TBClass
Dim clsEventsSB As TBClass
Dim bxName As String
Dim bxNumber As Integer
Dim SpinName As String

Set osh = ThisWorkbook.Worksheets(1)
If mcolEvents Is Nothing Then
Set mcolEvents = New Collection
End If

'Loop through all the controls
For Each objTextBox In osh.OLEObjects
If TypeName(objTextBox.Object) = "TextBox" Then
'Create a new instance of the event handler class
Set clsEventsTB = New TBClass

'Tell it to handle the events for the text box
Set clsEventsTB.TBControl = objTextBox.Object

'get testbox name
bxName = objTextBox.Name
bxNumber = Val(Replace(bxName, "TextBox", ""))

'get spinbuton name
SpinName = "SpinButton" & bxNumber
Set objSpinButton = ActiveSheet.OLEObjects(SpinName)
Set clsEventsTB.SBControl = objSpinButton.Object

'Add the event handler instance to our collection,
'so it stays alive during the life of the workbook
mcolEvents.Add clsEventsTB
mcolEvents.Add clsEventsSB
End If
Next
End Sub

Sub TerminateEvents()
'Here the collection of classes is destroyed so memory will be freed
up:
Set mcolEvents = Nothing
End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=164025

Microsoft Office Help