Userform - validation data. Please help needed
 

Hi, this is my 2nd attempt . Thanks to Dave Peterson I was able to get a
userform running, the first form validates the project number (has to be
unique) and then enter the information in the summary and in the Project tab.
My second userform once the project # is entered validates that the project
exists. this form is to enter negative numbers since are completition stages.
All of the above acomplish already, however what I want to do now is to avoid
mistakes so I'd like to control looking at the summary sheet to match the
information entered in the 2nd userform related to Project#, Client, Project
name and business. As an example if in the first form I enter Project 1,
Client A, Project name A1, Business Apps, if when entering the information in
the 2nd form that fields doesn't match exactly as per above a message error
shows up.
Here is my code
Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Dim res As Variant
Dim iRow1 As Long
Dim ws1 As Worksheet


Set ws = Worksheets("Projects")

'find first empty row in database

With ws
iRow = .Cells(.Rows.Count, 3).End(xlUp).Offset(1, 0).Row

'check for a project number
If Trim(Me.TxtProjectCode.Value) = "" Then
Me.TxtProjectCode.SetFocus
MsgBox "Please enter a project number"
Exit Sub
End If

res = Application.Match(Me.TxtProjectCode.Value, _
Worksheets("Projects").Range("e:e"), 0)
If IsError(res) Then
If IsNumeric(Me.TxtProjectCode.Value) Then
res = Application.Match(CDbl(Me.TxtProjectCode.Value), _
Worksheets("Projects").Range("e:e"), 0)
End If
End If

If IsNumeric(res) Then


' check that Business field is not empty

If Trim(Me.CboBusiness.Value) = "" Then
Me.CboBusiness.SetFocus
MsgBox "Please enter a Business"
Exit Sub
End If



'Check for "" in fields for 1st, 2nd and 3rd year data and replace
with "0"
If Me.TxtJanuary.Value = "" Then
Me.TxtJanuary.Value = "0"
End If
If Me.TxtFebruary.Value = "" Then
Me.TxtFebruary.Value = "0"
End If
If Me.TxtMarch.Value = "" Then
Me.TxtMarch.Value = "0"
End If
If Me.TxtApril.Value = "" Then
Me.TxtApril.Value = "0"
End If
If Me.TxtMay.Value = "" Then
Me.TxtMay.Value = "0"
End If
If Me.TxtJune.Value = "" Then
Me.TxtJune.Value = "0"
End If
If Me.TxtJuly.Value = "" Then
Me.TxtJuly.Value = "0"
End If
If Me.TxtAugust.Value = "" Then
Me.TxtAugust.Value = "0"
End If
If Me.TxtSeptember.Value = "" Then
Me.TxtSeptember.Value = "0"
End If
If Me.TxtOctober.Value = "" Then
Me.TxtOctober.Value = "0"
End If
If Me.TxtNovember.Value = "" Then
Me.TxtNovember.Value = "0"
End If
If Me.TxtDecember.Value = "" Then
Me.TxtDecember.Value = "0"
End If


'Summarize Recognized Revenue 12 months

If IsNumeric(Me.TxtJanuary.Value) _
And IsNumeric(Me.TxtFebruary.Value) _
And IsNumeric(Me.TxtMarch.Value) _
And IsNumeric(Me.TxtApril.Value) _
And IsNumeric(Me.TxtMay.Value) _
And IsNumeric(Me.TxtJune.Value) _
And IsNumeric(Me.TxtJuly.Value) _
And IsNumeric(Me.TxtAugust.Value) _
And IsNumeric(Me.TxtSeptember.Value) _
And IsNumeric(Me.TxtOctober.Value) _
And IsNumeric(Me.TxtNovember.Value) _
And IsNumeric(Me.TxtDecember.Value) Then
Me.TxtTotalRe.Value = CDbl(Me.TxtJanuary.Value) _
+ CDbl(Me.TxtFebruary.Value) _
+ CDbl(Me.TxtMarch.Value) _
+ CDbl(Me.TxtApril.Value) _
+ CDbl(Me.TxtMay.Value) _
+ CDbl(Me.TxtJune.Value) _
+ CDbl(Me.TxtJuly.Value) _
+ CDbl(Me.TxtAugust.Value) _
+ CDbl(Me.TxtSeptember.Value) _
+ CDbl(Me.TxtOctober.Value) _
+ CDbl(Me.TxtNovember.Value) _
+ CDbl(Me.TxtDecember.Value)


End If





popUp = MsgBox("Do you Agree with the Total ", vbYesNo + vbQuestion,
"Gross Revenue & Total Recognized Revenue")
If popUp = vbYes Then

'copy the data to the database
.Cells(iRow, 4).Value = Me.TxtProjectname.Value
.Cells(iRow, 3).Value = Me.TxtClient.Value
.Cells(iRow, 2).Value = Me.CboBusiness.Value
.Cells(iRow, 5).Value = Me.TxtProjectCode.Value
.Cells(iRow, 14).Value = -Me.TxtJanuary.Value
.Cells(iRow, 15).Value = -Me.TxtFebruary.Value
.Cells(iRow, 16).Value = -Me.TxtMarch.Value
.Cells(iRow, 17).Value = -Me.TxtApril.Value
.Cells(iRow, 18).Value = -Me.TxtMay.Value
.Cells(iRow, 19).Value = -Me.TxtJune.Value
.Cells(iRow, 20).Value = -Me.TxtJuly.Value
.Cells(iRow, 21).Value = -Me.TxtAugust.Value
.Cells(iRow, 22).Value = -Me.TxtSeptember.Value
.Cells(iRow, 23).Value = -Me.TxtOctober.Value
.Cells(iRow, 24).Value = -Me.TxtNovember.Value
.Cells(iRow, 25).Value = -Me.TxtDecember.Value
.Cells(iRow, 26).Value = -Me.TxtTotalRe.Value



