none
Delete the checkbox after save and reopen of the Excel sheet programatically.... RRS feed

  • Question

  • Hi,

    I have created checkboxes programatically after populating the data on sheet depending upon the number of rows population.Now save the excel sheet and reopen it.On button click I am removing the data and checkboxes from the sheet,but the data is getting cleared but the checkboxes are not getting moved.I found that this.Controls.Count is 0 after reopening the sheet.Please help me in resolving  this problem asap.Please tell me how to delete the checkboxes after reopening the excel.

    Thanks in advance.

    Thanks,

    Meenakshi Nagpal


    N.Meenakshi

    Monday, January 28, 2013 1:08 PM

Answers

  • Hi Meenakshi Nagpal,

    Thank you for your last reply.

    In reply to your original question:

    I cannot reproduce your scenario. I've tried

    this.Controls.AddControl()

    to add controls to my worksheet. After that I have code in button click event handler to popup messagebox which shows the controls.Count and I can always get the right result.

    In reply to your second question:

    You can take a look at ControlSite which represents a linked or embedded control on a worksheet. 

    this.Controls.AddControl()

    returns a ControlSite object and you can set the placement property of it.

    Sample code seen as below.

    Microsoft.Office.Tools.Excel.Controls.CheckBox checkBox 
        = new Microsoft.Office.Tools.Excel.Controls.CheckBox();
    Microsoft.Office.Tools.Excel.ControlSite embededControl;
    embededControl = this.Controls.AddControl();
    embededControl.Placement = Excel.XlPlacement.xlMove;

    Hope it helps.

    Best regards,


    Quist Zhang [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, January 29, 2013 11:51 AM
    Moderator
  • Hi Meenakshi Nagpal,

    You are working on a Workbook Customization.

    You have code in your ThisWorkbook_Startup or Sheet_Startup which mean that when you open this workbook, the code will be executed.

    You can make a experiment by follow the below steps.

    1. Create a workbook project.
    2. Compile it and write something on a sheet1. Save and close.
    3. Re-compile the project. You'll see the words there.
    4. Add something simple in the sheet1_Startup event handler. ex. MessageBox.Show("Hello World.");
    5. Compile it and then write something on a sheet1. Save and close.
    6. Re-compile the project. 

    I think you can create a ribbon tab with buttons on it, and add code that generate checkbox in the buttons' click event handler.

    Hope it helps.

    Best regards,


    Quist Zhang [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    Tuesday, January 29, 2013 12:46 PM
    Moderator

All replies

  • Hi Meenakshi Nagpal,

    Thank you for posting in the MSDN Forum.

    What kind of project are you working with? 

    Could you please show me the code that adds the checkboxes?

    I look forward to your reply.

    Best regards,


    Quist Zhang [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, January 29, 2013 6:54 AM
    Moderator
  • Hi Quist Zhang,

    Thanks for your reply.Bleow is the code which I am using to create the checkboxes.

     if (!this.Controls.Contains("chkBox" + i.ToString()))
                    {
                        Microsoft.Office.Tools.Excel.Controls.CheckBox chkBox = new Microsoft.Office.Tools.Excel.Controls.CheckBox();
                        var cell = this.get_Range("$A$" + i, this.missing);
                        chkBox.Name = "chkBox" + i.ToString();
                        this.Controls.AddControl(chkBox, Convert.ToDouble(cell.Left) + 5, Convert.ToDouble(cell.Top) + 10, 10, 10, "chkBox" + i.ToString());
                        //chkBox.Placement = XlPlacement.xlMove;
                        chkBox.CheckedChanged += chkBox_CheckedChanged;
                    }

    here i is the number of cell which which I am going to add the checkbox.

    Thanks,

    Meenakshi Nagpal


    N.Meenakshi

    Tuesday, January 29, 2013 7:10 AM
  • chkBox.Placement = XlPlacement.xlMove;

    when I was trying to set the placement peoperty it is thorwing object reference not set to instance of an object please help me in resolving this issue also.

    I am using excelworkbook project in VS 2012.

    Thanks,

    Meenakshi Nagpal


    N.Meenakshi

    Tuesday, January 29, 2013 7:12 AM
  • Hi Meenakshi Nagpal,

    Thank you for your last reply.

    In reply to your original question:

    I cannot reproduce your scenario. I've tried

    this.Controls.AddControl()

    to add controls to my worksheet. After that I have code in button click event handler to popup messagebox which shows the controls.Count and I can always get the right result.

    In reply to your second question:

    You can take a look at ControlSite which represents a linked or embedded control on a worksheet. 

    this.Controls.AddControl()

    returns a ControlSite object and you can set the placement property of it.

    Sample code seen as below.

    Microsoft.Office.Tools.Excel.Controls.CheckBox checkBox 
        = new Microsoft.Office.Tools.Excel.Controls.CheckBox();
    Microsoft.Office.Tools.Excel.ControlSite embededControl;
    embededControl = this.Controls.AddControl();
    embededControl.Placement = Excel.XlPlacement.xlMove;

    Hope it helps.

    Best regards,


    Quist Zhang [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, January 29, 2013 11:51 AM
    Moderator
  • Hi Thanks for your reply..

    When I run the application it works fine.But when I save and reopen the excel the count is not the one which I got at the time of creating.I have added the list object also to bind the data to sheet when I save and reopen the sheet and if I checked for listobject it says it's not there.Please help me in resolving this issue.Save and reopen the sheet then this issue is coming.

    Thanks,

    Meenakshi Nagpal


    N.Meenakshi

    Tuesday, January 29, 2013 11:57 AM
  • Hi Meenakshi Nagpal,

    You are working on a Workbook Customization.

    You have code in your ThisWorkbook_Startup or Sheet_Startup which mean that when you open this workbook, the code will be executed.

    You can make a experiment by follow the below steps.

    1. Create a workbook project.
    2. Compile it and write something on a sheet1. Save and close.
    3. Re-compile the project. You'll see the words there.
    4. Add something simple in the sheet1_Startup event handler. ex. MessageBox.Show("Hello World.");
    5. Compile it and then write something on a sheet1. Save and close.
    6. Re-compile the project. 

    I think you can create a ribbon tab with buttons on it, and add code that generate checkbox in the buttons' click event handler.

    Hope it helps.

    Best regards,


    Quist Zhang [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    Tuesday, January 29, 2013 12:46 PM
    Moderator