You are here:

Excel/Follow Up on Row Height VBA Change Event.

Advertisement


Question
QUESTION: Hi Tom,

You helped me the other day with a Row Height VBA, and it works phenomenally. One unforeseen issue, as I am a Macro/VBA neophyte, is that I intended for the Cells that trigger the Row Height to be formula driven.  I did not realize that the VBA will only work with Manual entries.

For example, if A5 is my Cell that will Trigger the Row Height adjustment in Row 1, I'd like for the height of Row 1 to change when the Value in A5 changes due to the formula it contains. I've found that the Row height only will change with the current VBA if I manually adjust the Cell that triggers the Row adjustment.

If it's not possible, that's fine. I can live with that slight hiccup. Thanks again!

-Ken

ANSWER: Hello Ken,

You can use the calculate event.  It fires everytime there is a calculate cycle.  In this routine you would have to have your code set the row height of every row because the calculate event does not return a "Target" which triggered the event. But for rows where the height would not change, it will just reset it to what it was.  The one that was changed will be resized.


Private Sub Worksheet_Calculate()
Range("C2").EntireRow.RowHeight = Application.InchesToPoints( Range("A1").Value)
Range("F2").Entirerow.RowHeight = Application.InchesToPoints( Range("B3").Value)
Range("M2").EntireRow.RowHeight = Application.InchesToPoints( Range("L10").Value)
' and so forth

End Sub

this goes in the worksheet module where event code is placed ( same as where the Change event was/is located).

--
Regards,
Tom Ogilvy



---------- FOLLOW-UP ----------

QUESTION: Thank you, Tom.  I appreciate the answer, but feel I might now be out of my league.  I tired to apply the Calculate Event, attempting to put the desired Trigger Cell and Row Locations into the Code you provided, but cannot seem to get it right.  So ,if you are willing to look at my actual Row Height VBA and the Corresponding Cell Locations where the Values/Formulas are being entered, Ill copy it below.

Below is my Current VBA Code, and it works exactly as you described...

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$C$24" Then

If IsNumeric(Target.Value) Then

      Range("A6").EntireRow.RowHeight = Application.InchesToPoints(Target.Value)

End If

End If



If Target.Address = "$C$25" Then

If IsNumeric(Target.Value) Then

      Range("A7").EntireRow.RowHeight = Application.InchesToPoints(Target.Value)

End If

End If



If Target.Address = "$C$26" Then

If IsNumeric(Target.Value) Then

      Range("A8").EntireRow.RowHeight = Application.InchesToPoints(Target.Value)

End If

End If



If Target.Address = "$C$27" Then

If IsNumeric(Target.Value) Then

      Range("A9").EntireRow.RowHeight = Application.InchesToPoints(Target.Value)

End If

End If



If Target.Address = "$C$28" Then

If IsNumeric(Target.Value) Then

      Range("A10").EntireRow.RowHeight = Application.InchesToPoints(Target.Value)

End If

End If



If Target.Address = "$C$29" Then

If IsNumeric(Target.Value) Then

      Range("A11").EntireRow.RowHeight = Application.InchesToPoints(Target.Value)

End If

End If



If Target.Address = "$C$30" Then

If IsNumeric(Target.Value) Then

      Range("A12").EntireRow.RowHeight = Application.InchesToPoints(Target.Value)

End If

End If



If Target.Address = "$C$31" Then

If IsNumeric(Target.Value) Then

      Range("A13").EntireRow.RowHeight = Application.InchesToPoints(Target.Value)

End If

End If



End Sub



The only Rows that I desire for the Height to Change are Rows 6 -13 (any others I can Manually Adjust if necessary)

I have Manually Keyed Data in Cells E24-E31.  These will be the only Cells that where I would want to Manually Adjust the Values.

Starting at E24 the Respective Values are:

7.7          E24
6.2
5.0
3.9
2.8
1.9
1.2
0.5          E31



The Cells Containing the Formulas (derived from the Values in Cells E24-E31) that Id Like to Trigger the Row Height Changes are C24-C31.

Starting at C24 the Respecitve Formulas are:

=E24-E25          C24
=E25-E26
=E26-E27
=E27-E28
=E28-E29
=E29-E30
=E30-E31
=E31          C31


So, as I comprehend your suggestion, I would have to Create a Calculate Event that Contains 8 Lines of Code for Each of the 8 Rows I want to see Adjusted, the 8 Cells that I will be Manually Changing the Data, and 8 Cells Containing Formulas that Trigger the Row Height Changes.

As stated, I think Ive ventured outside of my Abilities.  So providing my actual Coding, along with the Actual Cell Locations that contain the Values and Formulas, is sort of a last ditch effort to convey what Id need in order to accomplish the end goal.

Again, youve already gone above and beyond.  Id completely understand if you feel that weve reached a point where I lack the Technical Skills to comprehend the Calculate Event.  Ultimately, if I have to just manually key them in when I want to make changes, it will still be far more convenient than manually adjusting the Row Heights each time.

Best Regards.
Ken

Answer
Ken,

this is how I would convert the code you sent me

Private Sub Worksheet_Calculate()
Range("A6").EntireRow.RowHeight = Application.InchesToPoints(Range("C24").Value)
Range("A7").EntireRow.RowHeight = Application.InchesToPoints(Range("C25").Value)
Range("A8").EntireRow.RowHeight = Application.InchesToPoints(Range("C26").Value)
Range("A9").EntireRow.RowHeight = Application.InchesToPoints(Range("C27").Value)
Range("A10").EntireRow.RowHeight = Application.InchesToPoints(Range("C28").Value)
Range("A11").EntireRow.RowHeight = Application.InchesToPoints(Range("C29").Value)
Range("A12").EntireRow.RowHeight = Application.InchesToPoints(Range("C30").Value)
Range("A13").EntireRow.RowHeight = Application.InchesToPoints(Range("C31").Value)
End Sub

I will have to look at the rest of the explanation you gave and revise this answer if appropriate. -- [Added:] I have looked at the remainder of your description and I believe the code above will do what you want.

You sent your question in the middle of the night, so I only have a minute to look at it right now at 5:30 AM
--
Regards,
Tom Ogilvy

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.