Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Application.Caller | Excel Discussion (Misc queries) |