1

Getting a weird problem with left aligning a Shapes to a range of cells. It seems to work when there is no ListObject on the sheet, but when a ListObject is present the left alignment slowly 'creeps' to the right such that after about 20 columns the shape is visibly out of alignment with the cell.

enter image description here

If you paste the below into a module in a new workbook then run it once and all the shapes should be aligned. Then uncomment the line that creates the ListObject and run again - the shapes move to the right.

Hope someone has an idea on a fix and/or hack!

Public Sub WierdAlignment()

    Dim ws As Worksheet
    Dim MonthNum As Long
    Dim lo As ListObject
    Dim vStartMonth As Date
    Dim cell As Range
    Dim shp As Shape
    
    Set ws = ActiveSheet
    
    ' Clean up before testing
    For Each shp In ws.Shapes
        shp.Delete
    Next shp
    For Each lo In ws.ListObjects
        lo.Delete
    Next lo
    Cells.Delete
    
    vStartMonth = Now()
    MonthNum = 0
    
    ' Run once, then uncomment this line
    ' Set lo = ws.ListObjects.Add(xlSrcRange, ws.Range("B5:AZ10"))

    For Each cell In Range("B5:AZ5")
        Set shp = ws.Shapes.AddTextbox(msoTextOrientationHorizontal, cell.Left, cell.Top - 20, 1, 15)
        shp.TextFrame.AutoSize = True
        With shp.TextFrame2.TextRange.Characters
            .Font.Size = 8
            .Text = Format(DateAdd("m", MonthNum, vStartMonth), "Mmm-YYYY")
        End With
        shp.Left = cell.Left ' doing it again after it is populated...
        MonthNum = MonthNum + 1
    Next cell

End Sub
Davo
  • 141
  • 7
  • I get RTE 5 Invalid procedure call or argument when uncommenting that line after an initial run. Error occurs on that line. Looking at the documentation the correct line should be `Set lo = ws.ListObjects.Add(xlSrcRange, ws.Range("B5:AZ10"))` I don't then see an alignment issue. – QHarr Feb 14 '22 at 06:18
  • Yep typo there, thank you - have updated and added a screenshot. Still getting the alignment issue (Office 365 v2102). – Davo Feb 14 '22 at 07:03
  • It doesn't occur for me Excel 2019 :-( Does this happen even with a fresh workbook and worksheet? I do notice column width increases at column 10 (as per headers. Col K worksheet) and wonder if that is related? – QHarr Feb 14 '22 at 08:06
  • Side note: Use explicit worksheet references through. Generally avoid ActiveSheet and implicit Activesheet references such as `Range("B5:AZ5")`. See [this](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba/35864330#35864330) – QHarr Feb 14 '22 at 08:13
  • Have tried on new workbook, closed excel etc, no joy. Haven't even been able to find a reliable hack to make it work consistently. Concerning if it behaves differently on different versions (which i cannot easily test). – Davo Feb 14 '22 at 08:16
  • Is your alignment issue starting at the same point column width increases? – QHarr Feb 14 '22 at 08:31
  • 1
    Just found a solution. Added the following immediately after the For "cell.UseStandardWidth = True". Found that completely by chance! My best explanation is that the ListObject somehow messes with the width of the cells, and this resets it. Thank you for your comments @ QHarr – Davo Feb 14 '22 at 08:38
  • Btw you can self-answer your question and accept that answer after two days. – QHarr Feb 14 '22 at 08:42

1 Answers1

0

In this circumstance, I was able to fix by including:

cell.UseStandardWidth = True

after the "For" statement. This seemed to fix the problem and the boxes aligned to the cells correctly. Unfortunately it created some other problems as cell width was adjusted, so further accommodations had to be made. So not an ideal fix, but the only one found so far.

Davo
  • 141
  • 7