Referencing A Subprocedures Name (Re-post)
 

Here is a paste from a post I made a week or so back and
I still need help with... the latest reply having not
been answered and I still need the issue addressed!

////////////////////////////////////////////////////////

Hi!

Thanks for the help, much appreciated... theres one
problem though...

The textboxes I'm using are not on a form, they are "on
top" of the spreadsheet I have created, for want of a
better term. Values are then entered into the
textboxes... which were then originally passing the data,
through the linked cell, as a string. The code I am
putting in is to convert it to an integer for use in
calculations on the main sheet. The main jist of what I'm
saying... the textbox controls are not part of a form,
they are all just individually linked, through their
properties, to the spreadsheet.

A major problem I have found is if anyone adds / removes
rows it messes up the entire sheet as all the linked
cells for the textboxes apparently change... hence the
reason why I'm trying to just do it all through code, the
code for each textbox being the same and just passing the
changing variables (the linked cell and cell value)
through to the function to be converted.

I'm guessing some slight modifications to this solution
would create a working demo?

Thanks for any more help.

Bryn


-----Original Message-----
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

Referencing A Subprocedures Name (Re-post)
 

Here is the original post if it is of any help!

//////////////////////////////////////////////////////////

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.

Referencing A Subprocedures Name (Re-post)
 

Try this as a demo:

1. Create a new blank workbook. By default the first sheet is
codename'd Sheet1.
2. From the Controls Toolbox toolbar, add an ActiveX textbox to
Sheet1. Place it 'inside' cell B2.
3. Create a new class module, named Class1 by default, and paste in
the following code:

'<Code in class module named Class1--------
Option Explicit

Private WithEvents m_oTextBox As MSForms.TextBox
Private m_oRange As Excel.Range

Public Function Init( _
ByVal MSFormsTextBox As MSForms.TextBox, _
ByVal ExcelRange As Excel.Range _
) As Boolean

Set m_oTextBox = MSFormsTextBox
Set m_oRange = ExcelRange

Init = True

End Function

Private Sub m_oTextBox_Change()
Convert
End Sub

Private Function Convert() As Boolean
Dim intTest As Integer
On Error Resume Next
intTest = CInt(m_oTextBox.Text)
If Err.Number = 0 Then
m_oRange.Value = intTest
Else
m_oRange.Value = "Error!"
End If
On Error GoTo 0
End Function
'</Code in class module named Class1-------

4. Paste the following code to the ThisWorkbook code module:

'<Code in ThisWorkbook code module--------
Option Explicit

Private c As Class1

Private Sub Workbook_Open()
Set c = New Class1
c.Init Sheet1.TextBox1, Sheet1.Range("A2")
End Sub
'</Code in ThisWorkbook code module-------

5. Run the Workbook_Open sub (either save, close and reopen the
workbook, or just run it from the VBE).

6. Type in the textbox. If the text is an integer it will be shown in
cell A2, otherwise an error message will appear. You can delete/insert
rows above/below row2 and the functionality still works.

--

"Bryn" wrote in message ...
Here is the original post if it is of any help!

//////////////////////////////////////////////////////////

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.