List Box Size

I have created a validation list box but is too small to display all
entries without scrolling. Is there any way to increase the size of the box?

Also, is it possible to have the box display entries from the list
starting at a point that matches the first 2 characters typed?

Orf Bartrop

Hi
for the first questiion see:
http://www.contextures.com/xlDataVal08.html#Larger

For the second one: Not possible AFAIK with the data validation listbox

--
Regards
Frank Kabel
Frankfurt, Germany

"Orf Bartrop" schrieb im Newsbeitrag
...
I have created a validation list box but is too small to display all
entries without scrolling. Is there any way to increase the size of
the box?

Also, is it possible to have the box display entries from the list
starting at a point that matches the first 2 characters typed?

Orf Bartrop

Thanks, Frank.

From the link I re-organised my drop down list and automated it.
However, when I go to Tools/Protect and protect the sheet it prevented
the list from appearing. I have set the cells containing the drop down
box and the list itself to unprotect but that does not make it work.

One thing I did do and do not know if it is correct is in the sheet code
I already had a script in there as shown below but not the part between
the XXXXXXXXXXXXXXXXXXX lines (nor those lines).

The part between the xxxxxxxxxxxxxxx is designed to automate the adding
of words to the list if they are not present. There is another code on
the list sheet that sorts the list into alphabetical order.

To get the system to work I have to leave both sheets unprotected, which
I really do not want to do.

How can I fix that problem?

Orf Bartrop


Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If .Column = 4 Or .Column = 5 Or .Column = 6 Then
If .Row 3 Then
With Cells(.Row, "A")
.Value = Format(Date, "dd mmm yyyy")
End With
End If
End If
End With

ws_exit:
Application.EnableEvents = True
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
' Transfer word to list

On Error Resume Next
Dim ws As Worksheet
Dim i As Integer

