Wednesday, February 19, 2014

Changing the value in a worksheet text box, Part 1: Exploration and Explanation

Problem

One of my worksheets has a TextBox with information that the user had been updating manually each month.  It was a ratio between two figures on the worksheet and placed in front of a chart.  The calculation did not match what was in the chart.

Exploring the Object Model

I like to run a quick subroutine that assigns a worksheet (or some other object) to a variable, then I can look at an instantiated object and the related objects.  This gives me a more insight than just reading the Object model.  Using a break point at End Sub, I can examine it in the Watch window.

Sub LookAtWS
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1") ' Or any other worksheet.
End Sub

  • There were 3 Items in the worksheet's Shapes collection: the TextBox and 2 Charts. 
  • The Charts had meaningful names and the TextBox had a system generated name, e.g. "TextBox 3".
  • To get to what needed to be changed, diving in showed it at  ws.Shapes("TextBox 3").DrawingObject.Caption

Prep Code

I like meaningful names.  "TextBox 3" may uniquely identify the object but it does little for someone debugging or doing maintenance.  I saw no way to update the Shape's name via the Excel UI so I ran this throwaway.

Sub RenameShape
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1") ' Or any other worksheet.
    ws.Shapes("TextBox 3").Name = "Used vs Available")
End Sub

Solutions

As I looked at how to use this knowledge I realized there were multiple ways to use it.  Here are three initial paths, to be explored in subsequent entries.
  • Purpose-built.  This would go to a specific TextBox and update it using function or 
  • Brainless.  To this one you pass the worksheet, the name of the TextBox, and the new value.
  • Smart.  This one would have all the bells and whistles, including finding the right TextBox to update, calling functions by name, selecting cells for calculations, etc.