List Box Sizing

raccoon588

Board Regular
Joined
Aug 5, 2016
Messages
118
is there anyway to loop through all my lists boxes and make them the same size when the excel file is opened?
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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.

Code:
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:
Code:
Private Sub Workbook_Open()
Call Size_Listbox
End Sub
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top