Here's one fro all you budding programmers!
The issue here is that the textboxes already exist. Its
straightforward enoght adding textboxes, but we need to
do something clever.

So. The trick is to iterate through all the controls,
adding those that are required to a collection, and also
using our friendly class to trap events.

So here's a demo, and please email direct for the file if
you want.

There are three parts
a) create a userform
b) add the class
c) add some code to respond to the events.

so, we'll start with (c)
add a standard module and put this code into it:

Sub PastReply(msg As String, cbx As Control)
With UserForm1
.Label1.Caption = msg
Set .ctrl = cbx
End With
End Sub

this procedure takes a text string in the variable msg
and pops ito a label on the userform, plus passes the
contro itself back to the form

(b) add a class module
This will hold the ckeck box (for my example) and also
set the changed event to call the procedure that we just
wrote. Copy the following into the class module:

Option Explicit

Public WithEvents m_txtMyCheckBox As MSForms.CheckBox

Public Event Changed(text As String)


Private Sub m_txtMyCheckBox_Click()
PastReply "You clicked " & m_txtMyCheckBox.Caption _
& vbLf & _
m_txtMyCheckBox.Value, _
txtMyCheckBox

End Sub

(a) now create the form.
Add a new userform ( userform1) with a dozen or so
checkboxes, each should have a different caption. We
could do this in code, but our questioner already has the
controls. add a label (label1) and a button (cmdClose)

Add this code to the userform:
Option Explicit
Dim colCheckBoxes As New Collection
Dim WithEvents ctCheckBox As clsControl
Public ctrl As Control

Private Sub UserForm_Initialize()
Set ctCheckBox = New clsControl
Dim ctrl As Control
For Each ctrl In Controls
If ctrl.Name Like "Check*" Then
Add_A_Control ctrl
End If
Next
End Sub


Private Sub Add_A_Control(ctrl As Control)


Set ctCheckBox = New clsControl
Set ctCheckBox.m_txtMyCheckBox = ctrl
colCheckBoxes.Add ctCheckBox

End Sub


Private Sub cmdClose_Click()
Unload Me
End Sub

The initialize iterates through the controls, adding the
checkboxes to the collection that hosts the classes. My
controls' names all start with the word"check" eh
checkbox1, checkbox2 etc

Now when you run the form, each time you click a
checkbox, the event is fired and the label shows you
which box you checked.

It will be a minor task to change to code for text
boxes....and as you can see, you'd only need to port a
tiny amout of code.

Patrick Molloy
Microsoft Excel MVP


















-----Original Message-----
Here is an example of a subprocedure I am using... it is
used to convert an input into an integer.

Private Sub TextBox41_Change()

Dim linkedCell As String, cellValue As String

linkedCell = TextBox41.linkedCell

cellValue = TextBox41.Value

Call convert(linkedCell, cellValue)

End Sub

However, I am wanting to reference the name of the
subprocedure in which this code is being ran... so
instead
of having to type:

linkedCell = TextBox41.linkedCell

cellValue = TextBox41.Value

which is textbox specific I can simply reference the
name
of the textbox through the sub's name, after having
deleted
the _Click part previous to the remaining code, ie.
something like

linkedCell = "sub.name".linkedCell

cellValue = "sub.name".Value

I have over 100 of these text boxes to edit and I dont
want
to be having to paste in all this code only to have to
go
in and individually change the textbox numbers
specfically
for each textbox!

Thanks for any help.

Bryn.
.