Autofit Row Height not working on a cell containing a formula

[danners430]

Hey folks,

I've got a spreadsheet which uses formulas to keep the data in a single ("master") location, with the "alternate views" simply displaying the contents of that master cell. However, I've discovered that in some cases the Autofit function for rows (autofit row height) doesn't correctly fit the row height to the result of the formula, only displaying 1-3 "rows" of text, when in reality there should be sometimes more than 5 (sometimes even beyond the maximum row height).

This issue only seems to be present on one sheet - when I first designed the spreadsheet, Autofit worked fine on the main sheet that used the functions. But when I added another sheet, necessitating a new UDF, it stopped working on that specific sheet...

The original UDF reads:

Public Function returnValue(meRow As Long, meCol As Integer) As String              'User-defined Function, which is used to show what is in the JIRA, DRACAS or (future)                                                                                    'new Benefits Register.
                                                                                    'meRow and meCol refer to the row / column from which this function is called,
                                                                                    'allowing the function to read the Feedback Method and reference no.
                                                                                    
Dim row As Long                                                                     'Row number in which the referenced entry is stored (DRACAS / JIRA sheets)
Dim col As Integer                                                                  'Column number for this type of request
Dim queryType As String                                                             'Feedback Method - "DRACAS", "JIRA", etc.
Dim queryRef As Variant                                                             'DRACAS / JIRA reference number
Dim colHead As String                                                               'Column header


returnValue = "Error"                                                               'Default return value


On Error GoTo pub                                                                   'Error handling - any errors which occur skip the rest of the function,
                                                                                    'and end up returning the default value above
                                                                                    
If benefitRegInt = 0 Then Call resetVariables                                       'Check that static variables have been loaded into memory.
                                                                                    'Should happen at startup, but can be accidentally cleared by a reset.


queryType = Sheets(benefitRegInt).Cells(meRow, _
            sheetCols.returnID(benefitRegInt, sh1Feedback))                         'Load the feedback method
            
queryRef = Sheets(benefitRegInt).Cells(meRow, _
            sheetCols.returnID(benefitRegInt, sh1Ref))                              'Load the DRACAS / JIRA reference number
            
colHead = sheetCols.returnName(benefitRegInt, meCol)                                'Load the column header


Select Case queryType                                                               'DRACAS or JIRA


    Case "DRACAS"
    
        row = findReference(dracasInt, queryRef)                                    'Search for entry in DRACAS register
        
        Select Case colHead                                                         'Select correct column reference for this sheet
            Case sh1Title
                col = sheetCols.returnID(dracasInt, sh3Title)
            Case sh1Equip
                col = sheetCols.returnID(dracasInt, sh3Equip)
            Case sh1Desc
                col = sheetCols.returnID(dracasInt, sh3Desc)
            Case sh1Ref
                col = sheetCols.returnID(dracasInt, sh3Ref)
            Case sh1Open
                col = sheetCols.returnID(dracasInt, sh3Open)
            Case sh1Close
                col = sheetCols.returnID(dracasInt, sh3Close)
            Case sh1Update
                col = sheetCols.returnID(dracasInt, sh3Update)
        End Select
        
        returnValue = Sheets(dracasInt).Cells(row, col).Value                       'Return value contained in the cell referenced by row & col
        
    Case "JIRA"                                                                     'As above for JIRA
        
        row = findReference(jiraRegInt, queryRef)
        
        Select Case colHead
            Case sh1Title
                col = sheetCols.returnID(jiraRegInt, sh2Title)
            Case sh1Equip
                col = sheetCols.returnID(jiraRegInt, sh2Equip)
            Case sh1Desc
                col = sheetCols.returnID(jiraRegInt, sh2Desc)
            Case sh1Ref
                col = sheetCols.returnID(jiraRegInt, sh2Ref)
            Case sh1Update
                col = sheetCols.returnID(jiraRegInt, sh2Update)
        End Select
        
        returnValue = Sheets(jiraRegInt).Cells(row, col).Value
    
'   Case "Benefit"
        
End Select


pub:                                                                                'Error trap


    'cya!


End Function

The new UDF is:

Public Function returnBenefit(meRow As Long, meCol As Integer) As String            'User-defined Function, which is used in the Benefits Register to show what is in the _                                                                                    'main register.
                                                                                    'meRow and meCol refer to the row / column from which this function is called,
                                                                                    'allowing the function to read the Feedback Method and reference no.


Dim row As Long                                                                     'Row number in which the referenced entry is stored (main register)
Dim col As Integer                                                                  'Column number for this type of request
Dim queryRef As Variant                                                             'DRACAS / JIRA reference number
Dim colHead As String                                                               'Column header


returnBenefit = "Error"                                                               'Default return value


On Error GoTo pub                                                                   'Error handling - any errors which occur skip the rest of the function,
                                                                                    'and end up returning the default value above


If benefitRegInt = 0 Then Call resetVariables                                       'Check that static variables have been loaded into memory.
                                                                                    'Should happen at startup, but can be accidentally cleared by a reset.




queryRef = Sheets(Sh4Int).Cells(meRow, _
            sheetCols.returnID(Sh4Int, sh4Ref))                                 'Load the DRACAS / JIRA reference number


colHead = sheetCols.returnName(Sh4Int, meCol)                                       'Load the column header


row = findReference(benefitRegInt, queryRef, "Benefit")                                    'Search for entry in DRACAS register


Select Case colHead                                                         'Select correct column reference for this sheet
    Case sh4Title
        col = sheetCols.returnID(benefitRegInt, sh1Title)
    Case sh4Equip
        col = sheetCols.returnID(benefitRegInt, sh1Equip)
    Case sh4Desc
        col = sheetCols.returnID(benefitRegInt, sh1Desc)
    Case sh4Feedback
        col = sheetCols.returnID(benefitRegInt, sh1Feedback)
    Case sh4FeedRef
        col = sheetCols.returnID(benefitRegInt, sh1Ref)
End Select


        returnBenefit = Sheets(benefitRegInt).Cells(row, col).Value                       'Return value contained in the cell referenced by row & col


pub:                                                                             'Error trap


    'cya!


End Function

Note - all the sheet numbers are stored in constant variables, and column headers & numbers in the sheetCols class module, to protect against users moving columns about.
Both UDFs are called using the following syntax: "=[formulaName](ROW(), COLUMN())"

Both of these UDFs work absolutely fine in terms of displaying the desired values - it's just the Autofit function that doesnt work on the sheet using the second UDF.

Anyone have any ideas?

 

[sijpie]

No idea why it fails. I do have an idea on how to at least set the height correctly. It is something I use on some of my projects: In a hidden sheet I set a cell to the same width as the cell where the text needs to go. Then I put the text into that cell, do autofit and check the height. You can then in the macro set the height of the celll in the viewed sheet to the same height. I don't know if this is allowed to be done in a function, but it could be done in the sheet change event.

something like:

Function CheckCellHeight(sS As String, dW As Double) As Integer
    Dim cC As Range
    
    Set cC = ThisWorkbook.Sheets("Check").Cells(1, 1)
    cC.Columns.ColumnWidth = dW
    cC.Rows.RowHeight = 15
    cC.Value = sS
    cC.Rows.AutoFit
    CheckCellHeight = cC.Rows.RowHeight
    cC.Clear
End Function