none
How to know if numberFormat will be applied in Conditional Rules ? RRS feed

  • Question

  • All,

    I want to know if the property "numberFormat" will be applied if a conditional rule is verified.

    To check if all other properties will be applied, it is possible to compare the property with "Null" but it does not work with "numberFormat"

    Here are source code and ouput:

    Source Code

    Sub TestNumberFormat()
       
       Dim wb As Workbook
       Dim ws As Worksheet
       
       Dim writtenFc As FormatCondition
       
       Dim readFc1 As FormatCondition
       Dim readFc2 As FormatCondition
    
       Set wb = Workbooks("wb.xlsx")
       Set ws = wb.Sheets("Sheet1")
       
       ws.Cells.FormatConditions.Delete
       
       ' Create first format condition
       Set writtenFc = _
          ws.Range("A1").FormatConditions.Add( _
                 xlExpression, _
                 xlNone, _
                 True)
       writtenFc.Font.Bold = True
          
       ' Create second format condition
       Set writtenFc = _
           ws.Range("B2").FormatConditions.Add( _
                 xlExpression, _
                 xlNone, _
                 True)
       writtenFc.NumberFormat = "Standard"
    
    
       ' Read properties format condition
       Set readFc1 = ws.Cells.FormatConditions(1)
       Set readFc2 = ws.Cells.FormatConditions(2)
          
          
       Debug.Print "fc1 Numberformat = " + _
          readFc1.NumberFormat
       Debug.Print "fc2 NumberFormat = " + _
          readFc2.NumberFormat
          
       If (IsNull(readFc1.Font.Bold)) Then
          Debug.Print "fc1 Bold property will not be applied"
       Else
          Debug.Print "fc1 Bold         = " + _
             CStr(readFc1.Font.Bold)
       End If
       If (IsNull(readFc2.Font.Bold)) Then
          Debug.Print "fc2 Bold property will not be applied"
       Else
          Debug.Print "fc2 Bold         = " + _
             CStr(readFc2.Font.Bold)
       End If
       
    End Sub

    Output

    fc1 Numberformat = Standard
    fc2 NumberFormat = Standard
    fc1 Bold         = Vrai
    fc2 Bold property will not be applied

    Thanks for your help,

    ic0


    • Edited by himselfbis Monday, March 10, 2014 9:53 PM
    Monday, March 10, 2014 9:52 PM

All replies

  • Sub TestNumberFormat()
       
       Dim wb As Workbook
       Dim ws As Worksheet
       
       Dim writtenFc As FormatCondition
       
       Dim readFc1 As FormatCondition
       Dim readFc2 As FormatCondition
    
       Set wb = Workbooks("wb.xlsx")
       Set ws = wb.Sheets("Sheet1")
       
       ws.Cells.FormatConditions.Delete
       
       ' Create first format condition
       Set writtenFc = _
          ws.Range("A1").FormatConditions.Add( _
                 xlExpression, _
                 xlNone, _
                 True)
       writtenFc.Font.Bold = True
          
       ' Create second format condition
       Set writtenFc = _
           ws.Range("B2").FormatConditions.Add( _
                 xlExpression, _
                 xlNone, _
                 True)
        '*****MY COMMENT******
        '***There is no number format called Standard.***
        'writtenFc.NumberFormat = "Standard"
        writtenFc.NumberFormat = "0.00"
    
       
    '***You have set format condition to A1 and B2 and single condition to both.
    '***But in your code you tried to access from Cells property of sheet.
    '***Cells property refers to all cell of a sheet.
        
        ' Read properties format condition
       Set readFc1 = ws.Range("a1").FormatConditions(1)
       Set readFc2 = ws.Range("b2").FormatConditions(1)
    
       
       ' Read properties format condition
       'Set readFc1 = ws.Cells.FormatConditions(1)
       'Set readFc2 = ws.Cells.FormatConditions(2)
          
          
       Debug.Print "fc1 Numberformat = " + _
          readFc1.NumberFormat
       Debug.Print "fc2 NumberFormat = " + _
          readFc2.NumberFormat
          
       If (IsNull(readFc1.Font.Bold)) Then
          Debug.Print "fc1 Bold property will not be applied"
       Else
          Debug.Print "fc1 Bold         = " + _
             CStr(readFc1.Font.Bold)
       End If
       If (IsNull(readFc2.Font.Bold)) Then
          Debug.Print "fc2 Bold property will not be applied"
       Else
          Debug.Print "fc2 Bold         = " + _
             CStr(readFc2.Font.Bold)
       End If
       
    End Sub
    
    

    HOPE above will help.

    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Wednesday, March 19, 2014 9:46 AM
    Answerer
  • Thanks for your answer but the problem is still here...

    Till now, the only workaround that I have found is to add the following lines just before the line

    ' Read properties format condition

    ' ---- START ----
    Dim fullName As String
    fullName = wb.fullName
     wb.Save
     wb.Close
     Workbooks.Open Filename:= _
            fullName
       Set wb = Workbooks("wb.xlsx")
       Set ws = wb.Sheets("Sheet1")
    ' ---- END ----

    As you can see, it consists on closing the workbook and open it just after.

    Output of the code becomes :

    fc1 Numberformat =
    fc2 NumberFormat = Standard
    fc1 Bold         = Vrai
    fc2 Bold property will not be applied


    This is the expected behavior. But it remains a workaround.

    Any other idea ?


    Some additional information about the version of Office that I use:

    • Language is French. So numberFormat "Standard" does work

    • Complete version number is 14.0.7116.5000 (32 bits)
      Office Professionnel Plus 2010

    Regards,

    ic0

    Wednesday, March 19, 2014 9:41 PM
  • I just made few changes and it worked.Posted by me with comments.

    In excel each cell can have many format rules and those are accessed by...

    Range("A1").Formatconditions(1) for 1st rule, Range("A1").Formatconditions(2) for 2nd rule.....

    You applied condition on A1 and B2 but later tried to access with ws.Cells which is not right. If all cells have same format condition then you can acess the properties.If different rules applied it will not show.

    Can you tell me specifically at which line code fails.


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Thursday, March 20, 2014 8:22 AM
    Answerer
  • Thanks again for this answer.

    Some complementary information about what has been tested :

    • I have copy and paste your code and the output is still the same.
    • No error occurs when the code is executed. It means that no popup appears and code is fully executed.
      The issue is in the output that is printed by the lines that start with "Debug.print"
    • The first line of Output is "fc1 Numberformat = Standard" and I expect to have "fc1 Numberformat =" (without the standard at the end of the line).
      If my understanding is correct, it should mean: "if condition is true, the numberFormat of the cell will not be changed"

    • If I add the lines in your code (or in my code) to save the workbook, close it and re-open it just before the reading of conditional rules, the output is what is expected.

    Feel free to correct me if I have not quite understood how conditional rule works with vba.

    Regards,

    ic0

    Thursday, March 20, 2014 6:46 PM
  • I am getting the output without any work around. Seems to be a different issue.

    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Friday, March 21, 2014 1:28 PM
    Answerer
  • Well. It seems to be a different behavior with the version of Office that you use.

    I have tested the code on a 64 bits version (language and version number is the same) and the output is still the same.

    Can you send me the complete version number and the language of Office on your side ?

    Very strange...

    ic0

    Saturday, March 22, 2014 9:47 AM