Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform enter a field checking other two in database
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Userform enter a field checking other two in database
Sorry this is a duplication of my previous post, mi computer kick me off and
told me that the message was not post "Eduardo" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Database for checking out books | Excel Discussion (Misc queries) | |||
Enter info in one sheet, auto enter in another based on one field | New Users to Excel | |||
Userform to enter values and shown in same userform in list | Excel Programming | |||
Userform - Move to next textbox field by hitting enter instead of | Excel Programming | |||
Checking database field | Excel Programming |