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