Wednesday, February 19, 2014

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