i am exploring this http://www.contextures.com/xlForm02.html

would like to improve like how to i prevent no repeated values hava been
entered if users click "Add to Database"

Thanks!

What item(s) cannot be repeated (based on the sample file provided) i.e. what
do want checked for duplicates?

"kyoshirou" wrote:

i am exploring this http://www.contextures.com/xlForm02.html

would like to improve like how to i prevent no repeated values hava been
entered if users click "Add to Database"

Thanks!

Those values enter inside A1,A2,A3 cannot be repeated.
Like cat or dog.
Or numerical no. like 122 or 221

"Toppers" wrote:

What item(s) cannot be repeated (based on the sample file provided) i.e. what
do want checked for duplicates?

"kyoshirou" wrote:

i am exploring this http://www.contextures.com/xlForm02.html

would like to improve like how to i prevent no repeated values hava been
entered if users click "Add to Database"

Thanks!

You'll have to pick out the cell on the input worksheet that defines the
key--and the column in the history worksheet that holds that key. Then you can
look to see if there is a match with something like:

Option Explicit
Sub UpdateLogWorksheet()

Dim historyWks As Worksheet
Dim inputWks As Worksheet

Dim nextRow As Long
Dim oCol As Long

Dim myRng As Range
Dim myCopy As String
Dim myCell As Range
Dim Res As Variant
Dim ErrFound As Boolean

'cells to copy from Input sheet - some contain formulas
myCopy = "D5,D7,D9,D11,D13"

Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("PartsData")

With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With

With inputWks
Set myRng = .Range(myCopy)

If Application.CountA(myRng) < myRng.Cells.Count Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With

With historyWks
Res = Application.Match(inputWks.Range("D5"), .Range("C:C"), 0)
If IsNumeric(Res) Then
'already there
MsgBox "error???"
ErrFound = True
Else
ErrFound = False
With .Cells(nextRow, "A")
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
.Cells(nextRow, "B").Value = Application.UserName
oCol = 3
For Each myCell In myRng.Cells
historyWks.Cells(nextRow, oCol).Value = myCell.Value
oCol = oCol + 1
Next myCell
End If
End With

If ErrFound Then
'keep the data??
Else
'clear input cells that contain constants
With inputWks
On Error Resume Next
With .Range(myCopy).Cells.SpecialCells(xlCellTypeConsta nts)
.ClearContents
Application.Goto .Cells(1) ', Scroll:=True
End With
On Error GoTo 0
End With
End If

End Sub

(Compiled, but not tested.)

In this line,
Res = Application.Match(inputWks.Range("D5"), .Range("C:C"), 0)
I used D5 to hold the key in the input worksheet. And column C of the history
worksheet held all the key data.




kyoshirou wrote:

i am exploring this http://www.contextures.com/xlForm02.html

would like to improve like how to i prevent no repeated values hava been
entered if users click "Add to Database"

Thanks!

--

Dave Peterson

you mean just need to add:
Res = Application.Match(inputWks.Range("D5"), .Range("C:C"), 0)

at which line? can advice?

"Dave Peterson" wrote:

You'll have to pick out the cell on the input worksheet that defines the
key--and the column in the history worksheet that holds that key. Then you can
look to see if there is a match with something like:

Option Explicit
Sub UpdateLogWorksheet()

Dim historyWks As Worksheet
Dim inputWks As Worksheet

Dim nextRow As Long
Dim oCol As Long

Dim myRng As Range
Dim myCopy As String
Dim myCell As Range
Dim Res As Variant
Dim ErrFound As Boolean

'cells to copy from Input sheet - some contain formulas
myCopy = "D5,D7,D9,D11,D13"

Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("PartsData")

With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With

With inputWks
Set myRng = .Range(myCopy)

If Application.CountA(myRng) < myRng.Cells.Count Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With

With historyWks
Res = Application.Match(inputWks.Range("D5"), .Range("C:C"), 0)
If IsNumeric(Res) Then
'already there
MsgBox "error???"
ErrFound = True
Else
ErrFound = False
With .Cells(nextRow, "A")
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
.Cells(nextRow, "B").Value = Application.UserName
oCol = 3
For Each myCell In myRng.Cells
historyWks.Cells(nextRow, oCol).Value = myCell.Value
oCol = oCol + 1
Next myCell
End If
End With

If ErrFound Then
'keep the data??
Else
'clear input cells that contain constants
With inputWks
On Error Resume Next
With .Range(myCopy).Cells.SpecialCells(xlCellTypeConsta nts)
.ClearContents
Application.Goto .Cells(1) ', Scroll:=True
End With
On Error GoTo 0
End With
End If

End Sub

(Compiled, but not tested.)

In this line,
Res = Application.Match(inputWks.Range("D5"), .Range("C:C"), 0)
I used D5 to hold the key in the input worksheet. And column C of the history
worksheet held all the key data.




kyoshirou wrote:

i am exploring this http://www.contextures.com/xlForm02.html

would like to improve like how to i prevent no repeated values hava been
entered if users click "Add to Database"

Thanks!

--

Dave Peterson

I don't understand the question.

I put that line right under the section of code that would do the updating to
determine if there was a match before continuing.

kyoshirou wrote:

you mean just need to add:
Res = Application.Match(inputWks.Range("D5"), .Range("C:C"), 0)

at which line? can advice?

"Dave Peterson" wrote:

You'll have to pick out the cell on the input worksheet that defines the
key--and the column in the history worksheet that holds that key. Then you can
look to see if there is a match with something like:

Option Explicit
Sub UpdateLogWorksheet()

Dim historyWks As Worksheet
Dim inputWks As Worksheet

Dim nextRow As Long
Dim oCol As Long

Dim myRng As Range
Dim myCopy As String
Dim myCell As Range
Dim Res As Variant
Dim ErrFound As Boolean

'cells to copy from Input sheet - some contain formulas
myCopy = "D5,D7,D9,D11,D13"

Set inputWks = Worksheets("Input")
Set historyWks = Worksheets("PartsData")

With historyWks
nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
End With

With inputWks
Set myRng = .Range(myCopy)

If Application.CountA(myRng) < myRng.Cells.Count Then
MsgBox "Please fill in all the cells!"
Exit Sub
End If
End With

With historyWks
Res = Application.Match(inputWks.Range("D5"), .Range("C:C"), 0)
If IsNumeric(Res) Then
'already there
MsgBox "error???"
ErrFound = True
Else
ErrFound = False
With .Cells(nextRow, "A")
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
.Cells(nextRow, "B").Value = Application.UserName
oCol = 3
For Each myCell In myRng.Cells
historyWks.Cells(nextRow, oCol).Value = myCell.Value
oCol = oCol + 1
Next myCell
End If
End With

If ErrFound Then
'keep the data??
Else
'clear input cells that contain constants
With inputWks
On Error Resume Next
With .Range(myCopy).Cells.SpecialCells(xlCellTypeConsta nts)
.ClearContents
Application.Goto .Cells(1) ', Scroll:=True
End With
On Error GoTo 0
End With
End If

End Sub

(Compiled, but not tested.)

In this line,
Res = Application.Match(inputWks.Range("D5"), .Range("C:C"), 0)
I used D5 to hold the key in the input worksheet. And column C of the history
worksheet held all the key data.




kyoshirou wrote:

i am exploring this http://www.contextures.com/xlForm02.html

would like to improve like how to i prevent no repeated values hava been
entered if users click "Add to Database"

Thanks!

--

Dave Peterson


--

Dave Peterson