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?

 

[Norie]

Where are the listboxes located?

 

[raccoon588]

they're all on the same tab.

 

[My Aswer Is This]

Are these Activex Or Form control listbox's?

 

[My Aswer Is This]

edit

 

[raccoon588]

Activex

 

[My Aswer Is This]

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]

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.

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]

It needs to be like

            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]

Thanks Fluff it works great now

It needs to be like

            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