dynamic validation list with 'add' capability

[swtrader]

The following is used with a dynamic validation list that includes "(new entry)" as one of the selections. When (new entry) is selected, a pop-up form appears, asking for the new entry.

I copied this from web source. The quotation marks did not copy correctly so I replaced them. After replacing them, it ran correctly 1 time -- but I can't get it to fire at all.
--------------------------------------------------------

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

Dim vResp As Variant
Dim sTestValid As String

'Make sure the cell has validation
On Error Resume Next
sTestValid = Target.Validation.Formula1
On Error GoTo 0

'If the validation refers to our list and the user
'selected New entry
If sTestValid = "=ValList" Then
If Target.Value = "(new entry)" Then

'Get the new value from the user
vResp = InputBox("Enter new item", "New Entry")

'If the user didn’t click cancel
If Len(vResp) > 0 Then
'add the new entry to just below ValList
With Me.Range("ValList")
.Cells(.Cells.Count + 1).Value = vResp
End With

'Set the cell to the new entry
Target.Value = vResp
Else
'If the user cancelled, clear the cell
Target.ClearContents
End If
End If
End If

Application.EnableEvents = True

End Sub




swtrader
-- If you don't know where you're going, you'll always know when you're not there.

 

[Fr33dan]

Which version of Excel are you running? There is a issue with Excel 97 that makes _Change macros not work with data validation in which case you could just add a button next to the list to create a new

 

[swtrader]

2002

swtrader
-- If you don't know where you're going, you'll always know when you're not there.

 

[SkipVought]

Hi,

For one thing, ther is nothing that renames the range once the new item is added.

I added this code...

                With Me.Range("ValList")
                    .Cells(.Cells.Count + 1).Value = vResp[b]
                    Application.DisplayAlerts = False
                    .CurrentRegion.CreateNames True, False, False, False
                    Application.DisplayAlerts = True[/b]
                End With

assuming that

1) the List Heading is ValList
2) the list is isolated.

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[Fr33dan]

Sorry I can't help with the _Change thing(I'm running 97) the the following code will rename the range not assuming the list is isolated or has a heading

With Me.Range("ValList")
            .Cells(.Cells.count + 1).Value = "4"
            Application.DisplayAlerts = False
            ActiveWorkbook.Names.Add Name:="ValList", RefersToR1C1Local:= _
               "=R" & .row & "C" & .column & ":R" & (.Rows.count + .row) _
               & "C" & (.Columns.count + .column - 1) & ""
            Application.DisplayAlerts = True
End With

 

[SkipVought]

"After replacing them, it ran correctly 1 time -- but I can't get it to fire at all."

If you stopped the procedure, ad it di not run to conclusion, then EnableEvents is FALSE and no event will subsequently be detected.

Personally, I'd remove the two EnableEvents statements as they serve no purpose in this procedure the way it currently runs.

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[swtrader]

I re-copied the original code (from the website) and replaced all single and double quotes EXCEPT for this one:

If sTestValid = “ = ValList” Then

And it runs.

When I replace these original quotes
If sTestValid = " = ValList" Then

it won't run.

??

Thanks for your help. I'm baffled.