Solved: Is it possible for a pop up window to show data already on file?

[Aussiebear]

Can a pop up window show data already on file ( in this case names of people) and then allow you to pick on a name and have that name entered into the cell?

In the example spreadsheet if you were to type in the name Smith, up pops a window with all the variations of Smith that currently exist on file.

[Bob Phillips]

Try this



Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H10"
Dim iLastRow As Long
Dim i As Long
Dim sMsg As String

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
iLastRow = Me.Cells(Me.Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
If Me.Cells(i, "A").Value Like "*" & .Value & "*" Then
sMsg = sMsg & Me.Cells(i, "A").Value & vbTab & _
Me.Cells(i, "B").Value & vbTab & Me.Cells(i, "C").Value & vbNewLine
End If
Next i
MsgBox "(Currently on file ...)" & vbNewLine & sMsg
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

[johnske]

Can a pop up window show data already on file ( in this case names of people) and then allow you to pick on a name and have that name entered into the cell?

In the example spreadsheet if you were to type in the name Smith, up pops a window with all the variations of Smith that currently exist on file.How about a dropdown validation list? (attached demo)

[Aussiebear]

Xld, How does this pick up the names list which is on Sheet 2? This will be where the full list of Names is to be kept.

[Aussiebear]

How about a dropdown validation list? (attached demo)

Attached demo???

[Aussiebear]

I can see the list of names growing to well over 1000 names, and at times I'll need to pick the correct name by having the address showing as well

[Aussiebear]

Can a validation list have more than one column?

[Bob Phillips]

Not tested, but this should do it



Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H10"
Dim iLastRow As Long
Dim i As Long
Dim sMsg As String

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Worksheet("Sheet2")
iLastRow = .Cells(Me.Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
If .Cells(i, "A").Value Like "*" & Target.Value & "*" Then
sMsg = sMsg & .Cells(i, "A").Value & vbTab & _
.Cells(i, "B").Value & vbTab & _
.Cells(i, "C").Value & vbNewLine
End If
Next i
MsgBox "(Currently on file ...)" & vbNewLine & sMsg
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

[Aussiebear]

Sorry but you'll have to help me some more here. What does this code you have provided, do??

[Bob Phillips]

This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

[Aussiebear]

What triggers the code, and where can I expect the popup to occur?

[Bob Phillips]

Putting the target value in H10

[Aussiebear]

Nothing happening..... but then I'm currently using Excel 2007 beta version

[Aussiebear]

I type the name Smith in H10 and nothing happens.....

[lucas]

You have to put it in the code module for sheet 2 of your example Aussibear. Bob also made a small speelling error on this line:

With Worksheet("Sheet2")


need to add an s to the word worksheet

With Worksheets("Sheet2")

[Bob Phillips]

You have to put it in the code module for sheet 2 of your example Aussibear. Bob also made a small speelling error on this line:

With Worksheet("Sheet2")

need to add an s to the word worksheet

With Worksheets("Sheet2")



Darn! I tested the original version, but not when Aussiebear asked for it on a different sheet. Sod's law!

[Aussiebear]

Yep, I'm a devout Sod's Law disciple....

[lucas]

Hard to find anything wrong with Bobs posts. Not bad considering how often he gives excellent solutions.

[Charlize]

Not tested, but this should do it



Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H10"
Dim iLastRow As Long
Dim i As Long
Dim sMsg As String

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Worksheet("Sheet2")
iLastRow = .Cells(Me.Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
If .Cells(i, "A").Value Like "*" & Target.Value & "*" Then
sMsg = sMsg & .Cells(i, "A").Value & vbTab & _
.Cells(i, "B").Value & vbTab & _
.Cells(i, "C").Value & vbNewLine
End If
Next i
MsgBox "(Currently on file ...)" & vbNewLine & sMsg
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

I've changed the line :
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
with

If Target.Column = 1

Why, when you type for instance Sm and you leave the cell then a window pops up with the possible names with Sm in it. So from 1 to +/-65000 instead of only H10

And removed the line :
[VBA]
Const WS_RANGE As String = "H10"


Attached a little example of this try-out.

Hope this will show you the way

Charlize

[Bob Phillips]

I have no idea what point you are making here.

[Charlize]

I have no idea what point you are making here.

I'm not making any point at all. I've only changed your code a little (a line or two) so that the window would pop up in column A, when something has changed, instead of only in cell H10.

Charlize

[Aussiebear]

If I find that my entry is new, (i.e. That it currently doesn't exist in the pop up window list), can I type it into the list on Sheet 1 and on "Save" have the new names added to the Names range on Sheet 2?

What has been happenig is that data entry clerks have been duplicating names of growers, over silly mistakes like;

"Smith Farming P/L" & "Smith Farming Pty Ltd "or even "Smith Farming" or
"M/S 434" against "M.S 434" or "MS 434" when in reality all were from the same property, same town, same Postcode, and same State.

If the list box can show existing names and addresses then perhaps as they enter the data it would become easier to determine if they are trying to re enter the same client.

Ted

[Bob Phillips]

Ted,

How do you know that it is new? My recollection of this is that you were typing in a partial name and getting a list returned of how many items are like that partial name.

[Aussiebear]

If I don't find it in the list box list then I'm assuming its going to be a new entry. Can I update the List box list from the range list I'm building on the main sheet?

[Charlize]

If I don't find it in the list box list then I'm assuming its going to be a new entry. Can I update the List box list from the range list I'm building on the main sheet?

As requested a different manner of showing a window with possible matches.

This one triggers after hitting enter and shows a listbox instead of a popup window. So a partial entry and hitting enter.
The code assumes that you are searching for the activecell.value -1 (the row above).

You can adapt the form with your 3 textboxes and a commandbutton for adding names to sheet2.

Charlize