locked
Style of Excel cell changing by default. RRS feed

  • Question

  • Hi,

     

    I have created an application level Addin for Excel 2007 using VSTO 3.0 and VS 2008.

    I have a requirement that i need to change the Style for few cells based on some condition.

    I am able to do that successfully. But one more thing happens after i do that.

    Whenever a user is trying to edit a cell which is just next to the cell which has its Style changed, the new cell also changes its Style to the one that the previous cell is changed to. This happens when the user tabs out of the new cell. This goes on continuously for all the cells in the same row.

    This is really crazy. I have tried to capture events like Application_SheetChange and Application_SheetSelectionChange but i found nothing that relates these events to the Style change.

     

    Following is the piece of code that sets the style

    Style inputStyle;

    inputStyle.Font.Name = "Cambria";
                    inputStyle.Font.Size = 11;
                    inputStyle.Font.Bold = true;
                    inputStyle.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                    inputStyle.VerticalAlignment = XlVAlign.xlVAlignBottom;
                    inputStyle.Interior.Color = System.Drawing.ColorTranslator.ToOle(ColorTranslator.FromHtml("#BEC39F"));

    range.Style = inputStyle;

    Can someone tell me at what are the possibilities that lead to this type of behavior.

    Thanks,

    Shail

    Thursday, July 15, 2010 4:34 PM

Answers

  • Excel 2007 has under Excel Options, Advanced, Editing Options "Extend data range formats and formulas".

    Try switching that off.

    • Marked as answer by Amy Li Thursday, July 22, 2010 9:09 AM
    Friday, July 16, 2010 7:29 AM
  • Hi Shailendra_G,


    As a supplement of my reply, I've modified the code so that there is no need to set the settings of the Excel manually. The code is written as:

     private void ThisAddIn_Startup(object sender, System.EventArgs e)
      {
    
       this.Application.ExtendList = false; //Add this line to set the Excel
       Excel.Range range1 = Application.get_Range("$A$1:$B$5");
       Excel.Style inputStyle = this.Application.ActiveWorkbook.Styles.Add("new style",missing);
       inputStyle.Font.Name = "Cambria";
       inputStyle.Font.Size = 11;
       inputStyle.Font.Bold = true;
       inputStyle.VerticalAlignment = Excel.XlVAlign.xlVAlignBottom;
       inputStyle.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.ColorTranslator.FromHtml("#BEC39F"));
       range1.Style = inputStyle;
      }

    Since the property of extend in Excel is by default chosen, so you can edit the code to control that then it will be ok. Any further question, please feel free to contact. Thank you!


    Best Regards,
    Amy Li


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Marked as answer by Amy Li Thursday, July 22, 2010 9:09 AM
    Thursday, July 22, 2010 1:48 AM

All replies

  • Excel 2007 has under Excel Options, Advanced, Editing Options "Extend data range formats and formulas".

    Try switching that off.

    • Marked as answer by Amy Li Thursday, July 22, 2010 9:09 AM
    Friday, July 16, 2010 7:29 AM
  • Hi Shail,

    First, I managed to reproduce the problem. Initially I tried to setup the style on one cell only (A1), and couldn't reproduce it. Then I styled a range spanning a few columns, and got the same problem: when you fill in the styled range, and keep on the right, the formatting extends.

    It seems to me that this is a feature of Excel, which you get also without automation: if you use Styles manually on a range spanning multiple columns, Excel will "infer" that you want to extend that. In the process, it can do some bizarre stuff: if you apply a Style to a range, say A1:D1, and start filling it in D1, then E1, F1 and G1, E1 and F1 will have no style, but all cells after (G1, H1 etc...) will be styled.

    Edited: thx incre-d for the tip, didn't know about that.

    Friday, July 16, 2010 7:36 AM
  • Hi Shailendra_G,

    After reading your question, I've reproduced the program on my computer. Just as you say, the issue occurred and I found that the problem may be not relative to the code but caused by the setting of Excel. (Just as incre-d said). I've made the program on VS2010 and Excel2010. Here is the code:

     private void ThisAddIn_Startup(object sender, System.EventArgs e)
        {
    
          Excel.Range range1 = Application.get_Range("$A$1:$B$5");
          Excel.Style inputStyle = this.Application.ActiveWorkbook.Styles.Add("new style",missing);
          inputStyle.Font.Name = "Cambria";
          inputStyle.Font.Size = 11;
          inputStyle.Font.Bold = true;
          inputStyle.VerticalAlignment = Excel.XlVAlign.xlVAlignBottom;
          inputStyle.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.ColorTranslator.FromHtml("#BEC39F"));
          range1.Style = inputStyle;
        }

    In my opinion, I think your code is correct too. To solve the problem,  I've changed the setting of Excel. The procedure is:
    1. Open the Excel Options and Choose the Advanced Tab.
    2. In Editing Options, find the Item "Extend data range formats and formulas"
    3. If it is marked, try to cancel the hook option.
    I've tested it on my computer, it works fine. Please try that, if you still encounter the issue, feel free to contact. Thank you!

    Best Regards,
    Amy Li


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Wednesday, July 21, 2010 3:02 AM
  • Hi Shailendra_G,


    As a supplement of my reply, I've modified the code so that there is no need to set the settings of the Excel manually. The code is written as:

     private void ThisAddIn_Startup(object sender, System.EventArgs e)
      {
    
       this.Application.ExtendList = false; //Add this line to set the Excel
       Excel.Range range1 = Application.get_Range("$A$1:$B$5");
       Excel.Style inputStyle = this.Application.ActiveWorkbook.Styles.Add("new style",missing);
       inputStyle.Font.Name = "Cambria";
       inputStyle.Font.Size = 11;
       inputStyle.Font.Bold = true;
       inputStyle.VerticalAlignment = Excel.XlVAlign.xlVAlignBottom;
       inputStyle.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.ColorTranslator.FromHtml("#BEC39F"));
       range1.Style = inputStyle;
      }

    Since the property of extend in Excel is by default chosen, so you can edit the code to control that then it will be ok. Any further question, please feel free to contact. Thank you!


    Best Regards,
    Amy Li


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    • Marked as answer by Amy Li Thursday, July 22, 2010 9:09 AM
    Thursday, July 22, 2010 1:48 AM