title: Change Excel forms Label Length to match the length of it's text using VBA
body: <p>How can I get an Excel Form Control Label which is on a worksheet to change it's length to match the length of the sting of characters in it's caption. Importantly it must work irrispective of what screen the file is viewed on or what the zoom setting is on the sheet.</p>
  
  <p>So far I've got:</p>
  
  <pre><code>Sub LabelLength()
  
  Dim XYLabel As Shape
  Dim XYDataSheet As Worksheet
  
  Set XYDataSheet = ThisWorkbook.Sheets(1)
  Set XYLabel = XYDataSheet.Shapes(1) 'This is the Forms Label
  
  XYLabel.OLEFormat.Object.Caption = Trim(XYDataSheet.Cells(1, 1).Value)
  XYLabel.Width = Len(Trim(XYDataSheet.Cells(1, 1).Value)) * 7.5
  
  
  End Sub
  </code></pre>
  
  <p>Which works, but does leave quite a lot of space after some captions.</p>