button on excel cell

[NA_AB]

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 are:

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(searchButton_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

 

[Leith Ross]

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 are:

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(searchButton_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.CommandButton.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/)

 

[NA_AB]

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

 

[Peter T]

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]

try

OLEObject o1 = btn1.DrawingObject;

OMG!!! wow! hey Peter, thanks a billion dear friend!! I am a total novice
with no more than 40--45 days exp with c#, .net, excel addins etc... but
seriously, n if yu remember, i struggled hard to get the button and catch its
events!

Thanks a ton dude, it really helped and while deleting, there still was a
problem if i said o1.delete, o2.delete... so i jus removed the shapes
themselves and hence i had to retain the shapes (btn1, btn2) in my code!!


n hey, i had another qn: if yu can, please look into this and guide me thru,
a very important phase of my project!
here's d link
http://www.microsoft.com/communitie...0d-47bbdcc544c7&cat=&lang=&cr=&sloc=en-us&p=1

Once again, thanks a lot Peter!

Regards,
NA_AB

***************************************************

 

[Peter T]

Glad that worked but did you try the simpler suggestion.

I've just posted a response to your other thread, in effect the answer is
no.

For a complete novice in C#, .Net and the Excel object model you seem to be
doing pretty well. Perhaps when you've got your addin up and running you
might send me the C# project. One day I may need to do C# myself, no harm in
a head start !

Regards,
Peter T

 

[NA_AB]

with all due respect!

erm yes, the easier and simpler way yu suggested, well there s nt anythin
directly which is like, sht.OLEObjects.Add(..), yet there cud be a way to
skip the "shapes" in between, but as I said, I wud nehow need them(shapes) in
my delete routine.

Yes Peter, I did look at your response and thanks for yur views on it too...
will be looking forward to find a way if I can to crack that...

Once again, thank you Peter.

Regards,
na_ab

 

[Peter T]

with all due respect!

Normally that phrase precedes a "but", luckily there wasn't one !

erm yes, the easier and simpler way yu suggested, well there s nt anythin
directly which is like, sht.OLEObjects.Add(..), yet there cud be a way to
skip the "shapes" in between,

sht.OLEObjects.Add() does skip the using shapes, though it's no big deal
either way

but as I said, I wud nehow need them(shapes) in
my delete routine.

Don't see why, you already have the reference so simply
o1.delete

I did look at your response and thanks for yur views on it too...
will be looking forward to find a way if I can to crack that...

Really?
Do post back to the other thread if you get anywhere.

Regards,
Peter T

 

[NA_AB]

hey Peter, could you look into this please?

http://www.microsoft.com/communitie...63-0aba9921a8ed&cat=&lang=&cr=&sloc=en-us&p=1

Regards,
na_ab