I have 4 list boxes on a worksheet that I have manually placed and sized. While the lists are populated via VBA when the file is opened, there is no VBA control of sizing or placement. While the file opens fine for me, the boxes grow in size on coworkers machines. I have already verified the following.
Everyone has there zoom setting at 100%
No one has a monitor resolution set to anything other than Recommended.
I have the listboxes set to not move or size with cells
I have IntegralHeight set to false on all 4 list boxes
None of that appears to help. As mentioned the only VBA touching the boxes populates the lists and selects all items on opening. The following is the list populating code for just one of the boxes (I trimmed the sub down since its pretty much the same thing repeated).
Public Sub List_Pop()
Dim dLastRow As Double
With Sheet3
'Status Year List Box
With Sheet1
dLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
End With
.Range("D1:D" & dLastRow - 1).Value = Sheet1.Range("CT2:CT" & dLastRow).Value
.Range("D1:D" & dLastRow - 1).RemoveDuplicates Columns:=1
dLastRow = .Cells(.Rows.Count, 4).End(xlUp).Row
.Range("D1:D" & dLastRow).Sort Key1:=.Columns(4)
Sheet2.StatYearListBox.List = .Range("D1:D" & dLastRow).Value
.Columns("D").ClearContents
End With
End Sub
Display More
The following is how I select all the items in one of the boxes. The string is just to house the selections for a formula.
Public Sub Select_All()
Dim sSelected(3) As String
bSkipUpdate = True
With Sheet2
For i = 0 To .StatMonthListBox.ListCount - 1
.StatMonthListBox.Selected(i) = True
sSelected(0) = sSelected(0) & .StatMonthListBox.List(i) & ","
Next i
End With
End Sub
Display More
Is there anything in there that you think could be resizing the boxes, or something else I am missing? It does seem like it occurs after the lists have populated, as when a coworker first opens it the boxes appear correct. But once the list is there is goes wonky (which is what I thought IntegralHeight would fix). I know it would be simple to just force it to a size on opening but I hate band aids, especially when it works on my computer.