Listbox keeps moving

[rickblunt]

Hello,
I am using the sub below to populate a listbox (named "RepairHistory") that I have embedded in a worksheet, the code works fine but the issue is that every time I run the macro the listbox moves a few pixels up and to the left. I do not understand why it is doing that, has anyone ever had this issue before? I need to stop this from happening so I appreciate any insights - thanks,

Sub RepairHistoryButton_Click()

 Dim sh As Object, sh2 As Worksheet
  Dim i As Long
  
  Set sh = Sheets("Plant Status")
  Set sh2 = Sheets("Repair Log")
    
  sh.RepairHistory.Clear
  For i = 1 To sh2.Range("A" & Rows.Count).End(3).Row
    If sh2.Range("A" & i).Value = sh.RepairedDevice.Value Then
      With sh.RepairHistory
        .AddItem
        .List(.ListCount - 1, 0) = sh2.Cells(i, 2).Value
        .List(.ListCount - 1, 1) = sh2.Cells(i, 3).Value
        .List(.ListCount - 1, 2) = sh2.Cells(i, 4).Value
        .List(.ListCount - 1, 3) = sh2.Cells(i, 5).Value
        .List(.ListCount - 1, 4) = sh2.Cells(i, 6).Value
        .List(.ListCount - 1, 5) = i
       End With
    End If
  Next
        

End Sub

 

[My Aswer Is This]

When in design mode:
1. Right click on listbox and choose Format Control
2. Choose Don't move or size with cells.
No Vba Required

 

[rickblunt]

When in design mode:
1. Right click on listbox and choose Format Control
2. Choose Don't move or size with cells.
No Vba Required

,
Yes, even though there are no cells being adjusted that was the first thing that I did (just in case) but that did not work. The weird thing is that as the macro runs the listbox does not move as it is being populated, it seemingly moves at the very end of the routine.

Also, it will only do it the first time the macro runs, if I repeat the sub over and over the listbox will not continue to move each time - just the first time it is run. What in the world is it that is calling for this object to move? If it was the sub then I would think it would do it every time. I am wondering if it is one of the listbox properties but I am not calling the location parameter in any code.

I have never seen this happen before and it really is a head scratcher...

 

[My Aswer Is This]

You said:
the listbox moves a few pixels up and to the left.

How can you even notice a pixel

 

[DanteAmor]

Change the IntegralHeight property of the listbox to False

 

[rickblunt]

You said:
the listbox moves a few pixels up and to the left.

How can you even notice a pixel

well, I was using "pixel" as a general term. I would estimate that it moves left - 10pt, and up about 30pt. It ends up out of alignment with other objects and partially covers a commandbutton. It is always the same amount when it shifts.

 

[rickblunt]

Change the IntegralHeight property of the listbox to False

View attachment 58731

Thanks Dante - that did it. Another little item I will add to my VBA troubleshooting book. I appreciate the save (how many times has it been now? - lol)

 

[DanteAmor]

Glad I was able to help!