| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dave Peterson you help me with an userform could you help me again
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Dave Peterson you help me with an userform could you help me again
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 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 Eduardo wrote: 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 -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Dave Peterson you help me with an userform could you help me again
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 Dave Peterson wrote: 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 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 Eduardo wrote: 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 -- Dave Peterson -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Dave Peterson you help me with an userform could you help me a
MASTER, you are great !!!, thank you so much that was what I was looking for
"Dave Peterson" wrote: 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 Dave Peterson wrote: 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 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 Eduardo wrote: 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 -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Dave Peterson you help me with an userform could you help me a
Whew!
All that code scared me!!! Eduardo wrote: MASTER, you are great !!!, thank you so much that was what I was looking for "Dave Peterson" wrote: 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 Dave Peterson wrote: 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 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 Eduardo wrote: 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 -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Dave Peterson...Oh no, not her again... | Excel Discussion (Misc queries) | |||
| Dave Peterson | Excel Worksheet Functions | |||
| Dave Peterson | Excel Discussion (Misc queries) | |||
| Dave Peterson | Excel Discussion (Misc queries) | |||
| to Dave Peterson | Excel Discussion (Misc queries) | |||
Linear Mode
