Shrinking Listbox from Control Toolbox

[kumik5000]

Hello

I made a listbox using the control toolbox option but every time I "save as"
and open the spreadsheet, the size of the listbox changes. It usually shrinks
so that you can't read the list options inside.

Is there any way to keep that from happening? If there's a way without
having to use VBA (as I'm not familiar) that would be great.

Thanks for your help.

 

[Billy Liddel]

I'm afraid that you need a macro to fix the size of the list. I had this
problem myself and called the macro from a worksheet_change event and also on
opening.

The way is worked was to link the size to an input range height - if the row
height changed (due to wrapping text) then so did the list box.

Reply if you want to go down the macro route.

Peter Atherton

 

[kumik5000]

Thanks for the reply. It that's the only way I'm up to it!

 

[Billy Liddel]

Ok Kumik

Actually I did include calling the code from the workbook
code but deleted this later

Right click the sheet tab and click View Code. Copy the first
macro in there

Then Choose Insert, Module and copy the FormatRowHeight macro in
the module.

Remember to create a range name called InputRange or if you already
have a range name change the name in the code.

You may want to alter the row heights yourself later, all changes
are better done in a copy of your file - including these macros

Sheet Code

Note: ListBox object is called LstNames

' =========================================

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngHeight As Double

' ListBox object is called LstNames
' rowsheight is formatted in Module1 (General Macros)

FormatRowHeight

' format listNames object to row height
rngHeight = Range("InputRange").Height
If lstNames.Height = rngHeight Then
Exit Sub
Else
lstNames.Height = rngHeight
End If

End Sub

'===================================================================
' General Module Code

' Because this macro is called from the Sheet Module
' it has to be declared Public.
' The inputRange is a named range within Sheet with List Box
' ============================================================

Public Sub FormatRowHeight()
' Format the WeekView Input range Row Heights
' Note that the Input range Rows is fixed
' and is a 8 day cycle
Dim dblMinHeight As Double
Dim irngRows As Integer
Dim rngInput As Range
Dim CountRow As Integer
Dim rngTemp As Range
Dim iColCount As Integer

dblMinHeight = 45
iColCount = WorksheetFunction.CountA(Rows("1:1"))
Set rngInput = Range("Inputrange")

With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

For irngRows = 2 To 9
Set rngTemp = Range(Cells(irngRows, 3), Cells(irngRows, iColCount))
CountRow = WorksheetFunction.CountA(rngTemp)
If CountRow = 0 Then
Rows(irngRows).RowHeight = dblMinHeight
Else: Rows(irngRows).EntireRow.AutoFit
If Rows(irngRows).RowHeight < dblMinHeight Then
Rows(irngRows).RowHeight = dblMinHeight
End If
End If
Next irngRows

With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With

End Sub


Please tick the button if this is of use to you.

Regards
Peter Atherton