1

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.

So far I've got:

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

Which works, but does leave quite a lot of space after some captions.

Oliver Humphreys
  • 418
  • 1
  • 5
  • 17
  • 1
    if it works but is too big, couldnt you just keep decreasing the `7.5` until you get a size you want? – Marcucciboy2 Jun 20 '18 at 17:01
  • @Marcucciboy2, If I decrease it to a point where it works with one text string, then it won't work with text strings which contain bigger or smaller letters. eg 'X' takes up more space than 'i'. 7.5 seems to be the lowest value where don't loose letters. – Oliver Humphreys Jun 21 '18 at 10:07

1 Answers1

3

I don't think this can be done with a Form control, at least not directly. If you want to stick with a Form control, I think you're on the right track with scaling its .width property based on the text size. To do it better than that, you'd probably have to build a dictionary object or something and sum the width of all characters in the string based on the width you had listed in the dictionary object.

However, if you can switch your label to an ActiveX control, then you can solve this by using:

.WordWrap = False
.AutoSize = True

I think people in general prefer to use Form controls because they're a bit more simple. But I also think, in general, ActiveX controls have more flexibility and you can customize them to your needs more. You can read a bit more on Form controls vs. ActiveX controls here.

Chip R.
  • 350
  • 2
  • 6
  • Cheers! It worked very well at zoom 100% but not when zoomed in or out. However I solved the issue by setting zoom to 100 then changing the ActiveX label then zooming back to the original. – Oliver Humphreys Jun 22 '18 at 13:34
  • Here's the new code: `Sub LabelLength() Dim XYLabel As Shape Dim XYDataSheet As Worksheet Dim OrigZoom As Integer Set XYDataSheet = ThisWorkbook.Sheets(1) Set XYLabel = XYDataSheet.Shapes(1) OrigZoom = ActiveWindow.Zoom ActiveWindow.Zoom = 100 XYLabel.OLEFormat.Object.Object.Caption = XYDataSheet.Cells(1, 1).Value XYLabel.OLEFormat.Object.Object.WordWrap = False XYLabel.OLEFormat.Object.Object.AutoSize = True ActiveWindow.Zoom = OrigZoom End Sub` – Oliver Humphreys Jun 22 '18 at 13:35