UDF returns #VAULE! only when changing filter setting via own button function

[phunsoft]

Hi,
I did some research, but so far I haven't found an answer to my problem. Hopefully I can get some hints here.

I wrote the IsChangeopen() function (UDF, show futhrer down) to display "Closed" if out of 5 cells in the same row, all of them are colored (marking a complete status). If one or more cells are not colored, then "Open" shall be displayed.

I enter the functions in, say, cell K5 as =IsChangeOpen(F5;G5;H5;I5;J5). This initlally works as desired: If any of cells F5 to J5 are non-colored, then "Open" is shown in cell K5; otherwise "Closed" is shown.

I have activated auto filters, so that I can easily choose only "Open" rows to be shown. Changing the filter manually between "Open" and "all" rows works perfectly. The K cells show the desired values and the filter selects only desired rows.

I then have added a button on the sheet that shall set the filter to only rows showing "Open" in cells Kn on a click.

The strange thing is that all K cells immediately show #VALUE! as soon as I click no the button??? I can reset the K cells by a simple F2 followed by return.

I read that UDF may not enter data in cells, which is what my function does. I change the K cells to contain
=IF(=IsChangeOpen(F5;G5;H5;I5;J5)="Open";"Open";"Closed")
I thought this way the UDF does not change the cell but only help the IF to change it.
The problem still persists.
I'm out of clue what causes the #VALUE! error. Any ideas?

Thx
Peter


--------------------------------------------------------------
Function IsChangeOpen(c1, c2, c3, c4, c5) As String
Dim OpenCount
OpenCount = 0

If c1.Interior.ColorIndex = xlNone Then
OpenCount = OpenCount + 1
End If
If c2.Interior.ColorIndex = xlNone Then
OpenCount = OpenCount + 1
End If
If c3.Interior.ColorIndex = xlNone Then
OpenCount = OpenCount + 1
End If
If c4.Interior.ColorIndex = xlNone Then
OpenCount = OpenCount + 1
End If
If c5.Interior.ColorIndex = xlNone Then
OpenCount = OpenCount + 1
End If

If OpenCount = 0 Then
IsChangeOpen = "Closed"
Else
IsChangeOpen = "Open"
End If

End Function
-----------------------------------------------------------------------

 

[mikerickson]

...
there doesn't seem to be a satisfactory way to update the "Open/Closed" status immediately upon the act of the user changing the Interior.Color property of a cell through the User Interface.
...

That is why color should not convey meaning.
Excel is built for cell contents to have meaning, not cell formatting.

Color is a highlight for human eyes. For calculation and processing, cell values are the tools for that.

 

[phunsoft]

Sure. So many people visit MrExcel for code snippets that it's bound to help someone.

