VBA - Activex Listbox resizing/shrinking bug?

[cacos]

Hi everyone, I've been dealing with this one for a while now, hoping a more experienced vba user found a solution/workaround of some sort.

When using activex listbox (inserted on sheet, not userform), it happens that when another user opens the file the listbox starts behaving funny.

It shrinks/resizes font size, height and width. Also sometimes it shrinks everything including the scroll bar, and leaves the white background the same size.

I've tested and it happens to different users running different computers, resolutions and even Windows versions.

I've tried putting code on Workbook_Open to control font size, height and widht, as well as on the Listbox_Click event.

Code:

With Sheet1

.A_Listbox.Font.Size = 11
.A_Listbox.Height = 170
.A_Listbox.Width = 150

End With

But it still won't do it.

Anyone faced this before??

Thanks!

 

[Hui]

try

Private Sub Workbook_Open()
With Worksheets("Sheet1").Shapes.Range(Array("ListBox1"))
  .Width = 150
  .Height = 170
End With

Worksheets("Sheet1").ListBox1.Font.Size = 11
End Sub

 

[cacos]

Thanks Hui!!
I'll test it and let you know.