Unable to hide row Excel 2003 from function invoked from formula

Question

I have this very simple function

Public Function HRows(xx As String)
    BeginRow = 2
    EndRow = 10
   ' HideRows
    For RowCnt = BeginRow To EndRow
     Cells(RowCnt,ChkCol).EntireRow.Hidden = True
    Next RowCnt
End Function 

When invoked from a command button it works fine, when invoked as a formula, e.g =HRows(A1), from a worksheet cell it doesn't do anything on Excel 2003, it does work in Open Office Calc 4.1

This happens on an otherwise empty spreadsheet - no protection, no comments, no shapes (which have been suggested as inhibitors in other questions)

Eventually, I want to hide/show the relevant sections of a spreadsheet, depending on what the user enters in certain key cells - I don't want to have to add command buttons to control the hiding.

Eyla

In general it is not possible to change the state of a worksheet by using a UDF. However, Tim Williams posted this (https://mcmap.net/q/112088/-using-a-udf-in-excel-to-update-the-worksheet/2119523) quite brilliant work-around some time ago. I am not sure if this would work with hiding rows to. – Greenfield

Answer 1

I've already introduced this method here https://mcmap.net/q/112631/-excel-vba-load-worksheet-in-function, for your purpose a code will be as follows:

Place code to one of the module of VBAProject:

Public Tasks, PermitNewTasks, ReturnValue

Function HideRowsUDF(lBegRow, lEndRow) ' Use this UDF on the sheet
    If IsEmpty(Tasks) Then TasksInit
    If PermitNewTasks Then Tasks.Add Application.Caller, Array(lBegRow, lEndRow)
    HideRowsUDF = ReturnValue
End Function

Function HideRows(lFrom, lUpTo) ' actually all actions performed within this function, it runs without UDF limitations
    Range(Rows(lFrom), Rows(lUpTo)).EntireRow.Hidden = True
    HideRows = "Rows " & lFrom & "-" & lUpTo & " were hidden"
End Function

Sub TasksInit()
    Set Tasks = CreateObject("Scripting.Dictionary")
    ReturnValue = ""
    PermitNewTasks = True
End Sub

Place code to ThisWorkbook section of Microsoft Excel Objects in VBAProject:

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    Dim Task, TempFormula
    If IsEmpty(Tasks) Then TasksInit
    Application.EnableEvents = False
    PermitNewTasks = False
    For Each Task In Tasks
        TempFormula = Task.FormulaR1C1
        ReturnValue = HideRows(Tasks(Task)(0), Tasks(Task)(1))
        Task.FormulaR1C1 = TempFormula
        Tasks.Remove Task
    Next
    Application.EnableEvents = True
    ReturnValue = ""
    PermitNewTasks = True
End Sub

Sleep

Thanks for this - The answer is obviously more complex than I had hoped. I will need to work on more pressing problems before I can come back and play with this – Eyla