change height dropdown list box

[Guest]

How can I change the height of an in cell dropdown list box (not a combo box) for a validation list
I only see part of the items I can choose from when I drop down the list

 

[Debra Dalgleish]

Changing the cell height won't affect what you can see in the dropdown
list. However, you could use code to temporarily make the list wider.
There is sample code on my web site:

http://www.contextures.com/xlDataVal08.html#Wider

 

[Guest]

Thanks Debra

I do not see any change after copying the code in the way it is described on your site
In VBA-editor it does not allow debugging either. What do I do wrong

Toos

 

[Debra Dalgleish]

The code goes into the worksheet module -- right-click the sheet tab,
and choose View Code.
Paste the code where the cursor is flashing.

 

[Guest]

I tried it in Excel 2000 and 2002 but it doesnt show any result
( I copied

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$2" Then
ActiveWindow.Zoom = 120
Else
ActiveWindow.Zoom = 100
End If
End Su

and made a validation list in cell A2

Toos

 

[Debra Dalgleish]

That's the code to change the zoom setting, so the dropdown font appears
larger. The code I suggested is just above that:

http://www.contextures.com/xlDataVal08.html#Wider

'=====================================
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column = 4 Then
Columns(4).ColumnWidth = 20
Else
Columns(4).ColumnWidth = 5
End If
End Sub
'==========================================

Also, do you have macros enabled for the workbook?

 

[Guest]

You are right of course, but I tried the privat sub just below that one in order to make my dropdownview larger
It doesnt work however :

Too

----- Debra Dalgleish wrote: ----

That's the code to change the zoom setting, so the dropdown font appears
larger. The code I suggested is just above that

http://www.contextures.com/xlDataVal08.html#Wide

'====================================
Private Sub Worksheet_SelectionChange(ByVal Target As Range
If Target.Count > 1 Then Exit Su
If Target.Column = 4 The
Columns(4).ColumnWidth = 2
Els
Columns(4).ColumnWidth =
End I
End Su
'=========================================

Also, do you have macros enabled for the workbook


Toos wrote

 

[Debra Dalgleish]

Are macros enabled in the workbook?

You are right of course, but I tried the privat sub just below that one in order to make my dropdownview larger.
It doesnt work however

Toos

----- Debra Dalgleish wrote: -----

That's the code to change the zoom setting, so the dropdown font appears
larger. The code I suggested is just above that:

http://www.contextures.com/xlDataVal08.html#Wider

'=====================================
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column = 4 Then
Columns(4).ColumnWidth = 20
Else
Columns(4).ColumnWidth = 5
End If
End Sub
'==========================================

Also, do you have macros enabled for the workbook?

 

[Guest]

No macros.

In VB-editor the privat sub does not react on F8 (Debug)

----- Debra Dalgleish wrote: -----

Are macros enabled in the workbook?

 

[Debra Dalgleish]

I'm not sure what you mean by "No macros." If this code is going to
work, you have to enable macros.

Choose Tools>Macros>Sceurity, and make sure it is set to Medium.
When you open the workbook that contains this code, click the button to
enable macros.

 

[Guest]

Debra, all of a sudden it did work after I removed a real module with the same private sub statements.
Maybe that was the reason single step debugging didnt work.
Thanks for your support in this.

Another small question:
Is there a way to show a form combo box only when you select the cell that contains one?
If not my form shows a lot of combo boxes which makes it quite messy.
I know you can prevent these boxes to show up in print, but can it be done on the display itself?

Toos

 

[Debra Dalgleish]

You can set the properties of a combo box from the Control toolbox, so
its arrow is visible only when the combo box has been selected.

However, this feature isn't available for combo boxes from the Forms
toolbar. You could use programming to hide or show the combo boxes, but
perhaps you could replace them with data validation dropdown lists.

 

[kemperflow]

Debra,
Your code to zoom the dropdown is a great idea, but it got me slightl
annoyed in my particular spreadsheet. Everytime I clicked off th
dropdown it zoomed me back to your preset level, not the preset level
was at.. Fixed it by setting the prior zoom level with a publi
variable, ZoomLevel. Just an idea..
..

Public ZoomLevel As Integer

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address = "$A$100" Then
ZoomLevel = ActiveWindow.Zoom
ActiveWindow.Zoom = 120
Else
ActiveWindow.Zoom = ZoomLevel
End If

End Su