find the location of an activeX control

[hjc]

Is there a way to locate an ActiveX control (specifically, a button) in a
spreadsheet, i.e., to find out what column it is in? I can figure it out if
the control has a linked cell that I can put in the same column, but that
doesn't work for a button. Although the 'location' of the left edge of the
button is a property of the button, it doesn't seem to correlate to pixels
(that could be compared against the sum of the widths of the columns to the
left of the control, for example) or anything else, as far as I can tell.

I am trying to create a series of buttons, each of which will carry out the
same action on the column in which it resides. I am using Excel 2003.

Thanks for whatever help anybody can provide!
Hugh John

 

[Jim Cone]

One way...
'--
Sub WhereAreYou()
Dim x As Long
x = ActiveSheet.Shapes("CommandButton1").TopLeftCell.Column
MsgBox x
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


"hjc"
wrote in message
Is there a way to locate an ActiveX control (specifically, a button) in a
spreadsheet, i.e., to find out what column it is in? I can figure it out if
the control has a linked cell that I can put in the same column, but that
doesn't work for a button. Although the 'location' of the left edge of the
button is a property of the button, it doesn't seem to correlate to pixels
(that could be compared against the sum of the widths of the columns to the
left of the control, for example) or anything else, as far as I can tell.

I am trying to create a series of buttons, each of which will carry out the
same action on the column in which it resides. I am using Excel 2003.

Thanks for whatever help anybody can provide!
Hugh John

 

[hjc]

Thanks, Jim -- that certainly gets me started! Now all I need to know is how
to find out which button was clicked to get me into that routine, so I don't
have to hard-code the name of the button. Is there an easy way to do that?
Thanks!

Hugh John

 

[Jim Cone]

Private Sub CommandButton1_Click()
Call WhereAreYou(Me.CommandButton1.Name)
End Sub


Sub WhereAreYou(ByRef sButt As String)
Dim x As Long
x = Me.Shapes(sButt).TopLeftCell.Column
MsgBox sButt & " is in column " & x & " "
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"hjc"
wrote in message
Thanks, Jim -- that certainly gets me started! Now all I need to know is how
to find out which button was clicked to get me into that routine, so I don't
have to hard-code the name of the button. Is there an easy way to do that?
Thanks!
Hugh John

 

[hjc]

Thanks, Jim -- that's got it! (Although, I had to use 'ActiveSheet' instead
of 'Me' inside Sub WhereAreYou...as you did in your first post.)

Thanks again!
hjc