Dynamically Changing Row Heights based on cell values

Brilliant!

Thanks so much, that works perfectly.

Jason

Re: Dynamically Changing.. Everytime?

This is pretty close to what I am looking for, but is there any way it can be modified to change another row? For example, if I have a value in B2 in Sheet1, can it change row 4 in Sheet2?

Thanks for all your help,

Jason

Re: Dynamically Changing.. Everytime?

Jason,

With this code in sheet1's module it will change sheet2. Where I have placed x, you need to put in a row number. If you have a lot of values that you are inputing it will take a little time to set up each cell address. How do you have the sheet set up? Are there labels to the values that this can pull a row number from?
Let me know
RYan
Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("Sheet2").Rows(x).RowHeight = Target.Value
End Sub

Re: Dynamically Changing.. Everytime?

I think I phrased my last post about the dynamic row height manipulation incorrectly. Let me try again.

I currently have a macro that will correctly modify the heights as I desire (thanks again Ryan, your first suggestion was perfect). I would like to have this macro automatically run on Sheet2 every time a value in either cells B2 or B3 on Sheet1 is modified at all.

Is this possible? My setup currently calculates and aligns everything like I want, but I have to run the macro each time I change my values. That isn't a problem for me, but I am going to pass this around to my entire group, and the possibility for error from not running the macro is very high. I am trying to make this as "idiot proof" for the group as I can.

Thanks so much for all of your help,

Jason

Sample Code

Ryan,

Here is the macro code I am currently using.

Sub test()
'
Rows(3).RowHeight = Range("B17").Value
Columns("B:V").ColumnWidth = Range("B16").Value
Rows(2).RowHeight = Range("B18").Value
Rows(4).RowHeight = Range("B18").Value
'
End Sub

I have to run the macro while Sheet 2 is selected. I would like to be able to have the macro automatically run on Sheet2 whenever the values of B2 or B3 on Sheet1 are modified.

Thanks,

Jason

Re: Sample Code

Here you go,
Once again, this goes in the Sheet1 module not a regular module. Let me know if this works for you.

Ryan

Private Sub Worksheet_Change(ByVal Target As Range)
Dim VRange As Range
Set VRange = Range("B2:B3")
Application.ScreenUpdating = False
If Union(Target, VRange).Address = VRange.Address Then
Sheets("Sheet2").Rows(3).RowHeight = Range("B17").Value
Sheets("Sheet2").Columns("B:V").ColumnWidth = Range("B16").Value
Sheets("Sheet2").Rows(2).RowHeight = Range("B18").Value
Sheets("Sheet2").Rows(4).RowHeight = Range("B18").Value
End If
Application.ScreenUpdating = True
End Sub

Re: Sample Code

How do you make a Sheet1 module? I guess I have been using regular modules the whole time. In Excel '97 when I have the VB Editor open, it says Module1 (the one with your code) in a folder called Modules. I can't seem to associate that module specifically with Sheet1. I tired inserting an ActivityModuleControl from the Insert -> Object menu.

Am I screwing this up? It seems like the answer is right there, but I can't make it work.

Thanks,

Jason

Re: Sample Code

Jason,

First, make sure the project explorer is open (ctrl-R). In here you will see different projects(workbooks) that are open. These are bolded. Find the workbook that you want this code in and unhide everything. There should be 2 folders. One that says Microsoft Excel Objects and another that says Modules (There is also one for forms if you have any). In the Excel Objects folder there is an object for each sheet in that workbook and a "ThisWorkbook" object. These are the modules that you want. So if you want this code to go into sheet1, double click on Sheet1 (Sheet1) and paste the code into there. That's all. Hope you find your way.

Ryan

Jason,
Here is code that will do what you want. This example is taking the value in A1 and setting the row height of Row 1 to that value.
Let me know how it works!
Ryan

Rows(1).RowHeight = Range("A1").Value

Re: Dynamically Changing.. Everytime?

Is there a way to have this macro run everytime either one of two cells is modified?

I am afraid that the others in my office won't run the macro each time they change the values. Other than that it works great!

Thanks,

Jason

Re: Dynamically Changing.. Everytime?

Jason,

This code will change the row height each time a new value is entered. I don't know how you have the page set up, but this will change the height of the row that you are entering data into. So if you put 15 into "A1", Row 1's height will be 15, etc. If this needs to be different let me know. This code has to go into the Microsoft Excel Objects folder into the sheet module for the sheet that you are working on(i.e, if you are using "Sheet1", this code will go into sheet1's module. Let me know how it works out!
Ryan
Private Sub Worksheet_Change(ByVal Target As Range)
Rows(Target.Row).RowHeight = Target.Value
End Sub