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