List Box Size

[Orf Bartrop]

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

 

[Frank Kabel]

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

 

[Debra Dalgleish]

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
(View>Toolbars).

 

[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("CustomList"),
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
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
End Sub

 

[Debra Dalgleish]

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("CustomList"),
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]

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]

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]

That didn't work, Debra, in fact it played havoc with another macros
that deletes data.

For some unknown reason, adding data to any other column unprotects the
sheet so that all works well. However, if column "C" is the first one
selected then the sheet remains protected and the drop down list does
not work.
With the sheet unprotected, other columns that I do not want the user
playing with are vulnerable. So first things first - how do I unprotect
the sheet when column "C" is first clicked and before data is added?

Orf

 

[Debra Dalgleish]

For the macro that deletes data, you should be able to avoid problems by
wrapping the code with the following lines:

Application.EnableEvents = False
'your code
Application.EnableEvents = True

You could also add code to the Workbook_Open event, to unprotect the
sheet if column C contains the active cell.

The code, as I wrote it, will unprotect the sheet when cell C2 is
selected. To unprotect if any cell in column C is selected, change the
code to:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 3 Then
ActiveSheet.Unprotect
Else
ActiveSheet.Protect
End If
End Sub

 

[Orf Bartrop]

Thanks Debra, your code works well. However, I now find that if an
incorrect entry is typed into the drop down list box (column "C") and
then deleted, it deletes the second record in the CustomList on the
Lists sheet.

The worksheet code is:



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

' 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
On Error GoTo wt_exit:
ws.Unprotect
If Application.WorksheetFunction.CountIf(ws.Range("CustomList"),
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("B1:B" & i).Name = "CustomList"
ws.Range("CustomList").Sort Key1:=ws.Range("B1"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End If
End If
ws.Protect

wt_exit:
Application.EnableEvents = True

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Column = 3 Then
ActiveSheet.Unprotect
Else
ActiveSheet.Protect
End If

End Sub


The code on the Lists sheet is:



Private Sub Worksheet_Change(ByVal Target As Range)

Columns(2).Sort Key1:=Range("B1"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

End Sub



Can you see where the problem is?

My aim is to allow either selection from the list or to make a new entry
that will be added to the list. Mistakes are bound to be made by the
user so I have to guard against this.

Orf

 

[Debra Dalgleish]

Before adding the target value to the word list, you could check for an
empty string in the target cell, e.g.:

If Target.Value = "" Then Exit Sub

 

[Orf Bartrop]

Thank you Debra, you solved my problem and now all is working but not
before I found that in my bumbling attempt to create my masterpiece I
must have corrupted not only my file but Excel itself. No wonder I have
had difficulties over the last week or so getting scripts to run.

I found my drop down list was only displaying 2 items instead of 8 and
the list would not sort correctly. I ended up doing a repair on Excel
and deleted the drop down box and recreated it. Then the system started
to work as designed.

My first, and probably my last, foray into Excel was enlightening but
not all pleasure but thanks to you, Bob Phillips and this wonderfully
helpful newsgroup I achieved my goal.

Orf

 

[Debra Dalgleish]

You're welcome, and thanks for letting me know that you've finally got
it working. If you venture into Excel territory again, I hope it gives
you less trouble!