Dynamically Changing Arc Length based on Cell Value (Excel)

Question

I am trying to adjust the arc length in Excel based on the value of a specific cell.

For example, if the % = 100, then the arc should become a circle.

The code I'm using is not working. The Arc length of (Block Arc 1) doesn't change when I change the % value in the cell (A1). Can anyone help please :) ?

Sub AdjustArc(arcShape As Shape, percent As Single)
    Dim xAddress As String
    On Error Resume Next
    If Target.CountLarge = 1 Then
        xAddress = Target.Address(0, 0)
        If xAddress = "A1" Then
            AdjustArc ThisWorkbook.Sheets("Sheet1").Shapes("Block Arc 1"), Val(Target.Value)
        End If
    End If
End Sub

You can format your code block by clicking to [edit] your question, then select (highlight) the section that is code, then hit Ctrl+G or click the `{ }` button. ;) — ashleedawg, Aug 11 '18 at 09:22
What does 'not working' mean? If you were answering this question, what would you like to know? — nicomp, Aug 11 '18 at 12:37
@nicomp, I mean that the Arc length of (Block Arc 1) doesn't change when I change the % value in the cell (A1) — Ahmed, Aug 11 '18 at 13:51
@DisplayName - This is the only code I have. I just want to represent some progress% with Arc which its length change based on the %. I'm just a beginner :( — Ahmed, Aug 11 '18 at 13:57
your shown code has a `Sub AdjustArc(arcShape As Shape, percent As Single)` calling itself (`AdjustArc ThisWorkbook.Sheets("Sheet1").Shapes("Block Arc 1"), Val(Target.Value)`) — DisplayName, Aug 11 '18 at 13:57
theoretically I understand what you're saying, but could you help by recommending what to modify. — Ahmed, Aug 11 '18 at 14:00
theoretically you should have an `AdjustArc()` sub that acts on a shape: you must show it — DisplayName, Aug 11 '18 at 14:04

score 0 · Accepted Answer · answered Aug 11 '18 at 14:12

I saw your previous post.

You should need something like follows

Worksheet_Change event handler

place the following code in the code pane of the worksheet where the arc shape resides (and be sure the the shape is named after "Block Arc 1")

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge = 1 Then
        If Target.Address(0, 0) = "A1" Then AdjustArc Shapes("Block Arc 1"), Target.Value
    End If
End Sub

AdjustArc sub

place the AdjustArc() sub (@ashleedawg 's courtesy) in any module you want

Sub AdjustArc(arcShape As Shape, percent As Single)
' ashleedawg 's courtesy in https://stackoverflow.com/questions/51797109/changing-arc-length-in-excel-based-on-a-cell-value
'adjust the circumference of the arc or hides if 0%.
'Supply the percent as a fraction between 0 and 1. (50% = 0.5)

    With arcShape
        If percent <= 0 Then 'hide shape
            .Visible = False
            Exit Sub
        End If

        If percent > 1 Then percent = 1 'over 100%, make it 100%
        .Visible = True

        '0 = Full Circle, 359.9 = sliver, 360 = Full Circle
        .Adjustments.Item(1) = (1 - percent) * 359.9
    End With
End Sub

You are welcome. Still wondering why you didn’t attach your full previuos code... — DisplayName, Aug 11 '18 at 14:25

Dynamically Changing Arc Length based on Cell Value (Excel)

1 Answers1

Linked