none
Is it possible to change a cell style without applying the style? RRS feed

  • Question

  • Hello,

    I should only add the fact that I'm using VBA.

    If it is not possible, this can work for me: is it possible to set a cell's Style by changing its Value(xlRangeValueXMLSpreadsheet) property?

    Thanks.


    Felipe Costa Gualberto - http://www.ambienteoffice.com.br


    Tuesday, June 23, 2015 12:47 PM

Answers

  • Hi Felipe,

    No, as far as I know, it would change the style name if we change the cell value with xlRangeValueXMLSpreadsheet property.

    However, we could apply the style to the cell, so, you could apply the style to the target cell to achieve your requirement.

    Simple sample:

    Sub Main()
        Dim s As String
        Range("A1").Value2 = Range("B3").Value(xlRangeValueXMLSpreadsheet)
        s = Range("B3").Style.Name
        Range("B5").Value(xlRangeValueXMLSpreadsheet) = Range("A1").Value2
        Range("B5").Style = s
        Range("B4").Value2 = "Style: " & Range("B5").Style.Name
        Range("B1").Value2 = Range("B5").Value(xlRangeValueXMLSpreadsheet)
    End Sub

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, June 26, 2015 3:18 AM
    Moderator

All replies

  • Re: "change a style without applying the style"

      Create a new style instead.

    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Edited by James Cone Monday, October 31, 2016 11:34 PM
    Tuesday, June 23, 2015 1:27 PM
  • James, I didn't understand you suggestion.

    My scenario:

    Cell A1 is formatted with a style called MyStyle. I store its .Value(xlRangeValueXMLSpreadsheet) property in  the varible xmlValue. I process this variable. Next, I set cell A1 .Value(xlRangeValueXMLSpreadsheet) property back to xmlValue, but the style changes to Normal. I want either to mantian MyStyle or change xmoVariable to make it back to the original style.


    Felipe Costa Gualberto - http://www.ambienteoffice.com.br

    Tuesday, June 23, 2015 1:34 PM
  • Re:  changing a cells value

    I answered a question you did not ask.
    '---

    You do not have to specify the data type for the Value property.
      Range("D10").Value = "Sludge"
    -or-
      Range("D10").Value(xlRangeValueDefault) = "Sludge"

    Either of the above, will not change the cell style.

    '---
    Jim Cone
    Tuesday, June 23, 2015 2:24 PM
  • When the argument is xlRangeValueDefault, it doesn't change cell's style. However, doing this quick test, changed it:

    Set a style other than Normal at cell B3 and type a text.

    Run this macro:

    Sub Main()
        Range("A1").Value2 = Range("B3").Value(xlRangeValueXMLSpreadsheet)
        Range("B3").Value(xlRangeValueXMLSpreadsheet) = Range("A1").Value2
    End Sub

    The style shouldn't change, but it changes.

    ---

    Edit: I should add that I have to use xlRangeValueXMLSpreadsheet because I'm customizing cell's text formatting.


    Felipe Costa Gualberto - http://www.ambienteoffice.com.br



    Tuesday, June 23, 2015 3:14 PM
  • Re:  format changes to worksheet cells.

    We may by in two different environments...
    I am using xl2010 on a .xlsx worksheet.  The style does not change in cell B3 when using your "main" code.

    If you want to change the text formatting in a cell, the following changes characters 460 to 469 to bold without changing the cell style...
      Range("D5").Characters(460, 10).Font.Bold = True

    '---
    Jim Cone
     
    Tuesday, June 23, 2015 6:00 PM
  • Looks like my Excel has some problem - other guys tested my script and the style didn't change. I'm using Win 8.1 64 bits, both Excel 2010/2013 32 bits.

    About your suggestion using Range.Characters: I was using it before, but I got struggled with the situation where I have more than 256 characters in a cell. In this situation, the method Characters.Delete doesn't work.


    Felipe Costa Gualberto - http://www.ambienteoffice.com.br

    Tuesday, June 23, 2015 9:04 PM
  • Hi Felipe,

    Base on my test with excel 2013 64bit (windows 8.1 64 bit) and excel 2010 64 bit (windows 7 64bit), both of them work fine. Could you reproduce that issue in other environment?

    You may share a sample file on the OneDrive, we will check it whether the issue is in the file.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, June 24, 2015 2:23 AM
    Moderator
  • I just tested in my home PC the same scenario in a similar setup (Windows 8.1 64, both Office 32 bits 2010/2013) and the style got changed:

    Sample workbook: https://onedrive.live.com/redir?resid=FB206A2D510E0661%21200815

    After I run the macro, I get:


    Felipe Costa Gualberto - http://www.ambienteoffice.com.br

    Wednesday, June 24, 2015 10:51 AM
  • Hi Felipe,

    Base on my test with your file, yes the name of style has been changed, but the style doesn’t changed.

    This xml code of B3 that not modified:

      <Style ss:ID="s30" ss:Name="Neutral">
    
       <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#9C6500"/>
    
       <Interior ss:Color="#FFEB9C" ss:Pattern="Solid"/>
    
      </Style>
    
      <Style ss:ID="s66" ss:Parent="s30">
    
       <Alignment ss:Vertical="Bottom"/>
    
       <Borders/>
    
       <NumberFormat/>
    
       <Protection/>
    
      </Style>
    
     </Styles>
    
     <Worksheet ss:Name="Plan1">
    
      <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1"
    
       ss:DefaultRowHeight="15">
    
       <Column ss:AutoFitWidth="0" ss:Width="339.75"/>
    
       <Row>
    
        <Cell ss:StyleID="s66"><Data ss:Type="String">Test</Data></Cell>
    
       </Row>
    
      </Table>
    

    The cell’s style id is s66 and the style of s66 has the parent style of s30.

    Then for this code of B3 that be modified.

    <Style ss:ID="s90">
    
       <Alignment ss:Vertical="Bottom"/>
    
       <Borders/>
    
       <Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11" ss:Color="#993300"/>
    
       <Interior ss:Color="#FFEB9C" ss:Pattern="Solid"/>
    
       <NumberFormat/>
    
       <Protection/>
    
      </Style>
    
     </Styles>
    
     <Worksheet ss:Name="Plan1">
    
      <Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1"
    
       ss:DefaultRowHeight="15">
    
       <Column ss:AutoFitWidth="0" ss:Width="339.75"/>
    
       <Row>
    
        <Cell ss:StyleID="s90"><Data ss:Type="String">Test</Data></Cell>
    
       </Row>
    
      </Table>
    

    It merges the styles to a style without style name.

    For your requirement, I suggest that you could through Range.Copy method to achieve that.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, June 25, 2015 4:11 AM
    Moderator
  • Thank you Starain. I'm afraid that I expressed myself wrong.

    It is exactly what happens: the formatting is retained, but the name of Style changes, as it happens to you. However, I need to retain the Style name because I do lots of automation in a process that uses Style name as anchor.

    So, any of the solutions below would work for me:

    1 - Is it possible to change the Style name (other than Normal) of a cell by changing its Value(xlRangeValueXMLSpreadsheet) property?

    2 - Is it possible to not change the Style name of a cell by changing its Value(xlRangeValueXMLSpreadsheet) property?



    Felipe Costa Gualberto - http://www.ambienteoffice.com.br

    Thursday, June 25, 2015 1:32 PM
  • Hi Felipe,

    No, as far as I know, it would change the style name if we change the cell value with xlRangeValueXMLSpreadsheet property.

    However, we could apply the style to the cell, so, you could apply the style to the target cell to achieve your requirement.

    Simple sample:

    Sub Main()
        Dim s As String
        Range("A1").Value2 = Range("B3").Value(xlRangeValueXMLSpreadsheet)
        s = Range("B3").Style.Name
        Range("B5").Value(xlRangeValueXMLSpreadsheet) = Range("A1").Value2
        Range("B5").Style = s
        Range("B4").Value2 = "Style: " & Range("B5").Style.Name
        Range("B1").Value2 = Range("B5").Value(xlRangeValueXMLSpreadsheet)
    End Sub

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, June 26, 2015 3:18 AM
    Moderator
  • Hi Starain,

    The problem is that in my case, I'll have a partially formatted text inside the cell (like Starain Chen). If I a apply the style, I'll lose all internal formatting.

    I didn't like the fact that the Style changes if I change a cell's Value property, but at least now I'm sure this is the default behavior.

    In any case, I got a workaround for my specific problem in this very same forum.

    Thank you for your answers and have a good day.


    Felipe Costa Gualberto - http://www.ambienteoffice.com.br

    Friday, June 26, 2015 10:04 AM