Dave Peterson you help me with an userform could you help me again

E

Eduardo

Thanks to you I have working an userform to enter data and validate that the
project # is unique. Now I modify that userform and create a 2nd one (Project
application) which check that the project # exists and then allow the user to
fill out the information, the information will go to the spreadsheets as
negative. What I am trying to do is once the user enter the Project # if it
exists will populate automatically in the other 3 text box the information
corresponding to Client, Project name and Business. Other option will be once
the 4 boxes are filled out to compare the information and if there is any
discrepancy with the database it will pop-up as an error. As follow is my
code the code I am trying to make it work is under "check information entered
match what is in Database"
Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Dim res As Variant
Dim iRow1 As Long
Dim ws1 As Worksheet
Dim Res1 As Variant

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 information entered match what is in Database

Res1 = Application.Match(Me.TxtProjectCode.Value =
Worksheets("Projects").Range("e:e") * Me.TxtClient.Value =
Worksheets("Projects").Range("c:c") * Me.CboBusiness.Value =
Worksheets("Projects").Range("b:b") * Me.TxtProjectname.Value =
Worksheets("Projects").Range("d:d"), 0)
If IsError(Res1) Then
If IsNumeric(Me.TxtProjectCode.Value * Me.TxtClient.Value *
Me.CboBusiness.Value * Me.TxtProjectname.Value) Then
Res1 = Application.Match(CDbl(Me.TxtProjectCode.Value =
Worksheets("Projects").Range("e:e")) * CDbl(Me.TxtClient.Value =
Worksheets("Projects").Range("c:c")) * CDbl(Me.CboBusiness.Value =
Worksheets("Projects").Range("b:b")) * CDbl(Me.TxtProjectname.Value =
Worksheets("Projects").Range("d:d")), 0)
End If
End If

If IsNumeric(Res1) = "0" Then





'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

' To finish code comparing information with Database
Else
MsgBox " Information doesn't belong to project #"

Exit Sub
End If

Worksheets("Projects").Select
End With

End Sub
 
D

Dave Peterson

I didn't take the time to set up a workbook and look through all that code, but
this portion looks to see if there's a match:
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
Click to expand...

If there is a match then res will equal the row that contains the match (in
projects!e:e).

So you could use something like:

with worksheets("projects")
if .cells(res,"F").value = sometextbox.value _
and .cells(res,"X").value = someothertextbox.value _
and .cells(res,"IV").value = Evenathirdtextbox.value then
'all the same
else
msgbox "something's different"
end if
end with
 
D

Dave Peterson

ps. you'd have to check to make sure res was a number, too:

if isnumeric(res) then
with worksheets("projects")
if .cells(res,"F").value = sometextbox.value _
and .cells(res,"X").value = someothertextbox.value _
and .cells(res,"IV").value = Evenathirdtextbox.value then
'all the same
else
msgbox "something's different"
end if
end with
end if
 
E

Eduardo

MASTER, you are great !!!, thank you so much that was what I was looking for

Dave Peterson said:
ps. you'd have to check to make sure res was a number, too:

if isnumeric(res) then
with worksheets("projects")
if .cells(res,"F").value = sometextbox.value _
and .cells(res,"X").value = someothertextbox.value _
and .cells(res,"IV").value = Evenathirdtextbox.value then
'all the same
else
msgbox "something's different"
end if
end with
end if
Click to expand...
 

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
Userform enter a field checking other two in database 1
userform Field forcing Capital Letters - Thank you 3
userform duplicate control not working in my code. Thank you 13

Top