Form/Input box

[S Swedowski]

I am a intermediate user of Excel. I don't know if this is possible but this
is what I would like to do. I am going to a trade show that particpants will
come to our booth and be able to register for a prize. They will only be
able to enter once. But over the course of the 3 day show, people come back
2 or even 3 times. So is it possible to have a form/box that would I can:

1. enter a unique identifer # into and then a search would look for a
duplicate.
2. if duplicate is found, would show the duplicate information.
3. if no duplicate is found, the cursor would go to the next empty record
and be ready for the input of the new information.

If you need more information in order to help me, please email me at
(e-mail address removed)

Thank you!!

 

[Joel]

The best way is to use FIND. Here is code to help you get started.

ID = 123
set FindRange = columns("A:A")
set FindRange.find(what:=ID,lookin:=xlvalues)
if not c is nothing then
LastRow = Range("A" & Row.Count).end(xlup).row
NewRow = LastRow + 1
else
msgbox("Duplicate" & c.offset(0,1))
end if

 

[S Swedowski]

I'll try it

Thanks,

Susie

The best way is to use FIND. Here is code to help you get started.

ID = 123
set FindRange = columns("A:A")
set FindRange.find(what:=ID,lookin:=xlvalues)
if not c is nothing then
LastRow = Range("A" & Row.Count).end(xlup).row
NewRow = LastRow + 1
else
msgbox("Duplicate" & c.offset(0,1))
end if

 

[john]

Not fully tested but something like the following may be of help.
It checks for Unique ID in Col A and if not found Prompts for Name, Compnay
Name & adds to a Worksheet I named Prize Draw (Change code if different) All
Entries are Date & Time Stamped.

Hope useful

Sub PrizeDraw()

Dim UID As Variant
Dim EntrantName As Variant
Dim CoName As Variant
Dim FoundCell As Range

Title = "Prize Draw"
UID = Application.InputBox(prompt:="Enter Unique ID", Title:="Prize
Draw", Type:=2)
If VarType(UID) = vbBoolean Then
If UID = False Then
Debug.Print "cancelled"
Exit Sub
End If
End If

Search = UID
With ThisWorkbook.Worksheets("Prize Draw")
Set FoundCell = .Columns(1).Find(Search, LookIn:=xlValues,
LookAt:=xlWhole)


If FoundCell Is Nothing = False Then
msg = MsgBox("UID " & UID & " Exists", 16, Title)

Else

EntrantName = Application.InputBox(prompt:="Enter Entrant's
Name", Title:="Prize Draw", Type:=2)
If VarType(EntrantName) = vbBoolean Then
If EntrantName = False Then
Debug.Print "cancelled"
Exit Sub
End If
End If
CoName = Application.InputBox(prompt:="Enter Company Name",
Title:="Prize Draw", Type:=2)
If VarType(CoName) = vbBoolean Then
If CoName = False Then
Debug.Print "cancelled"
Exit Sub
End If
End If

LastRow = .Cells(Rows.Count, "A").End(xlUp).Row + 1
.Cells(LastRow, 1).Value = UID
.Cells(LastRow, 2).Value = EntrantName
.Cells(LastRow, 3).Value = CoName
.Cells(LastRow, 4).Value = Format(Date, "dd/mm/yyyy")
.Cells(LastRow, 5).Value = Format(Time, "HH:MM:SS")

End If
End With
End Sub