This subroutines name value

[shaunk]

I am trying to write a generic subroutine that is called from clicking a command_button. No problem so far.

The code is:

Private Sub ACAT_New_Click()
      
    Application.DisplayAlerts = False
    Reporting_Unit = ACAT_NEW.Name
    Application.DisplayAlerts = True
    ' Pass the name of this Command Button to the Pivot table sub.
    Call Pivot_Acat(Reporting_Unit)
    
End Sub

What I want to do is populate the public variable "Reporting_Unit" with the name of the current subroutine, along the lines of Reporting_Unit = this.name.value as in javascript.
I can then pass the name of the current sub to the called routine where it will be used in range/db selection criteria. The trouble is, I don't know the syntax to detect the name of the currently executing subroutine.

In addition, I am still being prompted to accept or decline the cell update , even though I have coded "Application.DisplayAlerts = False" ?

Thanks

 

[PHV]

Have a look at the Application.Caller property.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[shaunk]

PHV,

You have definitely put me on the right track. Bear with me on this one as I am just poking this stuff with a stick.

When I add a control to the worksheet by using the contol toolbox, on right-click I am given the option of viewing code and of course editing code once Visual Basic is open. I can also view the command button properties and change it's name. The code looks like this :

Private Sub ACAT_New_Click()
    
    Select Case Application.Caller
        Case "ACAT_NEW"
            ' default OK button
            MsgBox "ACAT button was pressed."
        Case Else
            ' good programming practice to trap for the unexpected
            MsgBox "Some other button or control called this routine."
    End Select

End Sub

On pressing the button, I am getting a type mismatch error in the case statement.

If, however, I add the button using the button object on the toolbar (not the control toolox), I do not get the opportunity to view/edit code, only assign a macro. The thing is, the above code works in the assigned macro. All is good ! However, if I right click the button object created this way, I can't see it's name. let alone change it. So unless I remember what it was when I created, how do I know what the name of the button was.

I hope this makes sense and thankyou if you got this far.

 

[comaboy]

I *think* Application.Caller is referring to the actual control that makes the call - maybe Application.Caller.Name will work.

To all, please feel free to shoot down in flames if I'm wrong!

 

[TonyJollans]

Hi shaunk,

There are different types of button - as you have seen.

If you have code behind a button from the Control Toolbox (on its Click event) that code has to have a certain name and be in a certain place and you can't make more than one button directly invoke the same procedure. You can make several event procedures all call a common routine but that would be under your control so you can make sure whatever information you need is available. This type of code does not support Application.Caller and you get an error.

If you create a button from the Forms Toolbar you can assign a macro to it - and assign the same macro to multiple buttons. In this case, Application.Caller *does* give you the button name.

So far so good, I think. The easiest way to rename buttons from the Forms Toolbar is to select them and then edit the name displayed in the Name box (top left beside the formula bar). That should get you over your memory problem.

The answer to your original question - about subroutine names - is that they can't be obtained from the system and you must hard code whatever you need yourself.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[shaunk]

Thankyou both...learnt a lot already just from this post.
One more thing...for buttons entered from the form toolbar, there doesn't seem a way to edit the appearance with respect to the fill color...or other design attributes.
I would be handy to have a design feature to these buttons, as there are for buttons entered from the Control Toolbox.

 

[TonyJollans]

I don't think you can do much with them, I'm sorry. You can set all sorts of properties with VBA but they have no effect.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[