Target.value

[Stacy H]

I'm using the following code to change the height of rectangles for a chart. This works fine as long as I enter the value and press enter, I've attempting use a formula in $A$1 but it never updates the height of the object... what can i do for this to work ?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
  Shapes("Rectangle 1").Height = Target.Value * 6.96
End If
If Target.Address = "$A$2" Then
  Shapes("Rectangle 2").Height = Target.Value * 6.96

Thanks,

 

[chirayu]

refer to tutorial link
https://www.extendoffice.com/docume...n-macro-when-cell-formula-result-changes.html

 

[Stacy H]

The bars in my sheet do change size if I enter a value in A26:L26 and press enter, but I can't get t to work using a formula in those cells with the code from the example you linked. VBA and programming in general is new to me, could you look at my example and add the code to make it work?

Thanks.

 

[Debaser]

Given that the formulas depend on the three rows above, you should monitor those rows in the change event, not row 26. Given that there is a pattern to the shape names and column numbers, I'd use a simple loop like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim col                   As Range
    If Not Intersect(Target, Range("A23:L26")) Is Nothing Then
        For Each col In Intersect(Target, Range("A23:L26")).Columns
            Shapes("Rectangle " & 9 + col.Column).Height = Cells(26, col.Column).Value * 6.96
        Next col
    End If
End Sub

 

[Stacy H]

Thanks, that did exactly what I needed !