Changing the value in a worksheet text box, Part 2: Purpose Built

Here are the first path mentioned in Changing the value in a worksheet text box, Part 1.

Purpose Built Subroutine

This depends on accessing the values in cells (ranges) and manipulating them for what goes into the .DrawingObject.Caption.  The original code establishes where these are because they shift each month.  The ranges are hard-coded for this example.  B2 is the total number of employees.  B1 is the number assigned.  Both are end of month snap shots.

Sub UpdateRatioTextBox(ws as Worksheet)

    With ws

        .Shapes("Assignment Ratio Text Box").DrawingObject.Caption = "N/A"

        If IsNumber(.Range("B2"))  Then

            If .Range("B2") <> 0 Then

                If Len(.Range("B1") = 0 Then

                    .Shapes("Assignment Ratio Text Box").DrawingObject.Caption = _                                       Format(0, "0.00%")

                Else

                     If IsNumber(.Range("B1")) Then

                         .Shapes("Assignment Ratio Text Box").DrawingObject.Caption = _ 

                                Format(Iif(Len(.Range("B2")= Format(.Range("B2") _

                                                                                 / .Range("B1"), "0.00%")                      End If

                End If

         End If

    End With

End Sub