UserForms Private Sub names

[MKnust]

Hello, I am trying to call a Private Sub in a form which name is supposed to be generated from another Private Sub in the same form:

Private Sub mysub()

Dim i As Variant
Dim contr As Control
Dim contrname As String
Dim subname As String

Set contr = ActiveControl
contrname = contr.Name

i = Right(contrname, (Len(contrname) - Len("TexTBox"))) 'If the ActiveControl's name in the UserForm is TextBox1 then i = 1

subname = "TextBox" & (i + 5) & "_" & "Enter" 'Call an existing Private sub in the same form which name is TextBox6_Enter ()

Run subname

End Sub

This is the error message I am getting:

Run-time error '1004':
Cannot run the macro "TextBox6_Enter". The macro may not be available in this workbook or all macros may be disabled.

Any help is much appreciated.

Thank you in advance.

 

[AlphaFrog]

Try the CallByName function

CallByName UserForm1, subname, VbMethod

Where UserForm1 is the nameof your userform.

 

[Jaafar Tribak]

Try the CallByName function

CallByName UserForm1, subname, VbMethod

Where UserForm1 is the nameof your userform.

CallByName requires subname to be Public not Private... A Private Sub defined in a Class\UserForm Module is not a Method.

 

[MKnust]

Thank you AlphaFrog and Jaafar.

CallByName indeed does the job as long as I keep my subs public which is not the case in my project.

Just to further clarify the point, my UserForm1 has several rows of TextBoxes, where, for example:

Private sub TextBox6_Enter does a calculation using data in TextBox1 (Triggered when TextBox6 get focus)
Private sub TextBox12_Enter does a calculation using data in TextBox7 (Triggered when TextBox12 get focus)...
... and so on.

That's why I was trying to compose UserForm1 Private Sub names dinamically, so to say, instead of writing them literally.

As the UserForm's subs are Private by definition, the use of CallByName is restricted.

Anyway, I decided to use TextBox6_Enter, TextBox12_Enter, etc... literally in their respective places.

Thank you again to both!

Regards.

 

[Jaafar Tribak]

Thank you AlphaFrog and Jaafar.

CallByName indeed does the job as long as I keep my subs public which is not the case in my project.

You can make the event subs in the userform Public and they would still work.

 

[tfurnivall]

Hello, I am trying to call a Private Sub in a form which name is supposed to be generated from another Private Sub in the same form:

Private Sub mysub()

Dim i As Variant
Dim contr As Control
Dim contrname As String
Dim subname As String

Set contr = ActiveControl
contrname = contr.Name

i = Right(contrname, (Len(contrname) - Len("TexTBox"))) 'If the ActiveControl's name in the UserForm is TextBox1 then i = 1

subname = "TextBox" & (i + 5) & "_" & "Enter" 'Call an existing Private sub in the same form which name is TextBox6_Enter ()

Run subname

End Sub

This is the error message I am getting:

Run-time error '1004':
Cannot run the macro "TextBox6_Enter". The macro may not be available in this workbook or all macros may be disabled.

Any help is much appreciated.

Thank you in advance.

Hi, MKnust

Is the calculation the same for all the textboxes? Or does each textbox have its own personal, "not_shared_with_anyone" calculation?

If the calculations are the same (this is the easy part), then you can simply put that calculation into another Sub in your module, and pass in the values from the appropriate textbox. So, you need to identify what textbox is being entered (which triggers the calculation), and what textbox is used as the reference (which is the calculation you are already doing), and then simply pass whatever you need as parameters to the same calculation. In your example you have

TriggeringControl=Contr         ' This is not really accurate VBA, but I hope the idea is simple
ReferenceControlName="TextBox" & Right(contrname, (Len(contrname) - Len("TextBox")))+5

MyCommonSub ActiveForm.Controls(ReferenceControlName).Value

(You'll need to define MyCommonSub with a single string parameter)

What you are doing is saving space by having just one calculation, and extracting the value once before you call the common calculation.

Now -


If you have different calculations for each textbox, you then must use the CallByName approach, and, as noted, you must make those event handlers Public, rather than private.

If you can analyze the problem, and find any commonalities, then it's worth putting those commonalities anywhere else, rather than in a Form module. I'd go one step further, and say that even if they are all different, those calculations that you need to call should be moved into whatever event handler is already detecting the need to do the calculations. You've demonstrated that you can calculate what textbox you need to access. It is simple, at that point, to get the value you need, and then process it immediately. No need to jump through hoops trying to figure out how to make another control do your processing for you.


(Unless, of course, you may need to do exactly the same thing for different reasons, if the ReferenceControl is entered, or changed or whatever).

Good luck, let us know how you get on,

Tony