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

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

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

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 wrote:

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

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 wrote:
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 wrote:

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

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("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("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