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
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