List Box Sizing

[raccoon588]

is there anyway to loop through all my lists boxes and make them the same size when the excel file is opened?

 

[Fluff]

You're welcome

 

[My Aswer Is This]

OK Raccoon

You did not say what width you wanted the listbox's to be.
So this script will ask you what width.

If you do not want that let me know the width you want or if you can modify the script.

The script also needs to know what the sheet name is and since you did not provide sheet name.
And you wanted this to run when you Opened Workbook. I told the script to do this in sheet named "Excel"

You will need to modify that name.

Here is the script.

Sub Size_Listbox()
'Modified  1/29/2019  2:57:43 PM  EST
Dim ans As Long
Dim MySheet As String
MySheet = "Excel" ' Modify this sheet name List boxes in this sheet will be sized.
Sheets(MySheet).Activate
    ans = InputBox("What width do you want all litboxes", "I suggest 100", "100")
    For Each OLEobj In Sheets(MySheet).OLEObjects
        If OLEobj.progID = "Forms.ListBox.1" Then
            With OLEobj
                .Width = ans
            End With
        End If
    Next OLEobj
End Sub

Now to have this script run when you open Workbook
Do this:

Right click on any sheet tab.
Choose View code
Double click on Thisworkbook

Paste in this code:

Private Sub Workbook_Open()
Call Size_Listbox
End Sub