Solved: Display if Found, if not edit???

[Hoopsah]

Hi,

I have a spreadsheet with various columns of data.

I am creating an input template for this data, the user will be presented with a screen asking them the Reference of their job.

Depending on the answer, I want excel to check if the number is in the database/spreadsheet.

If it is, then the appropriate cells within the input sheet are filled in (This will be date received, logged by etc) and allow the user to complete the remaining cells. On completin and Submitting details, it should then overwrite the previous entry.

If it is not already in the database/spreadsheet then no boxes are filled in. User completes cells and submits.

http://www.theofficeexperts.com/forum/images/smilies/dontknow.gif

[Bob Phillips]

Here is the code for the two buttons (assign the macros accordingly)



Public Sub SubmitDetails()
Dim pos As Long
Dim NextRow As Long

On Error Resume Next
pos = Application.Match(Range("H9").Value, Worksheets("Process 2").Columns(3), 0)
On Error GoTo 0

With Worksheets("Process 2")

If pos > 0 Then

.Cells(pos, "B").Value = Range("H12").Value
.Cells(pos, "D").Value = Range("H14").Value
.Cells(pos, "E").Value = Range("H16").Value
.Cells(pos, "G").Value = Range("H18").Value
Else

NextRow = .Cells(.Rows.Count, "G").End(xlUp).Row + 1
.Cells(NextRow, "B").Value = Range("H12").Value
.Cells(NextRow, "D").Value = Range("H14").Value
.Cells(NextRow, "E").Value = Range("H16").Value
.Cells(NextRow, "G").Value = Range("H18").Value
End If
End With
End Sub

Public Sub ClearScreen()

Range("H9").Value = ""
Range("H12").Value = ""
Range("H14").Value = ""
Range("H16").Value = ""
Range("H18").Value = ""
End Sub


and here is some to populate the form



Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H9" '<== change to suit
Dim pos As Long

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target

On Error Resume Next
pos = Application.Match(.Value, Worksheets("Process 2").Columns(3), 0)
On Error GoTo 0
If pos > 0 Then

Me.Range("H12").Value = Worksheets("Process 2").Cells(pos, "B").Value
Me.Range("H14").Value = Worksheets("Process 2").Cells(pos, "D").Value
Me.Range("H16").Value = Worksheets("Process 2").Cells(pos, "E").Value
Me.Range("H18").Value = Worksheets("Process 2").Cells(pos, "G").Value
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


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.

[Hoopsah]

WOW!

XLD you are a star, this is absolutely perfect.

Thanks for your help (Again)

Cheers

Hoopsah :thumb