| Home | Forums | Reviews | Articles | Register |
 |
| Thread Tools | Rate Thread |
|
Guest
Posts: n/a
|
|
|
|
||
|
|
| |
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
|
|
|
|
||
|
Guest
Posts: n/a
|
Maybe this?
If Not Sheet1.Columns(1).Find(tbProjectNumber.Value) Is Nothing Then ufErrorHandler.Show Exit Sub End If Rick "aintlifegrand79" <(E-Mail Removed)> wrote in message news  6C85B80-488D-4A42-9D2A-(E-Mail Removed)...> 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" <(E-Mail Removed)> wrote in >> message news:A30C08B0-1FA7-40E0-A9A6-(E-Mail Removed)... >> >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 >> > >> >> |
|
|
|
|
|||
|
Guest
Posts: n/a
|
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" <(E-Mail Removed)> wrote in > message news 6C85B80-488D-4A42-9D2A-(E-Mail Removed)...> > 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" <(E-Mail Removed)> wrote in > >> message news:A30C08B0-1FA7-40E0-A9A6-(E-Mail Removed)... > >> >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 > >> > > >> > >> > > |
|
|
|
|
|||
|
|
| |
|
| Thread Tools | |
| Rate This Thread | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| how to plot column chart with one series against multiple series. | =?Utf-8?B?c3ZlbmthdGVzaG11cnRoeQ==?= | Microsoft Excel Charting | 2 | 16th Oct 2007 09:09 PM |
| Check column contents for series | =?Utf-8?B?QW50aG9ueQ==?= | Microsoft Excel Programming | 2 | 11th Oct 2007 12:24 PM |
| SORT (Excel) doesn't work in a column created from a LOOKUP functi | =?Utf-8?B?bXVybWFu?= | Microsoft Excel Programming | 1 | 8th Apr 2007 08:24 PM |
| getting the column address of an argument to a user defined functi | =?Utf-8?B?U2FsbWFu?= | Microsoft Excel Programming | 2 | 11th Aug 2006 03:12 PM |
| how do I change a line series to a column series in excel? | =?Utf-8?B?TWF0aQ==?= | Microsoft Excel Charting | 1 | 12th May 2005 09:32 AM |
