View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.programming
OssieMac OssieMac is offline
external usenet poster
 
Posts: 2,510
Default ListBox Size and Screen Resolution

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

--
Regards,

OssieMac