Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
find the location of an activeX control
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
find the location of an activeX control
One way...
'-- Sub WhereAreYou() Dim x As Long x = ActiveSheet.Shapes("CommandButton1").TopLeftCell.C olumn 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
find the location of an activeX control
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" wrote: One way... '-- Sub WhereAreYou() Dim x As Long x = ActiveSheet.Shapes("CommandButton1").TopLeftCell.C olumn 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
find the location of an activeX control
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 "Jim Cone" wrote: One way... '-- Sub WhereAreYou() Dim x As Long x = ActiveSheet.Shapes("CommandButton1").TopLeftCell.C olumn 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
find the location of an activeX control
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 "Jim Cone" wrote: 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 "Jim Cone" wrote: One way... '-- Sub WhereAreYou() Dim x As Long x = ActiveSheet.Shapes("CommandButton1").TopLeftCell.C olumn 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2007 Form Control/ActiveX Control font difference | Excel Discussion (Misc queries) | |||
publish activeX combobox and other activeX control | Excel Programming | |||
Help with using an activex control | Excel Programming | |||
How to control "Date Time Picker ActiveX Control" | Excel Programming | |||
ActiveX control | Excel Programming |