Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
to add a button to the active excel cell! here's the way! | Excel Programming | |||
a button on an excel cell | Excel Programming | |||
Linking a VBA button to Excel cell | Excel Programming | |||
Excel - create button to replace cell content with cell value | Excel Worksheet Functions | |||
does excel know in which cell a button is located? | Excel Programming |