Please Help!!! With Drop-down List
 

Is there any way to control how the dropdown list is displayed?
Specifically, I have a data validation list in a cell. The column it is
in is only 4 units wide, with the text aligned at 90 degrees
(vertically). The dropdown list displayed is also only 4 units wide,
and does not show enough characters of each list item. I've seen other
spreadsheets where the dropdown list is wider than the column, but it
seems to be somewhat random...

Is there any way to increase the size (or other properties) of the
dropdown list (apart from increasing the column width)?


--
seantera
------------------------------------------------------------------------
seantera's Profile: http://www.excelforum.com/member.php...o&userid=27294
View this thread: http://www.excelforum.com/showthread...hreadid=468401

Hi seantera,

May be this is not the best solution but you can use this in the sheets
selection change event.

This assuming that your listbox is in the cell "E1". Please change to
suit yours. And you need to check at the VBE immidiate window to see
the correct name for your drop down list. Here I only have one drop
down list, so the name is "Drop Down 1).

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim s As Shape
Dim BtnWidth As Single
With ActiveCell
Select Case .Address(False, False)
Case "E1"
If .Validation.Type = xlValidateList Then
Set s = ActiveSheet.Shapes("Drop Down 1")
BtnWidth = s.Width - .Width
s.Width = 150
s.Left = .Left + .Width - s.Width + BtnWidth
Set s = Nothing
End If
End Select
End With
End Sub

Best regards,


--
hideki
------------------------------------------------------------------------
hideki's Profile: http://www.excelforum.com/member.php...o&userid=18903
View this thread: http://www.excelforum.com/showthread...hreadid=468401