VBA to adjust Row Height based on Characters

[Jasuan]

Through research I understand that there is no formula to adjust row height, so now my question ventures into VBA:

In my workbook employees enter data into Cell B68 of worksheet DATA and that data is populated into merged Cell D8:J9 of worksheet V1. This happens for every "V" worksheet, of which there are 40 (i.e. DATA!B70 for worksheet V2, DATA!B72 for worksheet V3, etc.). The problem I run into is if there is too much text, employees have to unlock the worksheet to adjust it. We would like to avoid that.

Since the data is populated by formula ("=IF(DATA!B68=0," ",DATA!B68)") the wrap text function doesn't work. So my initial thought was to take an adjacent cell and have it use LEN() and use an algorithm formula to adjust height (i.e. add 1/8" height for every 83 characters). Since I can't do this, I will need a VBA to mimic this type of behavior.

I would need to have a minimum height of 3/8".

Is this possible? Is there a man or woman brave enough to take on this problem? I am appreciative of all responses.

 

[lrobbo314]

You might need to fine tune the math in there to adjust the row height the way you like, but something like this should work.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim l As Integer
Dim RH As Double
l = Len(Range("D8").Value)
RH = (l / 83) * 8
If RH < 20 Then RH = 20
Range("8:9").RowHeight = RH
End Sub

 

[Jasuan]

You might need to fine tune the math in there to adjust the row height the way you like, but something like this should work.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim l As Integer
Dim RH As Double
l = Len(Range("D8").Value)
RH = (l / 83) * 8
If RH < 20 Then RH = 20
Range("8:9").RowHeight = RH
End Sub

Thanks for the reply.
I'll input this on Monday and see how she runs. In the VBA I don't see how it references different sheets.