Referencing A Subprocedures Name (Re-post)

  • Thread starter Thread starter Bryn
  • Start date Start date
B

Bryn

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
 
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.
 
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.
 
Back
Top