Application.Caller with Command Buttons on a sheet?

rbeeman · Aug 15, 2006

I'm trying to use following line of code...

ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row

...to generically determine the row in which a button was clicked (which
initiates a macro). The code works great for Buttons created with the Forms toolbar, but does not work for Command Buttons created with the Controls Toolbox toolbar (which, I assume is ActiveX). I need to use the latter type so I can also programatically change its BackColor.

The error seems to be Error 2023.

Any way around this?

Andrew Poulsom · Aug 15, 2006

Have you tried eg?

Code:

Private Sub CommandButton1_Click()
    MsgBox CommandButton1.TopLeftCell.Address
End Sub

rbeeman · Aug 15, 2006

That would probably work, except that I several Command Buttons on several sheets, so I'm trying to write it generically so that I don't have to have unique code for each one.

Andrew Poulsom · Aug 15, 2006

To do that you would need a Class module:

Code:

' Class module named Class1

Public WithEvents ButtonGroup As CommandButton

Private Sub ButtonGroup_Click()
    MsgBox "Hello from " & ButtonGroup.Name
End Sub

' General module

Dim Buttons() As New Class1

Sub Class_Init()
    Dim Sh As Worksheet
    Dim Obj As OLEObject
    Dim ButtonCount As Integer
    For Each Sh In ThisWorkbook.Worksheets
        For Each Obj In Sh.OLEObjects
            If TypeName(Obj.Object) = "CommandButton" Then
                ButtonCount = ButtonCount + 1
                ReDim Preserve Buttons(1 To ButtonCount)
                Set Buttons(ButtonCount).ButtonGroup = Obj.Object
            End If
        Next Obj
    Next Sh
End Sub

Run the Class_Init procedure (it could be called from Workbook_Open or the like), then click one of your CommandButtons.

rbeeman · Aug 15, 2006

Thanks for your help! I'll try that. It might be a tad over my programming skills though.

jojohan · Jul 21, 2009

Hi, and thank you all for this website and forum.
Well I'm unburying this 3yo post because I found the beginning of my solution here, so sorry about it and 'hope someone could help me.

I wrote a VBA code which create some command buttons (no forms buttons), this way :

Code:

Do While RefCellRecap.Offset(j, 0) <> ""
 Set tObject = Sheets("Recap").OLEObjects.Add(ClassType:="Forms.Commandbutton.1", _
                                                          Link:=False, _
                                                          DisplayAsIcon:=False, _
                                                          Left:=RefCellRecap.Offset(j, 1).Left + 1, _
                                                          Top:=RefCellRecap.Offset(j, 1).Top + 1, _
                                                          Width:=RefCellRecap.Offset(j, 1).Width - 1, _
                                                          Height:=RefCellRecap.Offset(j, 1).Height - 1)
             tObject.Name = "ButtonDetailsI" & j
             tObject.Object.Caption = "Détails"
             Set tObject = Nothing
 j = j + 1
 Loop
 Class_Init()<!--EndFragment-->

In fact there are plenty of buttons (50+), which all call the same function so I used class module as described above.
Just after creation of buttons, it runs Class_Init() procedure but with no effect at all on the class. Class_Init detects the correct number of buttons, but no button is working and ButtonGroup is never called when any button is clicked.

But once buttons are created, when I run manually Class_Init() again, then it's all working ! It appears that Class_Init doesn't want to work if it is running just after creation of buttons or during the same instance of code execution. In my case I absolutely need all the buttons functionals just after their creation, without user interaction.

I've tried everything : insert some DoEvents and Sleep between creation and Class_Init, execute both in two distincts functions, run Class_Init several times, doesn't change anything...

If anyone could help, it would be more than appreciated since I already spent dozens of hours on it. Thanks in advance
BR

PS. Sorry for my english

Richard Schollar · Jul 21, 2009

Hi

Firstly, your English is perfectly fine (and is about 1 million times better than my French!) so no need to apologise

I think there is a problem in the original Class_Init code, but I also think you may be encountering a problem caused by you adding ActiveX controls to the sheet. I would divorce the Class_Init from the code which creates the buttons (you may notice a problem if you step thru the button-adding code using F8 in the VBE rather than running the macro).

I think the class_init code should look like this:

Rich (BB code):

Sub Class_Init()

    Dim Sh As Worksheet
    Dim Obj As OLEObject
    Dim ButtonCount As Integer
    Set Sh = Sheets("Sheet1")
    Erase buttons
        For Each Obj In Sh.OLEObjects
            If TypeName(Obj.Object) = "CommandButton" Then
                ButtonCount = ButtonCount + 1
                ReDim Preserve buttons(1 To ButtonCount)
                Set buttons(ButtonCount) = New Class1
                Set buttons(ButtonCount).ButtonGroup = Obj.Object
            End If
     Next Obj
End Sub

New bit is in red and I have removed the sheet iteration code as well (it wasn't required as you are only adding to the one sheet). I would test that the above Class_Init code works by running it a while after you have added the controls to the sheet.

jojohan · Jul 22, 2009

Hi & thank you for your time

There's no problem at all with the code which creates the buttons as well as with the original Class_Init. I don't think that setting Buttons(ButtonCount) as new Class1 could change anything since Buttons() is already declared as new Class1 in Andrew's code.

Debugging is not easy because I can't run step-by-step since VBE keeps telling me it can't enter in stopped mode (msg in french : "Impossible d'entrer en mode Arrêt maintenant")

Anyway, I tested your code and I confirm you it does all the same than the previous one. If Class_Init is run just after creation of buttons, it counts correctly but no button is working. So I manually (or by any other event like worksheet_change for example) start Class_Init a 2nd time and then it's all ok.

What do you mean exactly with

I would divorce the Class_Init from the code which creates the buttons

?

Since I start thinking this problem is a kind of VBA bug

that will never get solved, maybe someone could tell me about another way to get all these activeX control buttons calling the same function ? I can't manually write sub ButtonDetailsIx_Click() for all the buttons since I don't know in advance how many they are and what's their name.

Another idea is a 2nd call to Class_Init() on an event in sheet like worksheet_change. Problem with this one is that it requires an user action to be called and I want to avoid doing this.

So, any suggestion ?

Edit: XL2003 SP2

Richard Schollar · Jul 23, 2009

You need some kind of delay between when you physically add the buttons to the sheet and when you then add them to the Calss array (I believe this is a known issue with VBA and ActiveX controls - so you correct it is a bug). If you weren't physically addin the controls at runtime it wouldn't be an issue, but I presume there's no way you have the controls already added to the sheet?

bkh · Mar 5, 2012

Please help! I get an error on the following line?

Set shpCheckbox = ActiveSheet.Shapes(Application.Caller)

Application.Caller with Command Buttons on a sheet?

rbeeman

Board Regular

Excel Facts

Andrew Poulsom

MrExcel MVP

rbeeman

Board Regular

Andrew Poulsom

MrExcel MVP

rbeeman

Board Regular

jojohan

New Member

Richard Schollar

MrExcel MVP

jojohan

New Member

Richard Schollar

MrExcel MVP

bkh

New Member

Similar threads

Forum statistics

Share this page

Application.Caller with Command Buttons on a sheet?

Board Regular

Excel Facts

MrExcel MVP

Board Regular

MrExcel MVP

Board Regular

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock