button on excel cell
 

hey friends, now that am able to create as many number of buttons and catch
their events accordingly on any cell location in the excel sheet, i am facing
a problem when am trying to delete these buttons.

in my code, i have these statements:

1) a 'search' button to search data and show it.
2) a 'delete' button to delete
(i) the data,
(ii) the search button and
(iii) the delete button itself finally...

and the statements to add buttons a

Shape btn1 = sht.Shapes.AddOLEObject("Forms.CommandButton.1",
Missing.Value, Missing.Value,
Missing.Value, Missing.Value,
Missing.Value, Missing.Value,
sht.get_Range(act, act).Left,
sht.get_Range(act, act).Top,
sht.get_Range(act, act).Width,
((double)sht.get_Range(act, act).
Height) * 2);

OLEObject o1 = (OLEObject)(sht.OLEObjects("CommandButton" + k));

MSForms.CommandButton mbtn_1 = (MSForms.CommandButton)(o1.Object);

mbtn_1.Caption = "search";
mbtn_1.Click += new
MSForms.CommandButtonEvents_ClickEventHandler(sear chButton_Click);

and so on...

and to delete these, am saying:

o1.Delete(); //to delete 'search' button
o2.Delete(); //to delete 'delete' button

In the code, "k" is a static int which keeps incrementing with every button
added.

Now, when am trying to delete, it is deleting properly but again if am
trying to add a button, it is resulting in a weird behavour, the controls of
one button are going to another button, the reason for which i assume is the
statement:

OLEObject o1 = (OLEObject)(sht.OLEObjects("CommandButton" + k));

If am trying to change "CommandButton"+k to any other name, my button
doesn't even respond to anything.

What do I do about this? How will i be able to delete and add button as and
when required?

Thanks, in advance

Regards,
NA_AB

button on excel cell
 

NA_AB;205853 Wrote:
hey friends, now that am able to create as many number of buttons and
catch
their events accordingly on any cell location in the excel sheet, i am
facing
a problem when am trying to delete these buttons.

in my code, i have these statements:

1) a 'search' button to search data and show it.
2) a 'delete' button to delete
(i) the data,
(ii) the search button and
(iii) the delete button itself finally...

and the statements to add buttons a

Shape btn1 = sht.Shapes.AddOLEObject("Forms.CommandButton.1",
Missing.Value, Missing.Value,
Missing.Value, Missing.Value,
Missing.Value, Missing.Value,
sht.get_Range(act, act).Left,
sht.get_Range(act, act).Top,
sht.get_Range(act, act).Width,
((double)sht.get_Range(act, act).
Height) * 2);

OLEObject o1 = (OLEObject)(sht.OLEObjects("CommandButton" + k));

MSForms.CommandButton mbtn_1 = (MSForms.CommandButton)(o1.Object);

mbtn_1.Caption = "search";
mbtn_1.Click += new
MSForms.CommandButtonEvents_ClickEventHandler(sear chButton_Click);

and so on...

and to delete these, am saying:

o1.Delete(); //to delete 'search' button
o2.Delete(); //to delete 'delete' button

In the code, "k" is a static int which keeps incrementing with every
button
added.

Now, when am trying to delete, it is deleting properly but again if am
trying to add a button, it is resulting in a weird behavour, the
controls of
one button are going to another button, the reason for which i assume
is the
statement:

OLEObject o1 = (OLEObject)(sht.OLEObjects("CommandButton" + k));

If am trying to change "CommandButton"+k to any other name, my button
doesn't even respond to anything.

What do I do about this? How will i be able to delete and add button as
and
when required?

Thanks, in advance

Regards,
NA_AB

Hello NA_AB,

Here are 2 macros that create and delete a command button (Control
Toolbox type) on the active sheet.

Code:
--------------------

Sub AddCmdBtn()

Dim CmdBtn As Object

Set CmdBtn = ActiveSheet.OLEObjects.Add(ClassType:="Forms.Comma ndButton.1", _
Left:=ActiveCell.Left, _
Top:=ActiveCell.Top, _
Width:=ActiveCell.Width * 2, _
Height:=ActiveCell.Height * 2)

CmdBtn.Name = "TestButton 1"

End Sub

Sub DeleteCmdBtn()
ActiveSheet.OLEObjects("TestButton 1").Delete
End Sub

--------------------


--
Leith Ross

Sincerely,
Leith Ross

'The Code Cage' (http://www.thecodecage.com/)
------------------------------------------------------------------------
Leith Ross's Profile: http://www.thecodecage.com/forumz/member.php?userid=75
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=56537

button on excel cell
 

hey Ross, good to see yu looked into my problem, however am actually building
an excel COM addin using C# .net, and i do not want to make use of any
macros, i need a c# code that can serve the purpose. And as I said, I am not
dealing with a single button. I should be able to add and remove any number
of buttons.

Regards,
na_ab

"Leith Ross" wrote:


NA_AB;205853 Wrote:
hey friends, now that am able to create as many number of buttons and
catch
their events accordingly on any cell location in the excel sheet, i am
facing
a problem when am trying to delete these buttons.

in my code, i have these statements:

1) a 'search' button to search data and show it.
2) a 'delete' button to delete
(i) the data,
(ii) the search button and
(iii) the delete button itself finally...