Else
Exit Sub ' Quit the macro (Pop-up)
End If




'Copy information in the new Tab
Worksheets(Me.TxtProjectCode.Value).Select
Set ws1 = Worksheets(Me.TxtProjectCode.Value)

'find first empty row in database
With ws1
iRow1 = .Cells(.Rows.Count, 3).End(xlUp).Offset(1, 0).Row



'copy the data to the database
.Cells(iRow1, 4).Value = Me.TxtProjectname.Value
.Cells(iRow1, 3).Value = Me.TxtClient.Value
.Cells(iRow1, 2).Value = Me.CboBusiness.Value
.Cells(iRow1, 5).Value = Me.TxtProjectCode.Value
.Cells(iRow1, 14).Value = -Me.TxtJanuary.Value
.Cells(iRow1, 15).Value = -Me.TxtFebruary.Value
.Cells(iRow1, 16).Value = -Me.TxtMarch.Value
.Cells(iRow1, 17).Value = -Me.TxtApril.Value
.Cells(iRow1, 18).Value = -Me.TxtMay.Value
.Cells(iRow1, 19).Value = -Me.TxtJune.Value
.Cells(iRow1, 20).Value = -Me.TxtJuly.Value
.Cells(iRow1, 21).Value = -Me.TxtAugust.Value
.Cells(iRow1, 22).Value = -Me.TxtSeptember.Value
.Cells(iRow1, 23).Value = -Me.TxtOctober.Value
.Cells(iRow1, 24).Value = -Me.TxtNovember.Value
.Cells(iRow1, 25).Value = -Me.TxtDecember.Value
.Cells(iRow1, 26).Value = -Me.TxtTotalRe.Value

' Hide columns F to I ( Software )

Range("F:I").EntireColumn.Hidden = True


'clear the data
Me.CboBusiness.Value = ""
Me.TxtProjectname.Value = ""
Me.TxtClient.Value = ""
Me.TxtProjectCode.Value = ""
Me.TxtJanuary.Value = ""
Me.TxtFebruary.Value = ""
Me.TxtMarch.Value = ""
Me.TxtApril.Value = ""
Me.TxtMay.Value = ""
Me.TxtJune.Value = ""
Me.TxtJuly.Value = ""
Me.TxtAugust.Value = ""
Me.TxtSeptember.Value = ""
Me.TxtOctober.Value = ""
Me.TxtNovember.Value = ""
Me.TxtDecember.Value = ""
Me.TxtTotalRe.Value = ""


Me.TxtProjectCode.SetFocus
End With

' To finish code if Project Tab doesn't exist

Else
MsgBox "Project Code never enter before"

Exit Sub
End If

Worksheets("Projects").Select
End With

End Sub

Userform - validation data. Please help needed
 

Hi

It's a lot of code, but you don't tell us which line is causing the
error.

Also state the error message, other wise we don't know what to look
for.

---
Per

On 12 Dec., 19:10, Eduardo wrote:
Hi, this is my 2nd attempt . Thanks to Dave Peterson I was able to get a
userform running, the first form validates the project number (has to be
unique) and then enter the information in the summary and in the Project tab.
My second userform once the project # is entered validates that the project
exists. this form is to enter negative numbers since are completition stages.
All of the above acomplish already, however what I want to do now is to avoid
mistakes so I'd like to control looking at the summary sheet to match the
information entered in the 2nd userform related to Project#, Client, Project
name and business. As an example if in the first form I enter Project 1,
Client A, Project name A1, Business Apps, if when entering the information in
the 2nd form that fields doesn't match exactly as per above a message error
shows up.
Here is my code

Userform - validation data. Please help needed
 

Hi Per, and thank you for answering
There is no error in the code what happens lets say( I will include only two
fields ) in the first user form the user enter project number = 1 and client
= A, information is saved in the summary where all the projects are and in
the tab corresponding to that project number.
Then user has to assign the advance for that project and goes to the 2nd
userform, if the user enter project number = 2 the form will indicate that
the project was never enter before so he can go and enter 1 but if he makes a
mistake and enter as a client = B the form will save the information as B
what is wrong. So what I am looking for is once the information is entered in
this 2n form look at the data base matching for that project 3, the client,
project name and business and if one of them doesn't match tell the user that
for example for project 1, the client is incorrect. Hope this clarify
everything

"Per Jessen" wrote:

Hi

It's a lot of code, but you don't tell us which line is causing the
error.

Also state the error message, other wise we don't know what to look
for.

---
Per

On 12 Dec., 19:10, Eduardo wrote:
Hi, this is my 2nd attempt . Thanks to Dave Peterson I was able to get a
userform running, the first form validates the project number (has to be
unique) and then enter the information in the summary and in the Project tab.
My second userform once the project # is entered validates that the project
exists. this form is to enter negative numbers since are completition stages.
All of the above acomplish already, however what I want to do now is to avoid
mistakes so I'd like to control looking at the summary sheet to match the
information entered in the 2nd userform related to Project#, Client, Project
name and business. As an example if in the first form I enter Project 1,
Client A, Project name A1, Business Apps, if when entering the information in
the 2nd form that fields doesn't match exactly as per above a message error
shows up.
Here is my code