Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ListBox Size and Screen Resolution
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ListBox Size and Screen Resolution
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 -- Regards, OssieMac |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Variable screen size, not resolution | Excel Worksheet Functions | |||
How to get the screen resolution in VBA | Excel Programming | |||
Form elements change size with Screen Resolution change | Excel Programming | |||
How to get fixed size for every screen resolution? | Charts and Charting in Excel | |||
Can't get screen resolution | Excel Programming |