and the statements to add buttons a

Shape btn1 = sht.Shapes.AddOLEObject("Forms.CommandButton.1",
Missing.Value, Missing.Value,
Missing.Value, Missing.Value,
Missing.Value, Missing.Value,
sht.get_Range(act, act).Left,
sht.get_Range(act, act).Top,
sht.get_Range(act, act).Width,
((double)sht.get_Range(act, act).
Height) * 2);

OLEObject o1 = (OLEObject)(sht.OLEObjects("CommandButton" + k));

MSForms.CommandButton mbtn_1 = (MSForms.CommandButton)(o1.Object);

mbtn_1.Caption = "search";
mbtn_1.Click += new
MSForms.CommandButtonEvents_ClickEventHandler(sear chButton_Click);

and so on...

and to delete these, am saying:

o1.Delete(); //to delete 'search' button
o2.Delete(); //to delete 'delete' button

In the code, "k" is a static int which keeps incrementing with every
button
added.

Now, when am trying to delete, it is deleting properly but again if am
trying to add a button, it is resulting in a weird behavour, the
controls of
one button are going to another button, the reason for which i assume
is the
statement:

OLEObject o1 = (OLEObject)(sht.OLEObjects("CommandButton" + k));

If am trying to change "CommandButton"+k to any other name, my button
doesn't even respond to anything.

What do I do about this? How will i be able to delete and add button as
and
when required?

Thanks, in advance

Regards,
NA_AB

Hello NA_AB,

Here are 2 macros that create and delete a command button (Control
Toolbox type) on the active sheet.

Code:
--------------------

Sub AddCmdBtn()

Dim CmdBtn As Object

Set CmdBtn = ActiveSheet.OLEObjects.Add(ClassType:="Forms.Comma ndButton.1", _
Left:=ActiveCell.Left, _
Top:=ActiveCell.Top, _
Width:=ActiveCell.Width * 2, _
Height:=ActiveCell.Height * 2)

CmdBtn.Name = "TestButton 1"

End Sub

Sub DeleteCmdBtn()
ActiveSheet.OLEObjects("TestButton 1").Delete
End Sub

--------------------


--
Leith Ross

Sincerely,
Leith Ross

'The Code Cage' (http://www.thecodecage.com/)
------------------------------------------------------------------------
Leith Ross's Profile: http://www.thecodecage.com/forumz/member.php?userid=75
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=56537

button on excel cell
 

Instead of
OLEObject o1 = (OLEObject)(sht.OLEObjects("CommandButton" + k));

try
OLEObject o1 = btn1.DrawingObject;

or even simpler
OLEObject o1 = sht.OLEObjects.Add("Forms.CommandButton.1", etc


(I suspect I've got the C# syntax wrong but something like that should work)

Regards,
Peter T

"NA_AB" wrote in message
...
hey friends, now that am able to create as many number of buttons and
catch
their events accordingly on any cell location in the excel sheet, i am
facing
a problem when am trying to delete these buttons.

in my code, i have these statements:

1) a 'search' button to search data and show it.
2) a 'delete' button to delete
(i) the data,
(ii) the search button and
(iii) the delete button itself finally...

and the statements to add buttons a

Shape btn1 = sht.Shapes.AddOLEObject("Forms.CommandButton.1",
Missing.Value, Missing.Value,
Missing.Value, Missing.Value,
Missing.Value, Missing.Value,
sht.get_Range(act, act).Left,
sht.get_Range(act, act).Top,
sht.get_Range(act, act).Width,
((double)sht.get_Range(act, act).
Height) * 2);

OLEObject o1 = (OLEObject)(sht.OLEObjects("CommandButton" + k));

MSForms.CommandButton mbtn_1 = (MSForms.CommandButton)(o1.Object);

mbtn_1.Caption = "search";
mbtn_1.Click += new
MSForms.CommandButtonEvents_ClickEventHandler(sear chButton_Click);

and so on...

and to delete these, am saying:

o1.Delete(); //to delete 'search' button
o2.Delete(); //to delete 'delete' button

In the code, "k" is a static int which keeps incrementing with every
button
added.

Now, when am trying to delete, it is deleting properly but again if am
trying to add a button, it is resulting in a weird behavour, the controls
of
one button are going to another button, the reason for which i assume is
the
statement:

OLEObject o1 = (OLEObject)(sht.OLEObjects("CommandButton" + k));

If am trying to change "CommandButton"+k to any other name, my button
doesn't even respond to anything.

What do I do about this? How will i be able to delete and add button as
and
when required?

Thanks, in advance

Regards,
NA_AB

button on excel cell
 

hey Peter, could you look into this please?

http://www.microsoft.com/communities...sloc=en-us&p=1

Regards,
na_ab