Check column for a value and if not there execute series of functi

[aintlifegrand79]

I am trying to make it so the value I enter into the text box
(tbProjectNumber) in a userform is checked against the values already entered
into the database in column A of worksheet 1. If it is already entered I
want it to pull up a different userform (ufErrorHandler), if it is not
entered I want it to enter the project number along with all the other
information on the userform. I have the the code for entering all the
information to into the database but can't figure out how to perform the
check. Here is what I have I know it is kind of long sorry but, any help
would be great.

Private Sub SaveButton_Click()
' Activate Sheet1
Sheet1.Activate
' Check to see if project number already entered
If tbProjectNumber.Value <>
Sheet1.Columns(1).Find(tbProjectNumber.Value).Offset(0, 0).Value Then
ufErrorHandler.Show
If tbProjectNumber.Value =
Sheet1.Columns(1).Find(tbProjectNumber.Value).Offset(0, 0).Value Then
' Determine the next empty row
NextRow = _
Application.WorksheetFunction.CountA(Range("A:A")) + 1
' Transfer to Sheet1(Project Type)
Cells(NextRow, 1) = tbProjectNumber.Text
Cells(NextRow, 2) = tbAEName.Text
Cells(NextRow, 3) = tbSiteOwnerName.Text
Cells(NextRow, 4) = tbPGLead.Text
Cells(NextRow, 5) = cbProjectType.Text
Cells(NextRow, 6) = cbProjectCategory.Text
' Activate Sheet2
Sheet2.Activate
' Transfer to Sheet2(Project Definition)
Cells(NextRow, 1) = tbProjectNumber.Text
Cells(NextRow, 2) = tbAEName.Text
Cells(NextRow, 3) = tbSiteOwnerName.Text
Cells(NextRow, 4) = tbPGLead.Text
Cells(NextRow, 5) = tbAELocation.Text
Cells(NextRow, 6) = tbSiteOwnerLocation.Text
Cells(NextRow, 7) = tbSiteName.Text
Cells(NextRow, 8) = tbSiteUnitNumber.Text
Cells(NextRow, 9) = cbApplication.Text
' Set the controls for the next entry
tbProjectNumber.SetFocus
Sheet1.Activate
End If
End Sub

 

[Rick Rothstein \(MVP - VB\)]

Try this If-Then test in place of the one you have...

If Not Sheet1.Columns(1).Find(tbProjectNumber.Value) Is Nothing Then
ufErrorHandler.Show
End If

You can turn that into your one-line If-Then statement if you want (I used
the multi-line version to stop newsreaders from word wrapping the
statement).

Rick

 

[aintlifegrand79]

This worked for bringing up ufErrorHandler but still entered the information
into the database. I want it to only enter the information if ufErrorHandler
is not brought up. Thank for your help Rick and/or anyone else that can help
with my problem.

 

[Rick Rothstein \(MVP - VB\)]

Maybe this?

If Not Sheet1.Columns(1).Find(tbProjectNumber.Value) Is Nothing Then
ufErrorHandler.Show
Exit Sub
End If

Rick

 

[aintlifegrand79]

Got it to work thanks Rick

Maybe this?

If Not Sheet1.Columns(1).Find(tbProjectNumber.Value) Is Nothing Then
ufErrorHandler.Show
Exit Sub
End If

Rick