This is a follow up to a thread I followed in "How to make a macro run each time a specific cell value changes." That thread helped me understand how to get a macro to execute when a particular cell was changed, but the macro itself does not do what it is supposed to do.
The workbook I am developing has one sheet (sheet A) that contains a list of scheduled events or appointments. A second sheet (sheet B) draws information from Sheet A to produce a report of appointments for any given day. Cell B10 in sheet B allows the user to enter a date for the report. Formulas in sheet B match the date in B10 to entries in sheet A and list all the appointments that match that date. For lines where the date does not match, the formulas display blanks. Worksheet A also sorts these appointments by family group, based on a sort key field.
Worksheet A lists appointments for children with dates and times. Since the children in each family would be seen together, I want to have a blank line between each family group in Worksheet B. The result should be a page that lists a series of appointments, with the members of each family group single spaced, and a blank line between each family group.
To accomplish this, I included a conditional CHAR(10) (linefeed) character in the formula in Worksheet B. When the family identifier of the line does not match the identifier for the line above, a linefeed is included. This creates the extra space between lines. Based on information I've seen in other forums, I turned word wrap on so that the CHAR(10) will force an extra line in the cell. The formula for that cell is:
The last part of the formula inserts a null, a line feed and an item number followed by a period.
I am trying to get the sheet to execute an auto row height so that the row height will increase for those rows containing the extra linefeed, and shrink for those rows without the extra linefeed. If a different date is selected in B10 and a different grouping of children results, the change in the date should trigger the macro to autofit to the new data. I have been able to confirm that the technique I got from the early forum does result in triggering the macro, but I haven't been able to get the macro to work correctly. Actually, the problem appears to be with the autofit command itself, because if I use the menu selection Home|Format|Autofit Row Height on multiple rows, it doesn't work either. If I apply it to an individual row, it does work. Likewise, if I modify my macro to work only on one row, it works, but if I apply it to a range of rows, it doesn't. I have scoured various forums to find techniques. It appears that I may have to use a For ... Next or Do ... Until loop to autofit the rows one at a time, but this will slow down the execution. Does anyone have suggestions? By the way, I've seen many posts that indicate that trying to use Autofit with merged cells does not work, the rows I am operating on do not include any merged cells.
To trigger the automatic action, I use the following VBA code in worksheet B's module:
Here is the Row_Height Macro (it's in a different module, because if I get this working, it will be called by several different report worksheets.)
The workbook I am developing has one sheet (sheet A) that contains a list of scheduled events or appointments. A second sheet (sheet B) draws information from Sheet A to produce a report of appointments for any given day. Cell B10 in sheet B allows the user to enter a date for the report. Formulas in sheet B match the date in B10 to entries in sheet A and list all the appointments that match that date. For lines where the date does not match, the formulas display blanks. Worksheet A also sorts these appointments by family group, based on a sort key field.
Worksheet A lists appointments for children with dates and times. Since the children in each family would be seen together, I want to have a blank line between each family group in Worksheet B. The result should be a page that lists a series of appointments, with the members of each family group single spaced, and a blank line between each family group.
To accomplish this, I included a conditional CHAR(10) (linefeed) character in the formula in Worksheet B. When the family identifier of the line does not match the identifier for the line above, a linefeed is included. This creates the extra space between lines. Based on information I've seen in other forums, I turned word wrap on so that the CHAR(10) will force an extra line in the cell. The formula for that cell is:
=IF(A16=A15,"",IF(AND(INDEX(Data,$K$10+K15,1)=$B$10,INDEX(Data,$K$10+K15,3)>0.5),""&CHAR(10)&A16&".","")).
The last part of the formula inserts a null, a line feed and an item number followed by a period.
I am trying to get the sheet to execute an auto row height so that the row height will increase for those rows containing the extra linefeed, and shrink for those rows without the extra linefeed. If a different date is selected in B10 and a different grouping of children results, the change in the date should trigger the macro to autofit to the new data. I have been able to confirm that the technique I got from the early forum does result in triggering the macro, but I haven't been able to get the macro to work correctly. Actually, the problem appears to be with the autofit command itself, because if I use the menu selection Home|Format|Autofit Row Height on multiple rows, it doesn't work either. If I apply it to an individual row, it does work. Likewise, if I modify my macro to work only on one row, it works, but if I apply it to a range of rows, it doesn't. I have scoured various forums to find techniques. It appears that I may have to use a For ... Next or Do ... Until loop to autofit the rows one at a time, but this will slow down the execution. Does anyone have suggestions? By the way, I've seen many posts that indicate that trying to use Autofit with merged cells does not work, the rows I am operating on do not include any merged cells.
To trigger the automatic action, I use the following VBA code in worksheet B's module:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$10" Then
Application.Run "Row_Height"
End If
End Sub
If Target.Address = "$B$10" Then
Application.Run "Row_Height"
End If
End Sub
Here is the Row_Height Macro (it's in a different module, because if I get this working, it will be called by several different report worksheets.)
Sub Row_Height()
' Adjust Row Height Macro
ActiveWorkbook.Rows("15:43").AutoFit
End Sub
' Adjust Row Height Macro
ActiveWorkbook.Rows("15:43").AutoFit
End Sub
