Userform enter a field checking other two in database

E

Eduardo

Hi, and thank you in advance for any help
With your guidance I finally almost finished a userform called New Projects
where Client ID and Project #. Form validate Project # and create a new tab
for each Project saving the information in this tab and in a Summary tab
(which contains all the projects). Then in the same spreadsheet I have
created another userform to enter as negative, information related to the
project #( this form correspond to the % advanced in the project). I got the
code in this case to check if the tab exist and then save the information in
the summary tab and in the Project tab. Now my problem
My user form has the fields as follow
Client
Project Name
Project Code
Business

What I am trying to do is once the project # is entered and the code
validate that exist in the data base is to get the information related to the
Client, Project Name and Business populated automatically to the other
fields, if that is not possible at least to validate that the client, project
name and business correspond to the project # entered, otherwise get a
message with indication of the problem. Here is my code I know that is not
perfect but I am not an expert. thank you again. I hope this make sense

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
 
E

Eduardo

Sorry this is a duplication of my previous post, mi computer kick me off and
told me that the message was not post
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Userform enter a field checking other two fields - Thank you 2
Userform - validation data. Please help needed 2
Dave Peterson you help me with an userform could you help me again 4
userform Field forcing Capital Letters - Thank you 3
userform duplicate control not working in my code. Thank you 13

Top