Set ws = Worksheets("Lists")
If Target.Column = 3 And Target.Row 5 Then
If Application.WorksheetFunction.CountIf(ws.Range("Cu stomList"),
Target.Value) Then
Exit Sub
Else
i = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1
ws.Range("B" & i).Value = Target.Value
ws.Range("CustomList").Sort Key1:=ws.Range("B1"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End If
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxx
End Sub


Frank Kabel wrote:

Hi
for the first questiion see:
http://www.contextures.com/xlDataVal08.html#Larger

You can protect and unprotect the sheet in the code. Also, you should
redefine the range, unless you're using a dynamic name:

'========================
Set ws = Worksheets("Lists")
If Target.Column = 3 And Target.Row 5 Then
If Application.WorksheetFunction.CountIf(ws.Range("Cu stomList"),
Target.Value) Then
Exit Sub
Else
ws.Unprotect
i = ws.Cells(Rows.Count, 2).End(xlUp).Row + 1
ws.Range("B" & i).Value = Target.Value
ws.Range("B1:B" & i).Name = "CustomList"
ws.Range("CustomList").Sort Key1:=ws.Range("B1"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
ws.Protect
End If
End If
'===========================

Orf Bartrop wrote:
Thanks, Frank.

From the link I re-organised my drop down list and automated it.
However, when I go to Tools/Protect and protect the sheet it prevented
the list from appearing. I have set the cells containing the drop down
box and the list itself to unprotect but that does not make it work.

One thing I did do and do not know if it is correct is in the sheet code
I already had a script in there as shown below but not the part between
the XXXXXXXXXXXXXXXXXXX lines (nor those lines).

The part between the xxxxxxxxxxxxxxx is designed to automate the adding
of words to the list if they are not present. There is another code on
the list sheet that sorts the list into alphabetical order.

To get the system to work I have to leave both sheets unprotected, which
I really do not want to do.

How can I fix that problem?

Orf Bartrop


Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If .Column = 4 Or .Column = 5 Or .Column = 6 Then
If .Row 3 Then
With Cells(.Row, "A")
.Value = Format(Date, "dd mmm yyyy")
End With
End If
End If
End With

ws_exit:
Application.EnableEvents = True
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx ' Transfer word to list

On Error Resume Next
Dim ws As Worksheet
Dim i As Integer

Set ws = Worksheets("Lists")
If Target.Column = 3 And Target.Row 5 Then
If Application.WorksheetFunction.CountIf(ws.Range("Cu stomList"),
Target.Value) Then
Exit Sub
Else
i = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1
ws.Range("B" & i).Value = Target.Value
ws.Range("CustomList").Sort Key1:=ws.Range("B1"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End If
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxx
End Sub


Frank Kabel wrote:

Hi
for the first questiion see:
http://www.contextures.com/xlDataVal08.html#Larger






--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Thanks Debra, but your code made no difference but I have installed it
anyway. Perhaps I should explain more fully what is happening.

If I do not type anything in the cell, on clicking the down arrow the
drop down list will open showing the last 7 items in the list followed
by a blank. I would have thought it would open with the first 8 items.

That aside, what I am attempting to do, and it works if the sheet with
the box and the sheet with the list are both unprotected, is to be able
to type a letter in the cell and have the list open with that letter at
the top of the list. My list contains each letter of the alphabet as a
separate entity so, for instance, typing an "f" would open the drop down
list at "f".

If the sheets are protected, I can get this feature working if having
typed a letter I change the focus to another cell and then return to the
original cell and click the down arrow. Otherwise clicking the drop down
arrow does nothing.

Orf


Debra Dalgleish wrote:

You can protect and unprotect the sheet in the code. Also, you should
redefine the range, unless you're using a dynamic name:

'========================
Set ws = Worksheets("Lists")
If Target.Column = 3 And Target.Row 5 Then
If Application.WorksheetFunction.CountIf(ws.Range("Cu stomList"),
Target.Value) Then
Exit Sub
Else
ws.Unprotect
i = ws.Cells(Rows.Count, 2).End(xlUp).Row + 1
ws.Range("B" & i).Value = Target.Value
ws.Range("B1:B" & i).Name = "CustomList"
ws.Range("CustomList").Sort Key1:=ws.Range("B1"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
ws.Protect
End If
End If
'===========================

Orf Bartrop wrote:

Thanks, Frank.

From the link I re-organised my drop down list and automated it.
However, when I go to Tools/Protect and protect the sheet it
prevented the list from appearing. I have set the cells containing
the drop down box and the list itself to unprotect but that does not
make it work.

One thing I did do and do not know if it is correct is in the sheet
code I already had a script in there as shown below but not the part
between the XXXXXXXXXXXXXXXXXXX lines (nor those lines).

The part between the xxxxxxxxxxxxxxx is designed to automate the
adding of words to the list if they are not present. There is another
code on the list sheet that sorts the list into alphabetical order.

To get the system to work I have to leave both sheets unprotected,
which I really do not want to do.

How can I fix that problem?

Orf Bartrop


Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If .Column = 4 Or .Column = 5 Or .Column = 6 Then
If .Row 3 Then
With Cells(.Row, "A")
.Value = Format(Date, "dd mmm yyyy")
End With
End If
End If
End With

ws_exit:
Application.EnableEvents = True
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx ' Transfer word
to list

On Error Resume Next
Dim ws As Worksheet
Dim i As Integer

Set ws = Worksheets("Lists")
If Target.Column = 3 And Target.Row 5 Then
If
Application.WorksheetFunction.CountIf(ws.Range("Cu stomList"),
Target.Value) Then
Exit Sub
Else
i = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1
ws.Range("B" & i).Value = Target.Value
ws.Range("CustomList").Sort Key1:=ws.Range("B1"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End If
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxx
End Sub


Frank Kabel wrote:

Hi
for the first questiion see:
http://www.contextures.com/xlDataVal08.html#Larger

Thanks for the explanation. To get that feature to work as you
described, you could use the SelectionChange event to unprotect the
sheet if the data validation cell is selected. For example:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$C$2" Then
ActiveSheet.Unprotect
Else
ActiveSheet.Protect
End If
End Sub


Orf Bartrop wrote:
Thanks Debra, but your code made no difference but I have installed it
anyway. Perhaps I should explain more fully what is happening.

If I do not type anything in the cell, on clicking the down arrow the
drop down list will open showing the last 7 items in the list followed
by a blank. I would have thought it would open with the first 8 items.

That aside, what I am attempting to do, and it works if the sheet with
the box and the sheet with the list are both unprotected, is to be able
to type a letter in the cell and have the list open with that letter at
the top of the list. My list contains each letter of the alphabet as a
separate entity so, for instance, typing an "f" would open the drop down
list at "f".

If the sheets are protected, I can get this feature working if having
typed a letter I change the focus to another cell and then return to the
original cell and click the down arrow. Otherwise clicking the drop down
arrow does nothing.

Orf


Debra Dalgleish wrote:

You can protect and unprotect the sheet in the code. Also, you should
redefine the range, unless you're using a dynamic name:

'========================
Set ws = Worksheets("Lists")
If Target.Column = 3 And Target.Row 5 Then
If Application.WorksheetFunction.CountIf(ws.Range("Cu stomList"),
Target.Value) Then
Exit Sub
Else
ws.Unprotect
i = ws.Cells(Rows.Count, 2).End(xlUp).Row + 1
ws.Range("B" & i).Value = Target.Value
ws.Range("B1:B" & i).Name = "CustomList"
ws.Range("CustomList").Sort Key1:=ws.Range("B1"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
ws.Protect
End If
End If
'===========================

Orf Bartrop wrote:

Thanks, Frank.

From the link I re-organised my drop down list and automated it.
However, when I go to Tools/Protect and protect the sheet it
prevented the list from appearing. I have set the cells containing
the drop down box and the list itself to unprotect but that does not
make it work.

One thing I did do and do not know if it is correct is in the sheet
code I already had a script in there as shown below but not the part
between the XXXXXXXXXXXXXXXXXXX lines (nor those lines).

The part between the xxxxxxxxxxxxxxx is designed to automate the
adding of words to the list if they are not present. There is another
code on the list sheet that sorts the list into alphabetical order.

To get the system to work I have to leave both sheets unprotected,
which I really do not want to do.

How can I fix that problem?

Orf Bartrop


Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If .Column = 4 Or .Column = 5 Or .Column = 6 Then
If .Row 3 Then
With Cells(.Row, "A")
.Value = Format(Date, "dd mmm yyyy")
End With
End If
End If
End With

ws_exit:
Application.EnableEvents = True
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx ' Transfer word
to list

On Error Resume Next
Dim ws As Worksheet
Dim i As Integer

Set ws = Worksheets("Lists")
If Target.Column = 3 And Target.Row 5 Then
If
Application.WorksheetFunction.CountIf(ws.Range("Cu stomList"),
Target.Value) Then
Exit Sub
Else
i = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1
ws.Range("B" & i).Value = Target.Value
ws.Range("CustomList").Sort Key1:=ws.Range("B1"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End If
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxx
End Sub


Frank Kabel wrote:

Hi
for the first questiion see:
http://www.contextures.com/xlDataVal08.html#Larger









--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

No, you can't increase the length of the list from the default eight
items, or match characters as they're typed.

These features are available in a combobox from the Control toolbox
(ViewToolbars).


Orf Bartrop wrote:
I have created a validation list box but is too small to display all
entries without scrolling. Is there any way to increase the size of the
box?

Also, is it possible to have the box display entries from the list
starting at a point that matches the first 2 characters typed?


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html