Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8
Default Application.Caller with Command Buttons?

I'm trying ot 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?
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default Application.Caller with Command Buttons?

I'm not sure how the topleftcell and backcolor relate, but since you're in that
button's click procedure, you know what button started the macro:

Option Explicit
Private Sub CommandButton1_Click()
MsgBox Me.CommandButton1.TopLeftCell.Address
End Sub

or...

Option Explicit
Private Sub CommandButton1_Click()
MsgBox Me.CommandButton1.TopLeftCell.Address
Me.CommandButton1.BackColor = &HFF&
End Sub

RBee wrote:

I'm trying ot 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?


--

Dave Peterson
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8
Default Application.Caller with Command Buttons?

Yes, thank you. While I'm sure that code would work, my issue is that I have
several command buttons on several sheets, so I'm trying to write it
generically...so that I don't have unique code for each button, and can
simply refer all to same procedure.

"Dave Peterson" wrote:

I'm not sure how the topleftcell and backcolor relate, but since you're in that
button's click procedure, you know what button started the macro:

Option Explicit
Private Sub CommandButton1_Click()
MsgBox Me.CommandButton1.TopLeftCell.Address
End Sub

or...

Option Explicit
Private Sub CommandButton1_Click()
MsgBox Me.CommandButton1.TopLeftCell.Address
Me.CommandButton1.BackColor = &HFF&
End Sub

RBee wrote:

I'm trying ot 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?


--

Dave Peterson

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default Application.Caller with Command Buttons?

Since you're using commandbuttons from the control toolbox toolbar, aren't you
going through each button's _click event to call the common procedure?

Or have you set up some class module?

If you're going through the commandbutton's _click event, then just pass the
button to the common procedure.

Behind the worksheet:
Option Explicit
Private Sub CommandButton1_Click()
'do some setup?
Call CommonProc(Me.CommandButton1)
'do some clean up???
End Sub


In a general module:
Option Explicit
Sub CommonProc(myCMDBTN As MSForms.CommandButton)
If Rnd 0.5 Then
myCMDBTN.BackColor = &HFF&
Else
myCMDBTN.BackColor = &HFF00&
End If
End Sub

===
But if you're using commandbuttons from the control toolbox toolbar, you won't
be able to rightclick on them and assign a common macro.


RBee wrote:

Yes, thank you. While I'm sure that code would work, my issue is that I have
several command buttons on several sheets, so I'm trying to write it
generically...so that I don't have unique code for each button, and can
simply refer all to same procedure.

"Dave Peterson" wrote:

I'm not sure how the topleftcell and backcolor relate, but since you're in that
button's click procedure, you know what button started the macro:

Option Explicit
Private Sub CommandButton1_Click()
MsgBox Me.CommandButton1.TopLeftCell.Address
End Sub

or...

Option Explicit
Private Sub CommandButton1_Click()
MsgBox Me.CommandButton1.TopLeftCell.Address
Me.CommandButton1.BackColor = &HFF&
End Sub

RBee wrote:

I'm trying ot 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?


--

Dave Peterson


--

Dave Peterson
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Application.Caller Marcelo Excel Discussion (Misc queries) 6 June 23rd 06 03:07 PM


All times are GMT +1. The time now is 03:41 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"