ListBox Size and Screen Resolution

[MikeT]

Hi!

I am having difficulty with an ActiveX listbox on a worksheet and differing
screen resolutions. The listbox keeps changing sizes depending on the screen
resolution.

For example, I created a new workbook while in 1440x900 mode, added a
listbox control, and saved the workbook and closed Excel. Next, I changed to
1920x1200 and reopened the workbook. Now, clicking in and out of "Design
Mode" causes the font size in the box to continually get bigger until all
that is left is a black box where the listbox was. Next, I saved the
workbook, changed back to 1440x900, and reopened the workbook. The listbox
was so small it wasn't even visible. I had to go into design mode and click
around until the handles appeared, then I could drag the box out to a decent
size.

Is there any way I can "pin" the control to a range of cells, so the the box
coveers those cells, regardless of the display resolution?

 

[OssieMac]

Hi Mike,

The following code sizes to the cell positions. I have assumed that you want
it to occur when the workbook is opened so have placed it in the
Workbook_Open event.

Not sure if you know how to do this but to install the code open the VBA
editor (Alt/F11) and double click ThisWorkbook in the Project explorer and
copy the code into it.

Note the comments where you may need to edit the code.



Note that a space and underscore at the end of a line is a line break in an
otherwise single line of code.

Private Sub Workbook_Open()
Dim objListBox As OLEObject
Dim dblLeft As Double
Dim dblTop As Double
Dim dblWidth As Double
Dim dblHeight As Double

'Edit "Sheet1" to your sheet name
With Sheets("Sheet1")
'Edit the ranges to suit the sizes you want.
'Note measurements are Left and Top of cells
dblLeft = .Range("C4").Left
dblTop = .Range("C4").Top
dblWidth = .Range("E8").Left _
- .Range("C4").Left

dblHeight = .Range("C15").Top _
- .Range("C4").Top
End With

'Edit "ListBox1" to your listbox name
Set objListBox = Sheets("Sheet1") _
.OLEObjects("ListBox1")
With objListBox
.Left = 47.25
.Top = 26.25
.Width = 97.5
.Height = 177.75
End With

End Sub

 

[OssieMac]

My apologies Mike. I left the actual sizes in for the Left, width etc
instead of changing them to the variables. Try the following instead.

Private Sub Workbook_Open()
Dim objListBox As OLEObject
Dim dblLeft As Double
Dim dblTop As Double
Dim dblWidth As Double
Dim dblHeight As Double

'Edit "Sheet1" to your sheet name
With Sheets("Sheet1")
'Edit ranges to suit your required position and sizes
dblLeft = .Range("C4").Left
dblTop = .Range("C4").Top
dblWidth = .Range("E8").Left _
- .Range("C4").Left

dblHeight = .Range("C15").Top _
- .Range("C4").Top
End With

'Edit "ListBox1" to your object name
Set objListBox = Sheets("Sheet1") _
.OLEObjects("ListBox1")

With objListBox
.Left = dblLeft
.Top = dblTop
.Width = dblWidth
.Height = dblHeight
End With

End Sub