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?
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,559
Office Version
365
Platform
Windows
Where are the listboxes located?
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,761
Office Version
2013
Platform
Windows
edit
 
Last edited:

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,761
Office Version
2013
Platform
Windows
Thanks. I still have not figured out how to set width using vba in a loop to set width
Maybe someone else here will have a answer.

I can set nearly everything just not the Width.
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,761
Office Version
2013
Platform
Windows
This script is what I have but I cannot figure out why when I try to set Width I get a error code:

Run Time Error 438
Object doesn't support this property or method.

Everything works if I remove .Width
Which is what your wanting.

Code:
Private Sub CommandButton1_Click()
'Modified  1/29/2019  1:02:41 PM  EST
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
    For Each OLEobj In ActiveSheet.OLEObjects
        If OLEobj.progID = "Forms.ListBox.1" Then
  
            With OLEobj.Object
                .Font.Size = 24
                .Clear
                .List = Range("A1:A" & Lastrow).Value
                .BackColor = vbGreen
                .ForeColor = vbRed
                .Width = 200
            End With
        End If
    Next OLEobj
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,606
Office Version
365
Platform
Windows
It needs to be like
Code:
            With [COLOR=#ff0000]OLEobj[/COLOR]
                .[COLOR=#ff0000]Object[/COLOR].Font.Size = 24
                .[COLOR=#ff0000]Object[/COLOR].Clear
                .[COLOR=#ff0000]Object[/COLOR].List = Range("A1:A" & Lastrow).Value
                .[COLOR=#ff0000]Object[/COLOR].BackColor = vbGreen
                .[COLOR=#ff0000]Object[/COLOR].ForeColor = vbRed
                .Height = 200
                .Width = 200
            End With
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,761
Office Version
2013
Platform
Windows
Thanks Fluff it works great now

It needs to be like
Code:
            With [COLOR=#ff0000]OLEobj[/COLOR]
                .[COLOR=#ff0000]Object[/COLOR].Font.Size = 24
                .[COLOR=#ff0000]Object[/COLOR].Clear
                .[COLOR=#ff0000]Object[/COLOR].List = Range("A1:A" & Lastrow).Value
                .[COLOR=#ff0000]Object[/COLOR].BackColor = vbGreen
                .[COLOR=#ff0000]Object[/COLOR].ForeColor = vbRed
                .Height = 200
                .Width = 200
            End With
 

Watch MrExcel Video

Forum statistics

Threads
1,099,004
Messages
5,465,955
Members
406,455
Latest member
mkadam

This Week's Hot Topics

Top