The following code finally applies an autofilter on column K (#11) to only display rows where the function in column K returns "Open". It does so for all worksheets in the workbook. The function to remove the autofilter again (ShowAllItems) and to set the Status column (IchChangeOpen()) are also shown.

Sub ShowOnlyOpenItems()
'
' ShowOnlyOpenItems Macro
' Show only rows where "Status" (column K) is "Open"

    Dim oneSheet As Worksheet
    Dim currentSheet As Worksheet
            
    Set currentSheet = ActiveSheet
    
    For Each oneSheet In ActiveWorkbook.Worksheets
    
        With oneSheet
            .Activate

            With .Range("A5:K" & .Cells(.Rows.Count, "K").End(xlUp).row)
                .Calculate
                .AutoFilter Field:=11, Criteria1:="Open"
            End With
            
        End With
        
    Next
    
    For Each oneSheet In ActiveWorkbook.Worksheets
       oneSheet.Calculate
    Next
    
'   Jump back to the current sheet
    currentSheet.Activate
   
End Sub

Sub ShowAllItems()
'
' ShowAllItems Macro
' Show rows in any "State"
  
    Dim oneSheet As Worksheet
    Dim currentSheet As Worksheet
            
    Set currentSheet = ActiveSheet
    
    For Each oneSheet In ActiveWorkbook.Worksheets
    
        With oneSheet
            .Activate
            
            With .Range("A5:K" & .Cells(.Rows.Count, "K").End(xlUp).row)
                .Calculate
                .AutoFilter Field:=11
            End With
            
        End With
        
    Next
    
    For Each oneSheet In ActiveWorkbook.Worksheets
       oneSheet.Calculate
    Next

'   Jump back to the current sheet
    currentSheet.Activate
    
End Sub
                

Function IsChangeOpen(rng As Range) As String
    
    Application.Volatile True

    Dim cc As Range

    For Each cc In rng

        If cc.Interior.ColorIndex = xlNone Then
            IsChangeOpen = "Open"
            Exit Function
        End If
    
    Next cc
      
    IsChangeOpen = "Closed"
        
End Function

Thanks a lot for your help!

--
Peter

 

[phunsoft]

That is why color should not convey meaning.
Excel is built for cell contents to have meaning, not cell formatting.

Color is a highlight for human eyes. For calculation and processing, cell values are the tools for that.

Sure. The worksheet has long been used in this way: The cell content was a completion date or empty if task is incomplete, sometimes it is also a due date. The cell's colour was used to visualize completed state versus open state (uncoloured).

I wanted to be able to able to apply an autofilter to reduce the display to open task rows only. This is what I wrote the IsChangeOpen() functions for. It perfectly worked when the autofilter was manually applied (selecting only "Open" rows). Troubles began when trying to automate the process to activating an deactivating the autofilter on all sheets of the workbook.

--
Peter

 

[phunsoft]

[snip]... from our observation that there doesn't seem to be a satisfactory way to update the "Open/Closed" status immediately upon the act of the user changing the Interior.Color property of a cell through the User Interface.

A slight correction to the above statement. The value "Open"/"Close" value shown by the UDF changes immediately after changing the colour of a "monitored" cell. Applying an autofilter manually also works.

It is only when the autofilter is set from a script that the values are shown as #VALUE!

--
Peter

 

[Jerry Sullivan]

Peter, The UDF might update if you use your macro or paste into those cells, but it shouldn't update upon your manually applying color to the cells.

 

[phunsoft]

I've not been overly happy with the code as show in my previous post. First, I didn't like the dependency on K cells content to be a call to the UDF function. That was fine as long as there hasn't been any automation to the workbook. Second, I didn't like the ShowOnlyOpenItems() code that was a bit ugly due to the problem Excel had with the UDF.

I started to think this over and found a much nicer solution to the problem. Here it is for those interested.

The task was to code two routines that hide and unhide rows in a multi-workseet workbook based on some selection criteria. See the comment at the beginning of the subroutines for more information.

Sub ShowOnlyOpenItems()
'
' ShowOnlyOpenItems Macro
'
' On each worksheet show only rows that are either title rows or task rows for open tasks.
' - A row is considered a title row if cell A is nonempty and its background is colored.
' - A row is considered a task row if cell A is nonempty and its background is not colored.
' - A task is considered closed, if all of the cells F thru J have its background colored.
'
' Cell "E5" shall highlight the fact that not all rows are shown.    Dim oneSheet As Worksheet
    Dim LastRow As Long
    Dim CurrentRow As Long
    Dim IsChangeopen As String
    
    
'   Inhibit screen update while the function is working. This is the number one performance tip!
    Application.ScreenUpdating = False
    
    
    For Each oneSheet In ActiveWorkbook.Worksheets
    
        With oneSheet
        
            LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
            
            With .Range("E1")
                
                .Value = "Only Open Items"
            
                With .Font
                    .ColorIndex = 0
                    .TintAndShade = 0
                    .Underline = xlUnderlineStyleNone
                    .Bold = True
                End With
            
                With .Interior
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .Color = 255
                    .TintAndShade = 0
                    .PatternTintAndShade = 0
                End With
                
                     
            End With
    
            For CurrentRow = 5 To LastRow
                
                If IsEmpty(.Cells(CurrentRow, 1)) Then
                    .Cells(CurrentRow, 1).RowHeight = 0
                    
                Else
                    If .Cells(CurrentRow, 1).Interior.ColorIndex = xlNone Then
                        IsChangeopen = "Closed"
                        
                        For Cell# = 6 To 10
                        
                            If .Cells(CurrentRow, Cell#).Interior.ColorIndex = xlNone Then
                                IsChangeopen = "Open"
                                Exit For
                            End If
                            
                        Next Cell#
                        
                        If IsChangeopen = "Closed" Then
                            .Cells(CurrentRow, 1).RowHeight = 0
                        End If
                        
                    End If
                    
                End If
      
            Next CurrentRow
            
            
        End With
        
    Next
    
    
'   Allow screen updates again
    Application.ScreenUpdating = True
   
End Sub

------------------------------------------------------------------------------------------
Sub ShowAllItems()
'
' ShowAllItems Macro
'
' Resize row heigths so all rows are shown again'
'
    Dim oneSheet As Worksheet
    
'   Inhibit screen update while the function is working. This is the number one performance tip!
    Application.ScreenUpdating = False
    
    For Each oneSheet In ActiveWorkbook.Worksheets
    
        With oneSheet
        
            With .Range("E1")
                .Value = ""
                
                With .Interior
                    .Pattern = xlNone
                    .TintAndShade = 0
                    .ColorIndex = xlNone
                    .PatternTintAndShade = 0
                End With
                
                
            End With
    
'           Reset the row heights to the required heigth
            .Rows.AutoFit
            
        End With
        
    Next
    
    
'   Allow screen updates again
    Application.ScreenUpdating = True
   
End Sub

------------------------------------------------------------------------------------------
Sub MarkAsDone()
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 5296274
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    
End Sub

------------------------------------------------------------------------------------------
Sub MarkAsOpen()
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .ColorIndex = xlNone
        .PatternTintAndShade = 0
    End With
End Sub

------------------------------------------------------------------------------------------

--
Peter