| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check column for a value and if not there execute series of functi
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).Offs et(0, 0).Value Then ufErrorHandler.Show If tbProjectNumber.Value = Sheet1.Columns(1).Find(tbProjectNumber.Value).Offs et(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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Check column for a value and if not there execute series of functi
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" wrote in message ... 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).Offs et(0, 0).Value Then ufErrorHandler.Show If tbProjectNumber.Value = Sheet1.Columns(1).Find(tbProjectNumber.Value).Offs et(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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Check column for a value and if not there execute series of fu
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)" wrote: 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" wrote in message ... 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).Offs et(0, 0).Value Then ufErrorHandler.Show If tbProjectNumber.Value = Sheet1.Columns(1).Find(tbProjectNumber.Value).Offs et(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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Check column for a value and if not there execute series of fu
Maybe this?
If Not Sheet1.Columns(1).Find(tbProjectNumber.Value) Is Nothing Then ufErrorHandler.Show Exit Sub End If Rick "aintlifegrand79" wrote in message ... 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)" wrote: 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" wrote in message ... 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).Offs et(0, 0).Value Then ufErrorHandler.Show If tbProjectNumber.Value = Sheet1.Columns(1).Find(tbProjectNumber.Value).Offs et(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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Check column for a value and if not there execute series of fu
Got it to work thanks Rick
"Rick Rothstein (MVP - VB)" wrote: Maybe this? If Not Sheet1.Columns(1).Find(tbProjectNumber.Value) Is Nothing Then ufErrorHandler.Show Exit Sub End If Rick "aintlifegrand79" wrote in message ... 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)" wrote: 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" wrote in message ... 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).Offs et(0, 0).Value Then ufErrorHandler.Show If tbProjectNumber.Value = Sheet1.Columns(1).Find(tbProjectNumber.Value).Offs et(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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how to plot column chart with one series against multiple series. | Charts and Charting in Excel | |||
| Check column contents for series | Excel Programming | |||
| SORT (Excel) doesn't work in a column created from a LOOKUP functi | Excel Programming | |||
| Check My Code Please - Execute Access Macro | Excel Programming | |||
| getting the column address of an argument to a user defined functi | Excel Programming | |||
Hybrid Mode
