Me.LinkedCell type reference for 48 Controls!

[milesUK]

I have a single worksheet containing 48(!) spin button controls over cells B2:D17. They are named SpinButton1 through to SpinButton48 across and down this range. Each one has it's LinkedCell property populated with that cell over which it is placed in the sheet. I want a SpinButton//_Change event procedure that will execute code that makes reference to the cell in the LinkedCell property. However using that method would mean, as far as I can see, inefficiently creating and editing 48 separate routines!

This is what I envisage at the moment:

<code>Private Sub SpinButton1_Change()
Call myupdate(Range(SpinButton1.LinkedCell))
End Sub
.
.
Private Sub SpinButton48_Change()
Call myupdate(Range(SpinButton48.LinkedCell))
End Sub</code>

OK not a major issue as the code stands but if it grows then each additional reference to the button is 47 additional edits. Also if more (!) buttons are added then it even gets more out of hand.

I would like to use something like the "Me" keyword so that for each of the 48 spin button routines I can paste in identical code. Something like this:

<code>Private Sub SpinButton1_Change()
Call myupdate(Range(Me.LinkedCell))
End Sub
.
.
Private Sub SpinButton48_Change()
Call myupdate(Range(Me.LinkedCell))
End Sub</code>

However the Me keyword is for referencing the Sheet and not the Control. Is there any way to reference the parent control from within event procedures? I really don’t fancy editing 48 routines, or more, every time my code changes! Perhaps there is a generic routine that can run for any Spin button event and then get a reference to that object.

In anticipation,

 

[Jaafar Tribak]

if i understand you right , you will probably need to use a generic SpinButton change event handler via a Class module.
Similar questions have been asked here before . Try doing a search for Class modules & events.

Regards.

 

[milesUK]

Jafaar, Thank you for the pointer. Time to supplement my VBA knowledge with classes. I did look into class variables a year or so ago but have never implemented them. Cheers,