2

I have an excel sheet with multiple listboxes. All of them multi-select. As excel always messes up the dimensions of the listboxes i had a piece of code to repair them when opening the workbook:

Private Sub Workbook_Open()
    Dim Ctrl As OLEObject

    For Each Ctrl In Sheets("SomeSheet").OLEObjects
        If Ctrl.progID = "Forms.ListBox.1" Then
            With Ctrl
            .Width = 95.4
            .Height = 70.2
            End With
        End If
    Next Ctrl  
End Sub

However after a while i noticed that the last entry is at best partially visible. A search on the interweb provided me with a solution : link.

Unfortunately that solution does not work for me. I adapted the above code to:

Private Sub Workbook_Open()
    Dim Ctrl As OLEObject

    Application.ScreenUpdating = False

    For Each Ctrl In Sheets("SomeSheet").OLEObjects
        If Ctrl.progID = "Forms.ListBox.1" Then
            With Ctrl
            .Object.IntegralHeight = False
            .Width = 95.4
            .Height = 70.2
            .Object.IntegralHeight = True
            .Object.MultiSelect = fmMultiSelectSingle
            .Object.MultiSelect = fmMultiSelectExtended
            End With
        End If
    Next Ctrl   
End Sub

The result is that the width and height i have set are again screwed up by excel, as soon as the three statements following the .height are executed. With each statement resulting in a further shrinking of the listbox in both dimensions. To make matters worse they also move away from their position.

I'm looking for any clues how to fix this, so to have listboxes of the desired dimension, with all entries visible.

Edit 20140905 As per request a screenshot is added. Even though the scrollbar suggest i can scroll down further, that is not possible. As you can see by the blue color, 'Orange' is there, and is selected, and is in the listbox.

Example

Community
  • 1
  • 1
TooComplicated
  • 33
  • 4
  • 1
    Can you show a screenshot of the ListBoxes? – ServerS Sep 02 '14 at 15:17
  • Maybe [this link](http://stackoverflow.com/questions/1573349/excel-the-incredible-shrinking-and-expanding-controls) will help you. – Automate This Sep 02 '14 at 17:50
  • Are you by any chance messing with screen resolutions while Excel is open? I did this once while testing for display compatibility and encountered the effect you are describing. – blackworx Sep 04 '14 at 17:00
  • Screenshots will follow tomorrow. I'll check your link out as well Portland runner. I'm not messing with screen resolutions while excel is open. This file has only been edited/opened with my laptop docked. I get the changing controls size even when staying on the same resolution. Switching between worksheets can be enough. Thought i had that covered by setting the size on opening the sheet, but now the last entry is invisible. As a workaround i just added an empty entry as the last one in the list, so the actual last one is now always visible. – TooComplicated Sep 04 '14 at 18:48
  • Were you able to find any solution for this? – Simulator Oct 03 '19 at 07:32

1 Answers1

0

set IntegralHeigth property of ListBox to false.

Juan Ruiz de Castilla
  • 964
  • 14
  • 25