does excel know in which cell a button is located?
 

Hi,

I want to add a row by clicking a button. The row must be added just above
the button. So a row must be added on a position relative to the button.
For this i should be able to know the position of the button: in which cell
is the button or on which cell postion is it bound?
Does anyone know how to achieve this?

Thanks, Jim M

does excel know in which cell a button is located?
 

Hi Jim,

If the buttons are fro the Forms toolbar, assign them to the following
macro:

'=============
Public Sub InsertRow()
ActiveSheet.Buttons(Application.Caller). _
TopLeftCell.EntireRow.Insert
End Sub

'<<=============

If the buttons are from the Control Toolbox, try:

'=============
Private Sub CommandButton1_Click()
Me.CommandButton1.TopLeftCell.EntireRow.Insert
End Sub
<<=============


---
Regards,
Norman



"Jim" wrote in message
...
Hi,

I want to add a row by clicking a button. The row must be added just above
the button. So a row must be added on a position relative to the button.
For this i should be able to know the position of the button: in which
cell
is the button or on which cell postion is it bound?
Does anyone know how to achieve this?

Thanks, Jim M

does excel know in which cell a button is located?
 

Jim,

Use a button from the Forms toolbar.
The button property should be set to "move... with cells".
In the code below, it is assumed that the button
is named "Button 1".
'------------------------
Sub MoveEmOut()
Dim shpButton As Excel.Shape
Set shpButton = ActiveSheet.Shapes("Button 1")
shpButton.TopLeftCell.EntireRow.Insert
Set shpButton = Nothing
End Sub
'-----------------------------

Of course, you can just right-click any row heading
and choose "insert" to achieve the same thing.

Regards,
Jim Cone
San Francisco, USA



"Jim" wrote in message

Hi,
I want to add a row by clicking a button. The row must be added just above
the button. So a row must be added on a position relative to the button.
For this i should be able to know the position of the button: in which cell
is the button or on which cell postion is it bound?
Does anyone know how to achieve this?
Thanks,
Jim M

does excel know in which cell a button is located?
 

Thanks Jim and Norman. This works!


*** Sent via Developersdex http://www.developersdex.com ***

does excel know in which cell a button is located?
 

On Fri, 7 Oct 2005 15:05:34 -0700, Jim Cone wrote:

Dim shpButton As Excel.Shape
Set shpButton = ActiveSheet.Shapes("Button 1")
shpButton.TopLeftCell.EntireRow.Insert
Set shpButton = Nothing

Why do you do this in four lines when it can simply be done in one?, i.e.

ActiveSheet.Shapes("Button 1").TopLeftCell.EntireRow.Insert

Don <www.donwiss.com (e-mail link at home page bottom).

does excel know in which cell a button is located?
 

I can't speak for Jim, but his approach provided not only a solution, but
also instructed the OP on which object was being used thus further fostering
independent exploration as to the properties and methods of that object.
While not explicitly stated, it sets a foundation for continued actions with
the shape or perhaps passing a reference to the button to another procedure.

Also, if the OP tried typing in the suggestion, if a typo were made, it is
much more difficult to debug a single complex multi-qualifier line.

But showing the OP alternatives is useful as well.

--
Regards,
Tom Ogilvy



"Don Wiss" wrote in message
...
On Fri, 7 Oct 2005 15:05:34 -0700, Jim Cone
wrote:

Dim shpButton As Excel.Shape
Set shpButton = ActiveSheet.Shapes("Button 1")
shpButton.TopLeftCell.EntireRow.Insert
Set shpButton = Nothing

Why do you do this in four lines when it can simply be done in one?, i.e.

ActiveSheet.Shapes("Button 1").TopLeftCell.EntireRow.Insert

Don <www.donwiss.com (e-mail link at home page bottom).