|
userform duplicate control not working in my code. Thank you
Hi I have created a userform with the code as follow and I need to do two
things, first to check if the information entered in the user form already exist and secondly to have a calculation done in the user form ( they enter three different values and the 4th box give the total). I try to solve the first one as you will see in my code but it doesn't work. Thank you in Advance Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Projects") 'find first empty row in database iRow = ws.Cells(Rows.Count, 2) _ .End(xlUp).Offset(1, 0).Row 'check for a project number If Trim(Me.TxtProjectname.Value) = "" Then Me.TxtProjectname.SetFocus MsgBox "Please enter a project number" Exit Sub End If 'copy the data to the database ws.Cells(iRow, 3).Value = Me.TxtProjectname.Value ws.Cells(iRow, 2).Value = Me.TxtClient.Value ws.Cells(iRow, 8).Value = Me.TxtImplementation.Value ws.Cells(iRow, 9).Value = Me.TxtConsulting.Value ws.Cells(iRow, 10).Value = Me.TxtDevelopment.Value If IsNumeric(Application.Match(Me.TxtProjectname, [c:c], 0)) Then MsgBox "INFORMATION ALREADY IN DATA SHEET" End If 'clear the data Me.TxtProjectname.Value = "" Me.TxtClient.Value = "" Me.TxtImplementation.Value = "" Me.TxtConsulting.Value = "" Me.TxtDevelopment.Value = "" Me.TxtProjectname.SetFocus End Sub |
userform duplicate control not working in my code. Thank you
Hi Galimi,
Thank you for your response, I am new on this and just copy this code from Debra and make some adjustment as per post of the comunity and really I don't have any idea on how to apply your suggestion, could you help me. Thank you "galimi" wrote: Might now be the challenge, but you are not qualifying your objects, such as rows.count... should be, I presume ws.rows.count -- http://www.ExcelHelp.us 888-MY-ETHER ext. 01781474 "Eduardo" wrote: Hi I have created a userform with the code as follow and I need to do two things, first to check if the information entered in the user form already exist and secondly to have a calculation done in the user form ( they enter three different values and the 4th box give the total). I try to solve the first one as you will see in my code but it doesn't work. Thank you in Advance Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Projects") 'find first empty row in database iRow = ws.Cells(Rows.Count, 2) _ .End(xlUp).Offset(1, 0).Row 'check for a project number If Trim(Me.TxtProjectname.Value) = "" Then Me.TxtProjectname.SetFocus MsgBox "Please enter a project number" Exit Sub End If 'copy the data to the database ws.Cells(iRow, 3).Value = Me.TxtProjectname.Value ws.Cells(iRow, 2).Value = Me.TxtClient.Value ws.Cells(iRow, 8).Value = Me.TxtImplementation.Value ws.Cells(iRow, 9).Value = Me.TxtConsulting.Value ws.Cells(iRow, 10).Value = Me.TxtDevelopment.Value If IsNumeric(Application.Match(Me.TxtProjectname, [c:c], 0)) Then MsgBox "INFORMATION ALREADY IN DATA SHEET" End If 'clear the data Me.TxtProjectname.Value = "" Me.TxtClient.Value = "" Me.TxtImplementation.Value = "" Me.TxtConsulting.Value = "" Me.TxtDevelopment.Value = "" Me.TxtProjectname.SetFocus End Sub |
userform duplicate control not working in my code. Thank you
You didn't say what textboxes get the input and what the calculation is, but
maybe this will get you closer: Option Explicit Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Projects") 'find first empty row in database With ws iRow = .Cells(.Rows.Count, 2).End(xlUp).Offset(1, 0).Row 'check for a project number If Trim(Me.TxtProjectname.Value) = "" Then Me.TxtProjectname.SetFocus MsgBox "Please enter a project number" Exit Sub End If If IsNumeric(Me.TextBox6.Value) _ And IsNumeric(Me.TextBox7.Value) _ And IsNumeric(Me.TextBox8.Value) Then Me.TextBox9.Value = Me.TextBox6.Value _ + Me.TextBox7.Value + Me.TextBox8.Value Else MsgBox "Please enter numbers in those textboxes" Exit Sub End If If IsNumeric(Application.Match(Me.TxtProjectname.Valu e, _ .Range("C:C"), 0)) Then MsgBox "INFORMATION ALREADY IN DATA SHEET" Exit Sub End If 'copy the data to the database .Cells(iRow, 3).Value = Me.TxtProjectname.Value .Cells(iRow, 2).Value = Me.TxtClient.Value .Cells(iRow, 8).Value = Me.TxtImplementation.Value .Cells(iRow, 9).Value = Me.TxtConsulting.Value .Cells(iRow, 10).Value = Me.TxtDevelopment.Value .Cells(iRow, 20).Value = Me.TextBox6.Value .Cells(iRow, 30).Value = Me.TextBox7.Value .Cells(iRow, 40).Value = Me.TextBox8.Value .Cells(iRow, 50).Value = Me.TextBox9.Value 'clear the data Me.TxtProjectname.Value = "" Me.TxtClient.Value = "" Me.TxtImplementation.Value = "" Me.TxtConsulting.Value = "" Me.TxtDevelopment.Value = "" Me.TextBox6.Value = "" Me.TextBox7.Value = "" Me.TextBox8.Value = "" Me.TextBox9.Value = "" Me.TxtProjectname.SetFocus End With End Sub Eduardo wrote: Hi I have created a userform with the code as follow and I need to do two things, first to check if the information entered in the user form already exist and secondly to have a calculation done in the user form ( they enter three different values and the 4th box give the total). I try to solve the first one as you will see in my code but it doesn't work. Thank you in Advance Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Projects") 'find first empty row in database iRow = ws.Cells(Rows.Count, 2) _ .End(xlUp).Offset(1, 0).Row 'check for a project number If Trim(Me.TxtProjectname.Value) = "" Then Me.TxtProjectname.SetFocus MsgBox "Please enter a project number" Exit Sub End If 'copy the data to the database ws.Cells(iRow, 3).Value = Me.TxtProjectname.Value ws.Cells(iRow, 2).Value = Me.TxtClient.Value ws.Cells(iRow, 8).Value = Me.TxtImplementation.Value ws.Cells(iRow, 9).Value = Me.TxtConsulting.Value ws.Cells(iRow, 10).Value = Me.TxtDevelopment.Value If IsNumeric(Application.Match(Me.TxtProjectname, [c:c], 0)) Then MsgBox "INFORMATION ALREADY IN DATA SHEET" End If 'clear the data Me.TxtProjectname.Value = "" Me.TxtClient.Value = "" Me.TxtImplementation.Value = "" Me.TxtConsulting.Value = "" Me.TxtDevelopment.Value = "" Me.TxtProjectname.SetFocus End Sub -- Dave Peterson |
userform duplicate control not working in my code. Thank you
Hi Dave,
Thank you so much for answering, Sorry for not including the fields that are in the calculation but I was able to modify your code, however I have two problems once entered the information in TxtImplementation, TxtConsulting & TxtDevelopment it will not give me the total in the field called TxtGrossRevenue and the 2nd issue is that the project name might be names or number or a mix of both and I saw the code give me the message if I enter names. here is the code modified Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Projects") 'find first empty row in database With ws iRow = .Cells(.Rows.Count, 2).End(xlUp).Offset(1, 0).Row 'check for a project number If Trim(Me.TxtProjectname.Value) = "" Then Me.TxtProjectname.SetFocus MsgBox "Please enter a project number" Exit Sub End If If IsNumeric(Me.TxtImplementation.Value) _ And IsNumeric(Me.TxtConsulting.Value) _ And IsNumeric(Me.TxtDevelopment.Value) Then Me.TxtGrossRevenue.Value = Me.TxtImplementation.Value _ + Me.TxtConsulting.Value + Me.TxtDevelopment.Value Else MsgBox "Please enter numbers in those textboxes" Exit Sub End If If IsNumeric(Application.Match(Me.TxtProjectname.Valu e, _ .Range("C:C"), 0)) Then MsgBox "INFORMATION ALREADY IN DATA SHEET" Exit Sub End If 'copy the data to the database .Cells(iRow, 3).Value = Me.TxtProjectname.Value .Cells(iRow, 2).Value = Me.TxtClient.Value .Cells(iRow, 8).Value = Me.TxtImplementation.Value .Cells(iRow, 9).Value = Me.TxtConsulting.Value .Cells(iRow, 10).Value = Me.TxtDevelopment.Value 'clear the data Me.TxtProjectname.Value = "" Me.TxtClient.Value = "" Me.TxtImplementation.Value = "" Me.TxtConsulting.Value = "" Me.TxtDevelopment.Value = "" Me.TxtGrossRevenue.Value = "" Me.TxtProjectname.SetFocus End With End Sub "Dave Peterson" wrote: You didn't say what textboxes get the input and what the calculation is, but maybe this will get you closer: Option Explicit Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Projects") 'find first empty row in database With ws iRow = .Cells(.Rows.Count, 2).End(xlUp).Offset(1, 0).Row 'check for a project number If Trim(Me.TxtProjectname.Value) = "" Then Me.TxtProjectname.SetFocus MsgBox "Please enter a project number" Exit Sub End If If IsNumeric(Me.TextBox6.Value) _ And IsNumeric(Me.TextBox7.Value) _ And IsNumeric(Me.TextBox8.Value) Then Me.TextBox9.Value = Me.TextBox6.Value _ + Me.TextBox7.Value + Me.TextBox8.Value Else MsgBox "Please enter numbers in those textboxes" Exit Sub End If If IsNumeric(Application.Match(Me.TxtProjectname.Valu e, _ .Range("C:C"), 0)) Then MsgBox "INFORMATION ALREADY IN DATA SHEET" Exit Sub End If 'copy the data to the database .Cells(iRow, 3).Value = Me.TxtProjectname.Value .Cells(iRow, 2).Value = Me.TxtClient.Value .Cells(iRow, 8).Value = Me.TxtImplementation.Value .Cells(iRow, 9).Value = Me.TxtConsulting.Value .Cells(iRow, 10).Value = Me.TxtDevelopment.Value .Cells(iRow, 20).Value = Me.TextBox6.Value .Cells(iRow, 30).Value = Me.TextBox7.Value .Cells(iRow, 40).Value = Me.TextBox8.Value .Cells(iRow, 50).Value = Me.TextBox9.Value 'clear the data Me.TxtProjectname.Value = "" Me.TxtClient.Value = "" Me.TxtImplementation.Value = "" Me.TxtConsulting.Value = "" Me.TxtDevelopment.Value = "" Me.TextBox6.Value = "" Me.TextBox7.Value = "" Me.TextBox8.Value = "" Me.TextBox9.Value = "" Me.TxtProjectname.SetFocus End With End Sub Eduardo wrote: Hi I have created a userform with the code as follow and I need to do two things, first to check if the information entered in the user form already exist and secondly to have a calculation done in the user form ( they enter three different values and the 4th box give the total). I try to solve the first one as you will see in my code but it doesn't work. Thank you in Advance Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Projects") 'find first empty row in database iRow = ws.Cells(Rows.Count, 2) _ .End(xlUp).Offset(1, 0).Row 'check for a project number If Trim(Me.TxtProjectname.Value) = "" Then Me.TxtProjectname.SetFocus MsgBox "Please enter a project number" Exit Sub End If 'copy the data to the database ws.Cells(iRow, 3).Value = Me.TxtProjectname.Value ws.Cells(iRow, 2).Value = Me.TxtClient.Value ws.Cells(iRow, 8).Value = Me.TxtImplementation.Value ws.Cells(iRow, 9).Value = Me.TxtConsulting.Value ws.Cells(iRow, 10).Value = Me.TxtDevelopment.Value If IsNumeric(Application.Match(Me.TxtProjectname, [c:c], 0)) Then MsgBox "INFORMATION ALREADY IN DATA SHEET" End If 'clear the data Me.TxtProjectname.Value = "" Me.TxtClient.Value = "" Me.TxtImplementation.Value = "" Me.TxtConsulting.Value = "" Me.TxtDevelopment.Value = "" Me.TxtProjectname.SetFocus End Sub -- Dave Peterson |
userform duplicate control not working in my code. Thank you
What happens when you click the button?
Are you entering numeric values in those 3 textboxes? If you think so, then share those specific values. If the code says that there isn't a match when you enter a name, then there isn't a match. Maybe you have extra spaces in either the textbox or in the range? But if you're entering numbers, you'll want to do something like this (I saved it from a previous post so you'll have to modify for your code): 'textbox3.value is text--not a number 'so if column O can contain either text or numbers 'we can look twice. 'And if you only have integers, you can use 'clng() instead of cdbl(). varDN = Application.Match(TextBox3.Value, _ Worksheets("CE Tables").Range("O:O"), 0) If IsError(varDN) Then If IsNumeric(Me.TextBox3.Value) Then varDN = Application.Match(CDbl(TextBox3.Value), _ Worksheets("CE Tables").Range("O:O"), 0) End If End If If IsError(varDN) Then Me.Label1.Caption = "Non-Standard DN Value" Me.TextBox3.Value = "" Cancel = True End If Eduardo wrote: Hi Dave, Thank you so much for answering, Sorry for not including the fields that are in the calculation but I was able to modify your code, however I have two problems once entered the information in TxtImplementation, TxtConsulting & TxtDevelopment it will not give me the total in the field called TxtGrossRevenue and the 2nd issue is that the project name might be names or number or a mix of both and I saw the code give me the message if I enter names. here is the code modified Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Projects") 'find first empty row in database With ws iRow = .Cells(.Rows.Count, 2).End(xlUp).Offset(1, 0).Row 'check for a project number If Trim(Me.TxtProjectname.Value) = "" Then Me.TxtProjectname.SetFocus MsgBox "Please enter a project number" Exit Sub End If If IsNumeric(Me.TxtImplementation.Value) _ And IsNumeric(Me.TxtConsulting.Value) _ And IsNumeric(Me.TxtDevelopment.Value) Then Me.TxtGrossRevenue.Value = Me.TxtImplementation.Value _ + Me.TxtConsulting.Value + Me.TxtDevelopment.Value Else MsgBox "Please enter numbers in those textboxes" Exit Sub End If If IsNumeric(Application.Match(Me.TxtProjectname.Valu e, _ .Range("C:C"), 0)) Then MsgBox "INFORMATION ALREADY IN DATA SHEET" Exit Sub End If 'copy the data to the database .Cells(iRow, 3).Value = Me.TxtProjectname.Value .Cells(iRow, 2).Value = Me.TxtClient.Value .Cells(iRow, 8).Value = Me.TxtImplementation.Value .Cells(iRow, 9).Value = Me.TxtConsulting.Value .Cells(iRow, 10).Value = Me.TxtDevelopment.Value 'clear the data Me.TxtProjectname.Value = "" Me.TxtClient.Value = "" Me.TxtImplementation.Value = "" Me.TxtConsulting.Value = "" Me.TxtDevelopment.Value = "" Me.TxtGrossRevenue.Value = "" Me.TxtProjectname.SetFocus End With End Sub "Dave Peterson" wrote: You didn't say what textboxes get the input and what the calculation is, but maybe this will get you closer: Option Explicit Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Projects") 'find first empty row in database With ws iRow = .Cells(.Rows.Count, 2).End(xlUp).Offset(1, 0).Row 'check for a project number If Trim(Me.TxtProjectname.Value) = "" Then Me.TxtProjectname.SetFocus MsgBox "Please enter a project number" Exit Sub End If If IsNumeric(Me.TextBox6.Value) _ And IsNumeric(Me.TextBox7.Value) _ And IsNumeric(Me.TextBox8.Value) Then Me.TextBox9.Value = Me.TextBox6.Value _ + Me.TextBox7.Value + Me.TextBox8.Value Else MsgBox "Please enter numbers in those textboxes" Exit Sub End If If IsNumeric(Application.Match(Me.TxtProjectname.Valu e, _ .Range("C:C"), 0)) Then MsgBox "INFORMATION ALREADY IN DATA SHEET" Exit Sub End If 'copy the data to the database .Cells(iRow, 3).Value = Me.TxtProjectname.Value .Cells(iRow, 2).Value = Me.TxtClient.Value .Cells(iRow, 8).Value = Me.TxtImplementation.Value .Cells(iRow, 9).Value = Me.TxtConsulting.Value .Cells(iRow, 10).Value = Me.TxtDevelopment.Value .Cells(iRow, 20).Value = Me.TextBox6.Value .Cells(iRow, 30).Value = Me.TextBox7.Value .Cells(iRow, 40).Value = Me.TextBox8.Value .Cells(iRow, 50).Value = Me.TextBox9.Value 'clear the data Me.TxtProjectname.Value = "" Me.TxtClient.Value = "" Me.TxtImplementation.Value = "" Me.TxtConsulting.Value = "" Me.TxtDevelopment.Value = "" Me.TextBox6.Value = "" Me.TextBox7.Value = "" Me.TextBox8.Value = "" Me.TextBox9.Value = "" Me.TxtProjectname.SetFocus End With End Sub Eduardo wrote: Hi I have created a userform with the code as follow and I need to do two things, first to check if the information entered in the user form already exist and secondly to have a calculation done in the user form ( they enter three different values and the 4th box give the total). I try to solve the first one as you will see in my code but it doesn't work. Thank you in Advance Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Projects") 'find first empty row in database iRow = ws.Cells(Rows.Count, 2) _ .End(xlUp).Offset(1, 0).Row 'check for a project number If Trim(Me.TxtProjectname.Value) = "" Then Me.TxtProjectname.SetFocus MsgBox "Please enter a project number" Exit Sub End If 'copy the data to the database ws.Cells(iRow, 3).Value = Me.TxtProjectname.Value ws.Cells(iRow, 2).Value = Me.TxtClient.Value ws.Cells(iRow, 8).Value = Me.TxtImplementation.Value ws.Cells(iRow, 9).Value = Me.TxtConsulting.Value ws.Cells(iRow, 10).Value = Me.TxtDevelopment.Value If IsNumeric(Application.Match(Me.TxtProjectname, [c:c], 0)) Then MsgBox "INFORMATION ALREADY IN DATA SHEET" End If 'clear the data Me.TxtProjectname.Value = "" Me.TxtClient.Value = "" Me.TxtImplementation.Value = "" Me.TxtConsulting.Value = "" Me.TxtDevelopment.Value = "" Me.TxtProjectname.SetFocus End Sub -- Dave Peterson -- Dave Peterson |
userform duplicate control not working in my code. Thank you
Hi Dave,
I apologize for not been specific enough here are my problems 1) I enter number in the first three fields (TxtImplementation, TxtConsulting & TxtDevelopment), i.e. 100, 50, 20 what I am looking for is to have in the fourth field(TxtGrossRevenue) a total of 170 which will match the total invoice. At this moment once entered the three numbers mentioned above no calculation is populated in the fourth field 2) With reference to the control in the project name, if I enter lets say AA1, when entering again AA1 it tells me that the project exist which is correct, however if I enter as Project name 99 when entering again 99 it will allow it and send it to the data base In my real world it might be cases where the project name is a number (i.e. 99) or a name (Apolos) or a combination of both (Apolos 11) Hope this clarify everything "Dave Peterson" wrote: What happens when you click the button? Are you entering numeric values in those 3 textboxes? If you think so, then share those specific values. If the code says that there isn't a match when you enter a name, then there isn't a match. Maybe you have extra spaces in either the textbox or in the range? But if you're entering numbers, you'll want to do something like this (I saved it from a previous post so you'll have to modify for your code): 'textbox3.value is text--not a number 'so if column O can contain either text or numbers 'we can look twice. 'And if you only have integers, you can use 'clng() instead of cdbl(). varDN = Application.Match(TextBox3.Value, _ Worksheets("CE Tables").Range("O:O"), 0) If IsError(varDN) Then If IsNumeric(Me.TextBox3.Value) Then varDN = Application.Match(CDbl(TextBox3.Value), _ Worksheets("CE Tables").Range("O:O"), 0) End If End If If IsError(varDN) Then Me.Label1.Caption = "Non-Standard DN Value" Me.TextBox3.Value = "" Cancel = True End If Eduardo wrote: Hi Dave, Thank you so much for answering, Sorry for not including the fields that are in the calculation but I was able to modify your code, however I have two problems once entered the information in TxtImplementation, TxtConsulting & TxtDevelopment it will not give me the total in the field called TxtGrossRevenue and the 2nd issue is that the project name might be names or number or a mix of both and I saw the code give me the message if I enter names. here is the code modified Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Projects") 'find first empty row in database With ws iRow = .Cells(.Rows.Count, 2).End(xlUp).Offset(1, 0).Row 'check for a project number If Trim(Me.TxtProjectname.Value) = "" Then Me.TxtProjectname.SetFocus MsgBox "Please enter a project number" Exit Sub End If If IsNumeric(Me.TxtImplementation.Value) _ And IsNumeric(Me.TxtConsulting.Value) _ And IsNumeric(Me.TxtDevelopment.Value) Then Me.TxtGrossRevenue.Value = Me.TxtImplementation.Value _ + Me.TxtConsulting.Value + Me.TxtDevelopment.Value Else MsgBox "Please enter numbers in those textboxes" Exit Sub End If If IsNumeric(Application.Match(Me.TxtProjectname.Valu e, _ .Range("C:C"), 0)) Then MsgBox "INFORMATION ALREADY IN DATA SHEET" Exit Sub End If 'copy the data to the database .Cells(iRow, 3).Value = Me.TxtProjectname.Value .Cells(iRow, 2).Value = Me.TxtClient.Value .Cells(iRow, 8).Value = Me.TxtImplementation.Value .Cells(iRow, 9).Value = Me.TxtConsulting.Value .Cells(iRow, 10).Value = Me.TxtDevelopment.Value 'clear the data Me.TxtProjectname.Value = "" Me.TxtClient.Value = "" Me.TxtImplementation.Value = "" Me.TxtConsulting.Value = "" Me.TxtDevelopment.Value = "" Me.TxtGrossRevenue.Value = "" Me.TxtProjectname.SetFocus End With End Sub "Dave Peterson" wrote: You didn't say what textboxes get the input and what the calculation is, but maybe this will get you closer: Option Explicit Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Projects") 'find first empty row in database With ws iRow = .Cells(.Rows.Count, 2).End(xlUp).Offset(1, 0).Row 'check for a project number If Trim(Me.TxtProjectname.Value) = "" Then Me.TxtProjectname.SetFocus MsgBox "Please enter a project number" Exit Sub End If If IsNumeric(Me.TextBox6.Value) _ And IsNumeric(Me.TextBox7.Value) _ And IsNumeric(Me.TextBox8.Value) Then Me.TextBox9.Value = Me.TextBox6.Value _ + Me.TextBox7.Value + Me.TextBox8.Value Else MsgBox "Please enter numbers in those textboxes" Exit Sub End If If IsNumeric(Application.Match(Me.TxtProjectname.Valu e, _ .Range("C:C"), 0)) Then MsgBox "INFORMATION ALREADY IN DATA SHEET" Exit Sub End If 'copy the data to the database .Cells(iRow, 3).Value = Me.TxtProjectname.Value .Cells(iRow, 2).Value = Me.TxtClient.Value .Cells(iRow, 8).Value = Me.TxtImplementation.Value .Cells(iRow, 9).Value = Me.TxtConsulting.Value .Cells(iRow, 10).Value = Me.TxtDevelopment.Value .Cells(iRow, 20).Value = Me.TextBox6.Value .Cells(iRow, 30).Value = Me.TextBox7.Value .Cells(iRow, 40).Value = Me.TextBox8.Value .Cells(iRow, 50).Value = Me.TextBox9.Value 'clear the data Me.TxtProjectname.Value = "" Me.TxtClient.Value = "" Me.TxtImplementation.Value = "" Me.TxtConsulting.Value = "" Me.TxtDevelopment.Value = "" Me.TextBox6.Value = "" Me.TextBox7.Value = "" Me.TextBox8.Value = "" Me.TextBox9.Value = "" Me.TxtProjectname.SetFocus End With End Sub Eduardo wrote: Hi I have created a userform with the code as follow and I need to do two things, first to check if the information entered in the user form already exist and secondly to have a calculation done in the user form ( they enter three different values and the 4th box give the total). I try to solve the first one as you will see in my code but it doesn't work. Thank you in Advance Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Projects") 'find first empty row in database iRow = ws.Cells(Rows.Count, 2) _ .End(xlUp).Offset(1, 0).Row 'check for a project number If Trim(Me.TxtProjectname.Value) = "" Then Me.TxtProjectname.SetFocus MsgBox "Please enter a project number" Exit Sub End If 'copy the data to the database ws.Cells(iRow, 3).Value = Me.TxtProjectname.Value ws.Cells(iRow, 2).Value = Me.TxtClient.Value ws.Cells(iRow, 8).Value = Me.TxtImplementation.Value ws.Cells(iRow, 9).Value = Me.TxtConsulting.Value ws.Cells(iRow, 10).Value = Me.TxtDevelopment.Value If IsNumeric(Application.Match(Me.TxtProjectname, [c:c], 0)) Then MsgBox "INFORMATION ALREADY IN DATA SHEET" End If 'clear the data Me.TxtProjectname.Value = "" Me.TxtClient.Value = "" Me.TxtImplementation.Value = "" Me.TxtConsulting.Value = "" Me.TxtDevelopment.Value = "" Me.TxtProjectname.SetFocus End Sub -- Dave Peterson -- Dave Peterson |
userform duplicate control not working in my code. Thank you
#2 first. The values you type into a textbox is text--not a number.
There is a difference between 99 and "99" when you use Application.match(). One way around it is to check twice -- that was in my previous post. #1. I don't see why you're not getting a value in the 4th textbox with this code: If IsNumeric(Me.TxtImplementation.Value) _ And IsNumeric(Me.TxtConsulting.Value) _ And IsNumeric(Me.TxtDevelopment.Value) Then Me.TxtGrossRevenue.Value = Me.TxtImplementation.Value _ + Me.TxtConsulting.Value + Me.TxtDevelopment.Value Else MsgBox "Please enter numbers in those textboxes" Exit Sub End If Are you seeing that error message? If you are, maybe you should do some more checks: if isnumeric(Me.TxtImplementation.Value) = false then msgbox "Me.TxtImplementation.Value not numeric!" end if Same with the other two. Eduardo wrote: Hi Dave, I apologize for not been specific enough here are my problems 1) I enter number in the first three fields (TxtImplementation, TxtConsulting & TxtDevelopment), i.e. 100, 50, 20 what I am looking for is to have in the fourth field(TxtGrossRevenue) a total of 170 which will match the total invoice. At this moment once entered the three numbers mentioned above no calculation is populated in the fourth field 2) With reference to the control in the project name, if I enter lets say AA1, when entering again AA1 it tells me that the project exist which is correct, however if I enter as Project name 99 when entering again 99 it will allow it and send it to the data base In my real world it might be cases where the project name is a number (i.e. 99) or a name (Apolos) or a combination of both (Apolos 11) Hope this clarify everything "Dave Peterson" wrote: What happens when you click the button? Are you entering numeric values in those 3 textboxes? If you think so, then share those specific values. If the code says that there isn't a match when you enter a name, then there isn't a match. Maybe you have extra spaces in either the textbox or in the range? But if you're entering numbers, you'll want to do something like this (I saved it from a previous post so you'll have to modify for your code): 'textbox3.value is text--not a number 'so if column O can contain either text or numbers 'we can look twice. 'And if you only have integers, you can use 'clng() instead of cdbl(). varDN = Application.Match(TextBox3.Value, _ Worksheets("CE Tables").Range("O:O"), 0) If IsError(varDN) Then If IsNumeric(Me.TextBox3.Value) Then varDN = Application.Match(CDbl(TextBox3.Value), _ Worksheets("CE Tables").Range("O:O"), 0) End If End If If IsError(varDN) Then Me.Label1.Caption = "Non-Standard DN Value" Me.TextBox3.Value = "" Cancel = True End If Eduardo wrote: Hi Dave, Thank you so much for answering, Sorry for not including the fields that are in the calculation but I was able to modify your code, however I have two problems once entered the information in TxtImplementation, TxtConsulting & TxtDevelopment it will not give me the total in the field called TxtGrossRevenue and the 2nd issue is that the project name might be names or number or a mix of both and I saw the code give me the message if I enter names. here is the code modified Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Projects") 'find first empty row in database With ws iRow = .Cells(.Rows.Count, 2).End(xlUp).Offset(1, 0).Row 'check for a project number If Trim(Me.TxtProjectname.Value) = "" Then Me.TxtProjectname.SetFocus MsgBox "Please enter a project number" Exit Sub End If If IsNumeric(Me.TxtImplementation.Value) _ And IsNumeric(Me.TxtConsulting.Value) _ And IsNumeric(Me.TxtDevelopment.Value) Then Me.TxtGrossRevenue.Value = Me.TxtImplementation.Value _ + Me.TxtConsulting.Value + Me.TxtDevelopment.Value Else MsgBox "Please enter numbers in those textboxes" Exit Sub End If If IsNumeric(Application.Match(Me.TxtProjectname.Valu e, _ .Range("C:C"), 0)) Then MsgBox "INFORMATION ALREADY IN DATA SHEET" Exit Sub End If 'copy the data to the database .Cells(iRow, 3).Value = Me.TxtProjectname.Value .Cells(iRow, 2).Value = Me.TxtClient.Value .Cells(iRow, 8).Value = Me.TxtImplementation.Value .Cells(iRow, 9).Value = Me.TxtConsulting.Value .Cells(iRow, 10).Value = Me.TxtDevelopment.Value 'clear the data Me.TxtProjectname.Value = "" Me.TxtClient.Value = "" Me.TxtImplementation.Value = "" Me.TxtConsulting.Value = "" Me.TxtDevelopment.Value = "" Me.TxtGrossRevenue.Value = "" Me.TxtProjectname.SetFocus End With End Sub "Dave Peterson" wrote: You didn't say what textboxes get the input and what the calculation is, but maybe this will get you closer: Option Explicit Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Projects") 'find first empty row in database With ws iRow = .Cells(.Rows.Count, 2).End(xlUp).Offset(1, 0).Row 'check for a project number If Trim(Me.TxtProjectname.Value) = "" Then Me.TxtProjectname.SetFocus MsgBox "Please enter a project number" Exit Sub End If If IsNumeric(Me.TextBox6.Value) _ And IsNumeric(Me.TextBox7.Value) _ And IsNumeric(Me.TextBox8.Value) Then Me.TextBox9.Value = Me.TextBox6.Value _ + Me.TextBox7.Value + Me.TextBox8.Value Else MsgBox "Please enter numbers in those textboxes" Exit Sub End If If IsNumeric(Application.Match(Me.TxtProjectname.Valu e, _ .Range("C:C"), 0)) Then MsgBox "INFORMATION ALREADY IN DATA SHEET" Exit Sub End If 'copy the data to the database .Cells(iRow, 3).Value = Me.TxtProjectname.Value .Cells(iRow, 2).Value = Me.TxtClient.Value .Cells(iRow, 8).Value = Me.TxtImplementation.Value .Cells(iRow, 9).Value = Me.TxtConsulting.Value .Cells(iRow, 10).Value = Me.TxtDevelopment.Value .Cells(iRow, 20).Value = Me.TextBox6.Value .Cells(iRow, 30).Value = Me.TextBox7.Value .Cells(iRow, 40).Value = Me.TextBox8.Value .Cells(iRow, 50).Value = Me.TextBox9.Value 'clear the data Me.TxtProjectname.Value = "" Me.TxtClient.Value = "" Me.TxtImplementation.Value = "" Me.TxtConsulting.Value = "" Me.TxtDevelopment.Value = "" Me.TextBox6.Value = "" Me.TextBox7.Value = "" Me.TextBox8.Value = "" Me.TextBox9.Value = "" Me.TxtProjectname.SetFocus End With End Sub Eduardo wrote: Hi I have created a userform with the code as follow and I need to do two things, first to check if the information entered in the user form already exist and secondly to have a calculation done in the user form ( they enter three different values and the 4th box give the total). I try to solve the first one as you will see in my code but it doesn't work. Thank you in Advance Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Projects") 'find first empty row in database iRow = ws.Cells(Rows.Count, 2) _ .End(xlUp).Offset(1, 0).Row 'check for a project number If Trim(Me.TxtProjectname.Value) = "" Then Me.TxtProjectname.SetFocus MsgBox "Please enter a project number" Exit Sub End If 'copy the data to the database ws.Cells(iRow, 3).Value = Me.TxtProjectname.Value ws.Cells(iRow, 2).Value = Me.TxtClient.Value ws.Cells(iRow, 8).Value = Me.TxtImplementation.Value ws.Cells(iRow, 9).Value = Me.TxtConsulting.Value ws.Cells(iRow, 10).Value = Me.TxtDevelopment.Value If IsNumeric(Application.Match(Me.TxtProjectname, [c:c], 0)) Then MsgBox "INFORMATION ALREADY IN DATA SHEET" End If 'clear the data Me.TxtProjectname.Value = "" Me.TxtClient.Value = "" Me.TxtImplementation.Value = "" Me.TxtConsulting.Value = "" Me.TxtDevelopment.Value = "" Me.TxtProjectname.SetFocus End Sub -- Dave Peterson -- Dave Peterson -- Dave Peterson |
userform duplicate control not working in my code. Thank you
Hi Dave,
Sorry for comming back to you so late, yesterday was a terrible day with a dead line and today is another but as follow is what I got changing the code Still controlling if the project number start with a letter but not if it starts with a number I am attaching the hole code which include your code modified. I hope you can help thank you Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Projects") 'find first empty row in database With ws iRow = .Cells(.Rows.Count, 2).End(xlUp).Offset(1, 0).Row 'check for a project number If Trim(Me.TxtProjectname.Value) = "" Then Me.TxtProjectname.SetFocus MsgBox "Please enter a project number" Exit Sub End If 'textbox3.value is text--not a number 'so if column O can contain either text or numbers 'we can look twice. 'And if you only have integers, you can use 'clng() instead of cdbl(). varDN = Application.Match(TxtProjectname.Value, _ Worksheets("Projects").Range("c:c"), 0) If IsError(varDN) Then If IsNumeric(Me.TxtProjectname.Value) Then varDN = Application.Match(CDbl(TxtProjectname.Value), _ Worksheets("Projects").Range("c:c"), 0) End If End If If IsError(varDN) Then Me.Label1.Caption = "Non-Standard DN Value" Me.TxtProjectname.Value = "" Cancel = True End If If IsNumeric(Application.Match(Me.TxtProjectname.Valu e, _ .Range("C:C"), 0)) Then MsgBox "INFORMATION ALREADY IN DATA SHEET" Exit Sub End If ' Summarize 3 fields If IsNumeric(Me.TxtImplementation.Value) _ And IsNumeric(Me.TxtConsulting.Value) _ And IsNumeric(Me.TxtDevelopment.Value) Then Me.TxtGrossRevenue.Value = Me.TxtImplementation.Value _ + Me.TxtConsulting.Value + Me.TxtDevelopment.Value Else MsgBox "Please enter numbers in those textboxes" Exit Sub End If 'copy the data to the database .Cells(iRow, 3).Value = Me.TxtProjectname.Value .Cells(iRow, 2).Value = Me.TxtClient.Value .Cells(iRow, 8).Value = Me.TxtImplementation.Value .Cells(iRow, 9).Value = Me.TxtConsulting.Value .Cells(iRow, 10).Value = Me.TxtDevelopment.Value 'clear the data Me.TxtProjectname.Value = "" Me.TxtClient.Value = "" Me.TxtImplementation.Value = "" Me.TxtConsulting.Value = "" Me.TxtDevelopment.Value = "" Me.TxtGrossRevenue.Value = "" Me.TxtProjectname.SetFocus End With End Sub "Dave Peterson" wrote: #2 first. The values you type into a textbox is text--not a number. There is a difference between 99 and "99" when you use Application.match(). One way around it is to check twice -- that was in my previous post. #1. I don't see why you're not getting a value in the 4th textbox with this code: If IsNumeric(Me.TxtImplementation.Value) _ And IsNumeric(Me.TxtConsulting.Value) _ And IsNumeric(Me.TxtDevelopment.Value) Then Me.TxtGrossRevenue.Value = Me.TxtImplementation.Value _ + Me.TxtConsulting.Value + Me.TxtDevelopment.Value Else MsgBox "Please enter numbers in those textboxes" Exit Sub End If Are you seeing that error message? If you are, maybe you should do some more checks: if isnumeric(Me.TxtImplementation.Value) = false then msgbox "Me.TxtImplementation.Value not numeric!" end if Same with the other two. Eduardo wrote: Hi Dave, I apologize for not been specific enough here are my problems 1) I enter number in the first three fields (TxtImplementation, TxtConsulting & TxtDevelopment), i.e. 100, 50, 20 what I am looking for is to have in the fourth field(TxtGrossRevenue) a total of 170 which will match the total invoice. At this moment once entered the three numbers mentioned above no calculation is populated in the fourth field 2) With reference to the control in the project name, if I enter lets say AA1, when entering again AA1 it tells me that the project exist which is correct, however if I enter as Project name 99 when entering again 99 it will allow it and send it to the data base In my real world it might be cases where the project name is a number (i.e. 99) or a name (Apolos) or a combination of both (Apolos 11) Hope this clarify everything "Dave Peterson" wrote: What happens when you click the button? Are you entering numeric values in those 3 textboxes? If you think so, then share those specific values. If the code says that there isn't a match when you enter a name, then there isn't a match. Maybe you have extra spaces in either the textbox or in the range? But if you're entering numbers, you'll want to do something like this (I saved it from a previous post so you'll have to modify for your code): 'textbox3.value is text--not a number 'so if column O can contain either text or numbers 'we can look twice. 'And if you only have integers, you can use 'clng() instead of cdbl(). varDN = Application.Match(TextBox3.Value, _ Worksheets("CE Tables").Range("O:O"), 0) If IsError(varDN) Then If IsNumeric(Me.TextBox3.Value) Then varDN = Application.Match(CDbl(TextBox3.Value), _ Worksheets("CE Tables").Range("O:O"), 0) End If End If If IsError(varDN) Then Me.Label1.Caption = "Non-Standard DN Value" Me.TextBox3.Value = "" Cancel = True End If Eduardo wrote: Hi Dave, Thank you so much for answering, Sorry for not including the fields that are in the calculation but I was able to modify your code, however I have two problems once entered the information in TxtImplementation, TxtConsulting & TxtDevelopment it will not give me the total in the field called TxtGrossRevenue and the 2nd issue is that the project name might be names or number or a mix of both and I saw the code give me the message if I enter names. here is the code modified Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Projects") 'find first empty row in database With ws iRow = .Cells(.Rows.Count, 2).End(xlUp).Offset(1, 0).Row 'check for a project number If Trim(Me.TxtProjectname.Value) = "" Then Me.TxtProjectname.SetFocus MsgBox "Please enter a project number" Exit Sub End If If IsNumeric(Me.TxtImplementation.Value) _ And IsNumeric(Me.TxtConsulting.Value) _ And IsNumeric(Me.TxtDevelopment.Value) Then Me.TxtGrossRevenue.Value = Me.TxtImplementation.Value _ + Me.TxtConsulting.Value + Me.TxtDevelopment.Value Else MsgBox "Please enter numbers in those textboxes" Exit Sub End If If IsNumeric(Application.Match(Me.TxtProjectname.Valu e, _ .Range("C:C"), 0)) Then MsgBox "INFORMATION ALREADY IN DATA SHEET" Exit Sub End If 'copy the data to the database .Cells(iRow, 3).Value = Me.TxtProjectname.Value .Cells(iRow, 2).Value = Me.TxtClient.Value .Cells(iRow, 8).Value = Me.TxtImplementation.Value .Cells(iRow, 9).Value = Me.TxtConsulting.Value .Cells(iRow, 10).Value = Me.TxtDevelopment.Value 'clear the data Me.TxtProjectname.Value = "" Me.TxtClient.Value = "" Me.TxtImplementation.Value = "" Me.TxtConsulting.Value = "" Me.TxtDevelopment.Value = "" Me.TxtGrossRevenue.Value = "" Me.TxtProjectname.SetFocus End With End Sub "Dave Peterson" wrote: You didn't say what textboxes get the input and what the calculation is, but maybe this will get you closer: Option Explicit Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Projects") 'find first empty row in database With ws iRow = .Cells(.Rows.Count, 2).End(xlUp).Offset(1, 0).Row 'check for a project number If Trim(Me.TxtProjectname.Value) = "" Then Me.TxtProjectname.SetFocus MsgBox "Please enter a project number" Exit Sub End If If IsNumeric(Me.TextBox6.Value) _ And IsNumeric(Me.TextBox7.Value) _ And IsNumeric(Me.TextBox8.Value) Then Me.TextBox9.Value = Me.TextBox6.Value _ + Me.TextBox7.Value + Me.TextBox8.Value Else MsgBox "Please enter numbers in those textboxes" Exit Sub End If If IsNumeric(Application.Match(Me.TxtProjectname.Valu e, _ .Range("C:C"), 0)) Then MsgBox "INFORMATION ALREADY IN DATA SHEET" Exit Sub End If 'copy the data to the database .Cells(iRow, 3).Value = Me.TxtProjectname.Value .Cells(iRow, 2).Value = Me.TxtClient.Value .Cells(iRow, 8).Value = Me.TxtImplementation.Value .Cells(iRow, 9).Value = Me.TxtConsulting.Value .Cells(iRow, 10).Value = Me.TxtDevelopment.Value .Cells(iRow, 20).Value = Me.TextBox6.Value .Cells(iRow, 30).Value = Me.TextBox7.Value .Cells(iRow, 40).Value = Me.TextBox8.Value .Cells(iRow, 50).Value = Me.TextBox9.Value 'clear the data Me.TxtProjectname.Value = "" Me.TxtClient.Value = "" Me.TxtImplementation.Value = "" Me.TxtConsulting.Value = "" Me.TxtDevelopment.Value = "" Me.TextBox6.Value = "" Me.TextBox7.Value = "" Me.TextBox8.Value = "" Me.TextBox9.Value = "" Me.TxtProjectname.SetFocus End With End Sub Eduardo wrote: Hi I have created a userform with the code as follow and I need to do two things, first to check if the information entered in the user form already exist and secondly to have a calculation done in the user form ( they enter three different values and the 4th box give the total). I try to solve the first one as you will see in my code but it doesn't work. Thank you in Advance Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Projects") 'find first empty row in database iRow = ws.Cells(Rows.Count, 2) _ .End(xlUp).Offset(1, 0).Row 'check for a project number If Trim(Me.TxtProjectname.Value) = "" Then Me.TxtProjectname.SetFocus MsgBox "Please enter a project number" Exit Sub End If 'copy the data to the database ws.Cells(iRow, 3).Value = Me.TxtProjectname.Value ws.Cells(iRow, 2).Value = Me.TxtClient.Value ws.Cells(iRow, 8).Value = Me.TxtImplementation.Value ws.Cells(iRow, 9).Value = Me.TxtConsulting.Value ws.Cells(iRow, 10).Value = Me.TxtDevelopment.Value If IsNumeric(Application.Match(Me.TxtProjectname, [c:c], 0)) Then MsgBox "INFORMATION ALREADY IN DATA SHEET" End If 'clear the data Me.TxtProjectname.Value = "" Me.TxtClient.Value = "" Me.TxtImplementation.Value = "" Me.TxtConsulting.Value = "" Me.TxtDevelopment.Value = "" Me.TxtProjectname.SetFocus End Sub -- Dave Peterson -- Dave Peterson -- Dave Peterson |
userform duplicate control not working in my code. Thank you
Untested.
Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet dim res as variant Set ws = Worksheets("Projects") 'find first empty row in database With ws iRow = .Cells(.Rows.Count, 2).End(xlUp).Offset(1, 0).Row 'check for a project number If Trim(Me.TxtProjectname.Value) = "" Then Me.TxtProjectname.SetFocus MsgBox "Please enter a project number" Exit Sub End If res = Application.Match(me.TxtProjectname.Value, _ Worksheets("Projects").Range("c:c"), 0) If IsError(res) Then If IsNumeric(Me.TxtProjectname.Value) Then res = Application.Match(CDbl(me.TxtProjectname.Value), _ Worksheets("Projects").Range("c:c"), 0) End If End If If Isnumber(res) Then msgbox "already exists" Me.TxtProjectname.Value = "" exit sub end if ....rest of your code Eduardo wrote: Hi Dave, Sorry for comming back to you so late, yesterday was a terrible day with a dead line and today is another but as follow is what I got changing the code Still controlling if the project number start with a letter but not if it starts with a number I am attaching the hole code which include your code modified. I hope you can help thank you Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Projects") 'find first empty row in database With ws iRow = .Cells(.Rows.Count, 2).End(xlUp).Offset(1, 0).Row 'check for a project number If Trim(Me.TxtProjectname.Value) = "" Then Me.TxtProjectname.SetFocus MsgBox "Please enter a project number" Exit Sub End If 'textbox3.value is text--not a number 'so if column O can contain either text or numbers 'we can look twice. 'And if you only have integers, you can use 'clng() instead of cdbl(). varDN = Application.Match(TxtProjectname.Value, _ Worksheets("Projects").Range("c:c"), 0) If IsError(varDN) Then If IsNumeric(Me.TxtProjectname.Value) Then varDN = Application.Match(CDbl(TxtProjectname.Value), _ Worksheets("Projects").Range("c:c"), 0) End If End If If IsError(varDN) Then Me.Label1.Caption = "Non-Standard DN Value" Me.TxtProjectname.Value = "" Cancel = True End If If IsNumeric(Application.Match(Me.TxtProjectname.Valu e, _ .Range("C:C"), 0)) Then MsgBox "INFORMATION ALREADY IN DATA SHEET" Exit Sub End If ' Summarize 3 fields If IsNumeric(Me.TxtImplementation.Value) _ And IsNumeric(Me.TxtConsulting.Value) _ And IsNumeric(Me.TxtDevelopment.Value) Then Me.TxtGrossRevenue.Value = Me.TxtImplementation.Value _ + Me.TxtConsulting.Value + Me.TxtDevelopment.Value Else MsgBox "Please enter numbers in those textboxes" Exit Sub End If 'copy the data to the database .Cells(iRow, 3).Value = Me.TxtProjectname.Value .Cells(iRow, 2).Value = Me.TxtClient.Value .Cells(iRow, 8).Value = Me.TxtImplementation.Value .Cells(iRow, 9).Value = Me.TxtConsulting.Value .Cells(iRow, 10).Value = Me.TxtDevelopment.Value 'clear the data Me.TxtProjectname.Value = "" Me.TxtClient.Value = "" Me.TxtImplementation.Value = "" Me.TxtConsulting.Value = "" Me.TxtDevelopment.Value = "" Me.TxtGrossRevenue.Value = "" Me.TxtProjectname.SetFocus End With End Sub "Dave Peterson" wrote: #2 first. The values you type into a textbox is text--not a number. There is a difference between 99 and "99" when you use Application.match(). One way around it is to check twice -- that was in my previous post. #1. I don't see why you're not getting a value in the 4th textbox with this code: If IsNumeric(Me.TxtImplementation.Value) _ And IsNumeric(Me.TxtConsulting.Value) _ And IsNumeric(Me.TxtDevelopment.Value) Then Me.TxtGrossRevenue.Value = Me.TxtImplementation.Value _ + Me.TxtConsulting.Value + Me.TxtDevelopment.Value Else MsgBox "Please enter numbers in those textboxes" Exit Sub End If Are you seeing that error message? If you are, maybe you should do some more checks: if isnumeric(Me.TxtImplementation.Value) = false then msgbox "Me.TxtImplementation.Value not numeric!" end if Same with the other two. Eduardo wrote: Hi Dave, I apologize for not been specific enough here are my problems 1) I enter number in the first three fields (TxtImplementation, TxtConsulting & TxtDevelopment), i.e. 100, 50, 20 what I am looking for is to have in the fourth field(TxtGrossRevenue) a total of 170 which will match the total invoice. At this moment once entered the three numbers mentioned above no calculation is populated in the fourth field 2) With reference to the control in the project name, if I enter lets say AA1, when entering again AA1 it tells me that the project exist which is correct, however if I enter as Project name 99 when entering again 99 it will allow it and send it to the data base In my real world it might be cases where the project name is a number (i.e. 99) or a name (Apolos) or a combination of both (Apolos 11) Hope this clarify everything "Dave Peterson" wrote: What happens when you click the button? Are you entering numeric values in those 3 textboxes? If you think so, then share those specific values. If the code says that there isn't a match when you enter a name, then there isn't a match. Maybe you have extra spaces in either the textbox or in the range? But if you're entering numbers, you'll want to do something like this (I saved it from a previous post so you'll have to modify for your code): 'textbox3.value is text--not a number 'so if column O can contain either text or numbers 'we can look twice. 'And if you only have integers, you can use 'clng() instead of cdbl(). varDN = Application.Match(TextBox3.Value, _ Worksheets("CE Tables").Range("O:O"), 0) If IsError(varDN) Then If IsNumeric(Me.TextBox3.Value) Then varDN = Application.Match(CDbl(TextBox3.Value), _ Worksheets("CE Tables").Range("O:O"), 0) End If End If If IsError(varDN) Then Me.Label1.Caption = "Non-Standard DN Value" Me.TextBox3.Value = "" Cancel = True End If Eduardo wrote: Hi Dave, Thank you so much for answering, Sorry for not including the fields that are in the calculation but I was able to modify your code, however I have two problems once entered the information in TxtImplementation, TxtConsulting & TxtDevelopment it will not give me the total in the field called TxtGrossRevenue and the 2nd issue is that the project name might be names or number or a mix of both and I saw the code give me the message if I enter names. here is the code modified Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Projects") 'find first empty row in database With ws iRow = .Cells(.Rows.Count, 2).End(xlUp).Offset(1, 0).Row 'check for a project number If Trim(Me.TxtProjectname.Value) = "" Then Me.TxtProjectname.SetFocus MsgBox "Please enter a project number" Exit Sub End If If IsNumeric(Me.TxtImplementation.Value) _ And IsNumeric(Me.TxtConsulting.Value) _ And IsNumeric(Me.TxtDevelopment.Value) Then Me.TxtGrossRevenue.Value = Me.TxtImplementation.Value _ + Me.TxtConsulting.Value + Me.TxtDevelopment.Value Else MsgBox "Please enter numbers in those textboxes" Exit Sub End If If IsNumeric(Application.Match(Me.TxtProjectname.Valu e, _ .Range("C:C"), 0)) Then MsgBox "INFORMATION ALREADY IN DATA SHEET" Exit Sub End If 'copy the data to the database .Cells(iRow, 3).Value = Me.TxtProjectname.Value .Cells(iRow, 2).Value = Me.TxtClient.Value .Cells(iRow, 8).Value = Me.TxtImplementation.Value .Cells(iRow, 9).Value = Me.TxtConsulting.Value .Cells(iRow, 10).Value = Me.TxtDevelopment.Value 'clear the data Me.TxtProjectname.Value = "" Me.TxtClient.Value = "" Me.TxtImplementation.Value = "" Me.TxtConsulting.Value = "" Me.TxtDevelopment.Value = "" Me.TxtGrossRevenue.Value = "" Me.TxtProjectname.SetFocus End With End Sub "Dave Peterson" wrote: You didn't say what textboxes get the input and what the calculation is, but maybe this will get you closer: Option Explicit Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Projects") 'find first empty row in database With ws iRow = .Cells(.Rows.Count, 2).End(xlUp).Offset(1, 0).Row 'check for a project number If Trim(Me.TxtProjectname.Value) = "" Then Me.TxtProjectname.SetFocus MsgBox "Please enter a project number" Exit Sub End If If IsNumeric(Me.TextBox6.Value) _ And IsNumeric(Me.TextBox7.Value) _ And IsNumeric(Me.TextBox8.Value) Then Me.TextBox9.Value = Me.TextBox6.Value _ + Me.TextBox7.Value + Me.TextBox8.Value Else MsgBox "Please enter numbers in those textboxes" Exit Sub End If If IsNumeric(Application.Match(Me.TxtProjectname.Valu e, _ .Range("C:C"), 0)) Then MsgBox "INFORMATION ALREADY IN DATA SHEET" Exit Sub End If 'copy the data to the database .Cells(iRow, 3).Value = Me.TxtProjectname.Value .Cells(iRow, 2).Value = Me.TxtClient.Value .Cells(iRow, 8).Value = Me.TxtImplementation.Value .Cells(iRow, 9).Value = Me.TxtConsulting.Value .Cells(iRow, 10).Value = Me.TxtDevelopment.Value .Cells(iRow, 20).Value = Me.TextBox6.Value .Cells(iRow, 30).Value = Me.TextBox7.Value .Cells(iRow, 40).Value = Me.TextBox8.Value .Cells(iRow, 50).Value = Me.TextBox9.Value 'clear the data Me.TxtProjectname.Value = "" Me.TxtClient.Value = "" Me.TxtImplementation.Value = "" Me.TxtConsulting.Value = "" Me.TxtDevelopment.Value = "" Me.TextBox6.Value = "" Me.TextBox7.Value = "" Me.TextBox8.Value = "" Me.TextBox9.Value = "" Me.TxtProjectname.SetFocus End With End Sub Eduardo wrote: Hi I have created a userform with the code as follow and I need to do two things, first to check if the information entered in the user form already exist and secondly to have a calculation done in the user form ( they enter three different values and the 4th box give the total). I try to solve the first one as you will see in my code but it doesn't work. Thank you in Advance Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Projects") 'find first empty row in database iRow = ws.Cells(Rows.Count, 2) _ .End(xlUp).Offset(1, 0).Row 'check for a project number If Trim(Me.TxtProjectname.Value) = "" Then Me.TxtProjectname.SetFocus MsgBox "Please enter a project number" Exit Sub End If 'copy the data to the database ws.Cells(iRow, 3).Value = Me.TxtProjectname.Value ws.Cells(iRow, 2).Value = Me.TxtClient.Value ws.Cells(iRow, 8).Value = Me.TxtImplementation.Value ws.Cells(iRow, 9).Value = Me.TxtConsulting.Value ws.Cells(iRow, 10).Value = Me.TxtDevelopment.Value If IsNumeric(Application.Match(Me.TxtProjectname, [c:c], 0)) Then MsgBox "INFORMATION ALREADY IN DATA SHEET" End If 'clear the data Me.TxtProjectname.Value = "" Me.TxtClient.Value = "" Me.TxtImplementation.Value = "" Me.TxtConsulting.Value = "" Me.TxtDevelopment.Value = "" Me.TxtProjectname.SetFocus End Sub -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
userform duplicate control not working in my code. Thank you
Hi Dave,
You are awesome, that works perfectly controlling the project#. As I notice you keep track of your answers, in the untested code it was giving me an error but I figure it out, in the last statement instead of If Isnumber (res) should be If Isnumeric Still having problems with the field with the calculation, if I enter in the different fields 10,10,10 the total field should be showing 30 now nothing show up until I press the button, the information goes to the sheet and in this fields show up 10+10+10 The idea is that once they enter the amounts in the others fields the total show up immediately and they can control that total with the invoice. If what I am pretending cannot be done or is too much work for you I will delete the field Is there any good book you can recomend me to get more familiar with this kind of VBA. Thank you so much again "Dave Peterson" wrote: Untested. Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet dim res as variant Set ws = Worksheets("Projects") 'find first empty row in database With ws iRow = .Cells(.Rows.Count, 2).End(xlUp).Offset(1, 0).Row 'check for a project number If Trim(Me.TxtProjectname.Value) = "" Then Me.TxtProjectname.SetFocus MsgBox "Please enter a project number" Exit Sub End If res = Application.Match(me.TxtProjectname.Value, _ Worksheets("Projects").Range("c:c"), 0) If IsError(res) Then If IsNumeric(Me.TxtProjectname.Value) Then res = Application.Match(CDbl(me.TxtProjectname.Value), _ Worksheets("Projects").Range("c:c"), 0) End If End If If Isnumber(res) Then msgbox "already exists" Me.TxtProjectname.Value = "" exit sub end if ....rest of your code Eduardo wrote: Hi Dave, Sorry for comming back to you so late, yesterday was a terrible day with a dead line and today is another but as follow is what I got changing the code Still controlling if the project number start with a letter but not if it starts with a number I am attaching the hole code which include your code modified. I hope you can help thank you Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Projects") 'find first empty row in database With ws iRow = .Cells(.Rows.Count, 2).End(xlUp).Offset(1, 0).Row 'check for a project number If Trim(Me.TxtProjectname.Value) = "" Then Me.TxtProjectname.SetFocus MsgBox "Please enter a project number" Exit Sub End If 'textbox3.value is text--not a number 'so if column O can contain either text or numbers 'we can look twice. 'And if you only have integers, you can use 'clng() instead of cdbl(). varDN = Application.Match(TxtProjectname.Value, _ Worksheets("Projects").Range("c:c"), 0) If IsError(varDN) Then If IsNumeric(Me.TxtProjectname.Value) Then varDN = Application.Match(CDbl(TxtProjectname.Value), _ Worksheets("Projects").Range("c:c"), 0) End If End If If IsError(varDN) Then Me.Label1.Caption = "Non-Standard DN Value" Me.TxtProjectname.Value = "" Cancel = True End If If IsNumeric(Application.Match(Me.TxtProjectname.Valu e, _ .Range("C:C"), 0)) Then MsgBox "INFORMATION ALREADY IN DATA SHEET" Exit Sub End If ' Summarize 3 fields If IsNumeric(Me.TxtImplementation.Value) _ And IsNumeric(Me.TxtConsulting.Value) _ And IsNumeric(Me.TxtDevelopment.Value) Then Me.TxtGrossRevenue.Value = Me.TxtImplementation.Value _ + Me.TxtConsulting.Value + Me.TxtDevelopment.Value Else MsgBox "Please enter numbers in those textboxes" Exit Sub End If 'copy the data to the database .Cells(iRow, 3).Value = Me.TxtProjectname.Value .Cells(iRow, 2).Value = Me.TxtClient.Value .Cells(iRow, 8).Value = Me.TxtImplementation.Value .Cells(iRow, 9).Value = Me.TxtConsulting.Value .Cells(iRow, 10).Value = Me.TxtDevelopment.Value 'clear the data Me.TxtProjectname.Value = "" Me.TxtClient.Value = "" Me.TxtImplementation.Value = "" Me.TxtConsulting.Value = "" Me.TxtDevelopment.Value = "" Me.TxtGrossRevenue.Value = "" Me.TxtProjectname.SetFocus End With End Sub "Dave Peterson" wrote: #2 first. The values you type into a textbox is text--not a number. There is a difference between 99 and "99" when you use Application.match(). One way around it is to check twice -- that was in my previous post. #1. I don't see why you're not getting a value in the 4th textbox with this code: If IsNumeric(Me.TxtImplementation.Value) _ And IsNumeric(Me.TxtConsulting.Value) _ And IsNumeric(Me.TxtDevelopment.Value) Then Me.TxtGrossRevenue.Value = Me.TxtImplementation.Value _ + Me.TxtConsulting.Value + Me.TxtDevelopment.Value Else MsgBox "Please enter numbers in those textboxes" Exit Sub End If Are you seeing that error message? If you are, maybe you should do some more checks: if isnumeric(Me.TxtImplementation.Value) = false then msgbox "Me.TxtImplementation.Value not numeric!" end if Same with the other two. Eduardo wrote: Hi Dave, I apologize for not been specific enough here are my problems 1) I enter number in the first three fields (TxtImplementation, TxtConsulting & TxtDevelopment), i.e. 100, 50, 20 what I am looking for is to have in the fourth field(TxtGrossRevenue) a total of 170 which will match the total invoice. At this moment once entered the three numbers mentioned above no calculation is populated in the fourth field 2) With reference to the control in the project name, if I enter lets say AA1, when entering again AA1 it tells me that the project exist which is correct, however if I enter as Project name 99 when entering again 99 it will allow it and send it to the data base In my real world it might be cases where the project name is a number (i.e. 99) or a name (Apolos) or a combination of both (Apolos 11) Hope this clarify everything "Dave Peterson" wrote: What happens when you click the button? Are you entering numeric values in those 3 textboxes? If you think so, then share those specific values. If the code says that there isn't a match when you enter a name, then there isn't a match. Maybe you have extra spaces in either the textbox or in the range? But if you're entering numbers, you'll want to do something like this (I saved it from a previous post so you'll have to modify for your code): 'textbox3.value is text--not a number 'so if column O can contain either text or numbers 'we can look twice. 'And if you only have integers, you can use 'clng() instead of cdbl(). varDN = Application.Match(TextBox3.Value, _ Worksheets("CE Tables").Range("O:O"), 0) If IsError(varDN) Then If IsNumeric(Me.TextBox3.Value) Then varDN = Application.Match(CDbl(TextBox3.Value), _ Worksheets("CE Tables").Range("O:O"), 0) End If End If If IsError(varDN) Then Me.Label1.Caption = "Non-Standard DN Value" Me.TextBox3.Value = "" Cancel = True End If Eduardo wrote: Hi Dave, Thank you so much for answering, Sorry for not including the fields that are in the calculation but I was able to modify your code, however I have two problems once entered the information in TxtImplementation, TxtConsulting & TxtDevelopment it will not give me the total in the field called TxtGrossRevenue and the 2nd issue is that the project name might be names or number or a mix of both and I saw the code give me the message if I enter names. here is the code modified Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Projects") 'find first empty row in database With ws iRow = .Cells(.Rows.Count, 2).End(xlUp).Offset(1, 0).Row 'check for a project number If Trim(Me.TxtProjectname.Value) = "" Then Me.TxtProjectname.SetFocus MsgBox "Please enter a project number" Exit Sub End If If IsNumeric(Me.TxtImplementation.Value) _ And IsNumeric(Me.TxtConsulting.Value) _ And IsNumeric(Me.TxtDevelopment.Value) Then Me.TxtGrossRevenue.Value = Me.TxtImplementation.Value _ + Me.TxtConsulting.Value + Me.TxtDevelopment.Value Else MsgBox "Please enter numbers in those textboxes" Exit Sub End If If IsNumeric(Application.Match(Me.TxtProjectname.Valu e, _ .Range("C:C"), 0)) Then MsgBox "INFORMATION ALREADY IN DATA SHEET" Exit Sub End If 'copy the data to the database .Cells(iRow, 3).Value = Me.TxtProjectname.Value .Cells(iRow, 2).Value = Me.TxtClient.Value .Cells(iRow, 8).Value = Me.TxtImplementation.Value .Cells(iRow, 9).Value = Me.TxtConsulting.Value .Cells(iRow, 10).Value = Me.TxtDevelopment.Value 'clear the data Me.TxtProjectname.Value = "" Me.TxtClient.Value = "" Me.TxtImplementation.Value = "" Me.TxtConsulting.Value = "" Me.TxtDevelopment.Value = "" Me.TxtGrossRevenue.Value = "" Me.TxtProjectname.SetFocus End With End Sub "Dave Peterson" wrote: You didn't say what textboxes get the input and what the calculation is, but maybe this will get you closer: Option Explicit Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Projects") 'find first empty row in database With ws iRow = .Cells(.Rows.Count, 2).End(xlUp).Offset(1, 0).Row 'check for a project number If Trim(Me.TxtProjectname.Value) = "" Then Me.TxtProjectname.SetFocus MsgBox "Please enter a project number" Exit Sub End If If IsNumeric(Me.TextBox6.Value) _ |
userform duplicate control not working in my code. Thank you
Glad you caught the typo. Sometimes my fingers think that I'm in a worksheet
and want to use =isnumber(a1)... And that grossrevenue textbox is being updated. But it's too fast for you to see. You update the textbox, write to the worksheet, and then clear the contents of all those textboxes. Your eyes just aren't quick enough to catch it in the userform. But there is a bug in the code. I didn't test it and I thought that by adding something that looked like numbers, excel would do real arithmetic--not just string concatenation. Use this: If IsNumeric(Me.TxtImplementation.Value) _ And IsNumeric(Me.TxtConsulting.Value) _ And IsNumeric(Me.TxtDevelopment.Value) Then Me.TxtGrossRevenue.Value = cdbl(Me.TxtImplementation.Value) _ + cdbl(Me.TxtConsulting.Value) _ + cdbl(Me.TxtDevelopment.Value) else ..... cdbl will convert those string numbers to number numbers. Eduardo wrote: Hi Dave, You are awesome, that works perfectly controlling the project#. As I notice you keep track of your answers, in the untested code it was giving me an error but I figure it out, in the last statement instead of If Isnumber (res) should be If Isnumeric Still having problems with the field with the calculation, if I enter in the different fields 10,10,10 the total field should be showing 30 now nothing show up until I press the button, the information goes to the sheet and in this fields show up 10+10+10 The idea is that once they enter the amounts in the others fields the total show up immediately and they can control that total with the invoice. If what I am pretending cannot be done or is too much work for you I will delete the field Is there any good book you can recomend me to get more familiar with this kind of VBA. Thank you so much again "Dave Peterson" wrote: Untested. Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet dim res as variant Set ws = Worksheets("Projects") 'find first empty row in database With ws iRow = .Cells(.Rows.Count, 2).End(xlUp).Offset(1, 0).Row 'check for a project number If Trim(Me.TxtProjectname.Value) = "" Then Me.TxtProjectname.SetFocus MsgBox "Please enter a project number" Exit Sub End If res = Application.Match(me.TxtProjectname.Value, _ Worksheets("Projects").Range("c:c"), 0) If IsError(res) Then If IsNumeric(Me.TxtProjectname.Value) Then res = Application.Match(CDbl(me.TxtProjectname.Value), _ Worksheets("Projects").Range("c:c"), 0) End If End If If Isnumber(res) Then msgbox "already exists" Me.TxtProjectname.Value = "" exit sub end if ....rest of your code Eduardo wrote: Hi Dave, Sorry for comming back to you so late, yesterday was a terrible day with a dead line and today is another but as follow is what I got changing the code Still controlling if the project number start with a letter but not if it starts with a number I am attaching the hole code which include your code modified. I hope you can help thank you Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Projects") 'find first empty row in database With ws iRow = .Cells(.Rows.Count, 2).End(xlUp).Offset(1, 0).Row 'check for a project number If Trim(Me.TxtProjectname.Value) = "" Then Me.TxtProjectname.SetFocus MsgBox "Please enter a project number" Exit Sub End If 'textbox3.value is text--not a number 'so if column O can contain either text or numbers 'we can look twice. 'And if you only have integers, you can use 'clng() instead of cdbl(). varDN = Application.Match(TxtProjectname.Value, _ Worksheets("Projects").Range("c:c"), 0) If IsError(varDN) Then If IsNumeric(Me.TxtProjectname.Value) Then varDN = Application.Match(CDbl(TxtProjectname.Value), _ Worksheets("Projects").Range("c:c"), 0) End If End If If IsError(varDN) Then Me.Label1.Caption = "Non-Standard DN Value" Me.TxtProjectname.Value = "" Cancel = True End If If IsNumeric(Application.Match(Me.TxtProjectname.Valu e, _ .Range("C:C"), 0)) Then MsgBox "INFORMATION ALREADY IN DATA SHEET" Exit Sub End If ' Summarize 3 fields If IsNumeric(Me.TxtImplementation.Value) _ And IsNumeric(Me.TxtConsulting.Value) _ And IsNumeric(Me.TxtDevelopment.Value) Then Me.TxtGrossRevenue.Value = Me.TxtImplementation.Value _ + Me.TxtConsulting.Value + Me.TxtDevelopment.Value Else MsgBox "Please enter numbers in those textboxes" Exit Sub End If 'copy the data to the database .Cells(iRow, 3).Value = Me.TxtProjectname.Value .Cells(iRow, 2).Value = Me.TxtClient.Value .Cells(iRow, 8).Value = Me.TxtImplementation.Value .Cells(iRow, 9).Value = Me.TxtConsulting.Value .Cells(iRow, 10).Value = Me.TxtDevelopment.Value 'clear the data Me.TxtProjectname.Value = "" Me.TxtClient.Value = "" Me.TxtImplementation.Value = "" Me.TxtConsulting.Value = "" Me.TxtDevelopment.Value = "" Me.TxtGrossRevenue.Value = "" Me.TxtProjectname.SetFocus End With End Sub "Dave Peterson" wrote: #2 first. The values you type into a textbox is text--not a number. There is a difference between 99 and "99" when you use Application.match(). One way around it is to check twice -- that was in my previous post. #1. I don't see why you're not getting a value in the 4th textbox with this code: If IsNumeric(Me.TxtImplementation.Value) _ And IsNumeric(Me.TxtConsulting.Value) _ And IsNumeric(Me.TxtDevelopment.Value) Then Me.TxtGrossRevenue.Value = Me.TxtImplementation.Value _ + Me.TxtConsulting.Value + Me.TxtDevelopment.Value Else MsgBox "Please enter numbers in those textboxes" Exit Sub End If Are you seeing that error message? If you are, maybe you should do some more checks: if isnumeric(Me.TxtImplementation.Value) = false then msgbox "Me.TxtImplementation.Value not numeric!" end if Same with the other two. Eduardo wrote: Hi Dave, I apologize for not been specific enough here are my problems 1) I enter number in the first three fields (TxtImplementation, TxtConsulting & TxtDevelopment), i.e. 100, 50, 20 what I am looking for is to have in the fourth field(TxtGrossRevenue) a total of 170 which will match the total invoice. At this moment once entered the three numbers mentioned above no calculation is populated in the fourth field 2) With reference to the control in the project name, if I enter lets say AA1, when entering again AA1 it tells me that the project exist which is correct, however if I enter as Project name 99 when entering again 99 it will allow it and send it to the data base In my real world it might be cases where the project name is a number (i.e. 99) or a name (Apolos) or a combination of both (Apolos 11) Hope this clarify everything "Dave Peterson" wrote: What happens when you click the button? Are you entering numeric values in those 3 textboxes? If you think so, then share those specific values. If the code says that there isn't a match when you enter a name, then there isn't a match. Maybe you have extra spaces in either the textbox or in the range? But if you're entering numbers, you'll want to do something like this (I saved it from a previous post so you'll have to modify for your code): 'textbox3.value is text--not a number 'so if column O can contain either text or numbers 'we can look twice. 'And if you only have integers, you can use 'clng() instead of cdbl(). varDN = Application.Match(TextBox3.Value, _ Worksheets("CE Tables").Range("O:O"), 0) If IsError(varDN) Then If IsNumeric(Me.TextBox3.Value) Then varDN = Application.Match(CDbl(TextBox3.Value), _ Worksheets("CE Tables").Range("O:O"), 0) End If End If If IsError(varDN) Then Me.Label1.Caption = "Non-Standard DN Value" Me.TextBox3.Value = "" Cancel = True End If Eduardo wrote: Hi Dave, Thank you so much for answering, Sorry for not including the fields that are in the calculation but I was able to modify your code, however I have two problems once entered the information in TxtImplementation, TxtConsulting & TxtDevelopment it will not give me the total in the field called TxtGrossRevenue and the 2nd issue is that the project name might be names or number or a mix of both and I saw the code give me the message if I enter names. here is the code modified Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Projects") 'find first empty row in database With ws iRow = .Cells(.Rows.Count, 2).End(xlUp).Offset(1, 0).Row 'check for a project number If Trim(Me.TxtProjectname.Value) = "" Then Me.TxtProjectname.SetFocus MsgBox "Please enter a project number" Exit Sub End If If IsNumeric(Me.TxtImplementation.Value) _ And IsNumeric(Me.TxtConsulting.Value) _ And IsNumeric(Me.TxtDevelopment.Value) Then Me.TxtGrossRevenue.Value = Me.TxtImplementation.Value _ + Me.TxtConsulting.Value + Me.TxtDevelopment.Value Else MsgBox "Please enter numbers in those textboxes" Exit Sub End If If IsNumeric(Application.Match(Me.TxtProjectname.Valu e, _ .Range("C:C"), 0)) Then MsgBox "INFORMATION ALREADY IN DATA SHEET" Exit Sub End If 'copy the data to the database .Cells(iRow, 3).Value = Me.TxtProjectname.Value .Cells(iRow, 2).Value = Me.TxtClient.Value .Cells(iRow, 8).Value = Me.TxtImplementation.Value .Cells(iRow, 9).Value = Me.TxtConsulting.Value .Cells(iRow, 10).Value = Me.TxtDevelopment.Value 'clear the data Me.TxtProjectname.Value = "" Me.TxtClient.Value = "" Me.TxtImplementation.Value = "" Me.TxtConsulting.Value = "" Me.TxtDevelopment.Value = "" Me.TxtGrossRevenue.Value = "" Me.TxtProjectname.SetFocus End With End Sub "Dave Peterson" wrote: You didn't say what textboxes get the input and what the calculation is, but maybe this will get you closer: Option Explicit Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Projects") 'find first empty row in database With ws iRow = .Cells(.Rows.Count, 2).End(xlUp).Offset(1, 0).Row 'check for a project number If Trim(Me.TxtProjectname.Value) = "" Then Me.TxtProjectname.SetFocus MsgBox "Please enter a project number" Exit Sub End If If IsNumeric(Me.TextBox6.Value) _ -- Dave Peterson |
userform duplicate control not working in my code. Thank you
Hi Dave,
That was perfect, thank you so much, if you have any VBA book that you can recommend me please let me know Thank you again and have a Great Weekend "Dave Peterson" wrote: Glad you caught the typo. Sometimes my fingers think that I'm in a worksheet and want to use =isnumber(a1)... And that grossrevenue textbox is being updated. But it's too fast for you to see. You update the textbox, write to the worksheet, and then clear the contents of all those textboxes. Your eyes just aren't quick enough to catch it in the userform. But there is a bug in the code. I didn't test it and I thought that by adding something that looked like numbers, excel would do real arithmetic--not just string concatenation. Use this: If IsNumeric(Me.TxtImplementation.Value) _ And IsNumeric(Me.TxtConsulting.Value) _ And IsNumeric(Me.TxtDevelopment.Value) Then Me.TxtGrossRevenue.Value = cdbl(Me.TxtImplementation.Value) _ + cdbl(Me.TxtConsulting.Value) _ + cdbl(Me.TxtDevelopment.Value) else ..... cdbl will convert those string numbers to number numbers. Eduardo wrote: Hi Dave, You are awesome, that works perfectly controlling the project#. As I notice you keep track of your answers, in the untested code it was giving me an error but I figure it out, in the last statement instead of If Isnumber (res) should be If Isnumeric Still having problems with the field with the calculation, if I enter in the different fields 10,10,10 the total field should be showing 30 now nothing show up until I press the button, the information goes to the sheet and in this fields show up 10+10+10 The idea is that once they enter the amounts in the others fields the total show up immediately and they can control that total with the invoice. If what I am pretending cannot be done or is too much work for you I will delete the field Is there any good book you can recomend me to get more familiar with this kind of VBA. Thank you so much again "Dave Peterson" wrote: Untested. Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet dim res as variant Set ws = Worksheets("Projects") 'find first empty row in database With ws iRow = .Cells(.Rows.Count, 2).End(xlUp).Offset(1, 0).Row 'check for a project number If Trim(Me.TxtProjectname.Value) = "" Then Me.TxtProjectname.SetFocus MsgBox "Please enter a project number" Exit Sub End If res = Application.Match(me.TxtProjectname.Value, _ Worksheets("Projects").Range("c:c"), 0) If IsError(res) Then If IsNumeric(Me.TxtProjectname.Value) Then res = Application.Match(CDbl(me.TxtProjectname.Value), _ Worksheets("Projects").Range("c:c"), 0) End If End If If Isnumber(res) Then msgbox "already exists" Me.TxtProjectname.Value = "" exit sub end if ....rest of your code Eduardo wrote: Hi Dave, Sorry for comming back to you so late, yesterday was a terrible day with a dead line and today is another but as follow is what I got changing the code Still controlling if the project number start with a letter but not if it starts with a number I am attaching the hole code which include your code modified. I hope you can help thank you Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Projects") 'find first empty row in database With ws iRow = .Cells(.Rows.Count, 2).End(xlUp).Offset(1, 0).Row 'check for a project number If Trim(Me.TxtProjectname.Value) = "" Then Me.TxtProjectname.SetFocus MsgBox "Please enter a project number" Exit Sub End If 'textbox3.value is text--not a number 'so if column O can contain either text or numbers 'we can look twice. 'And if you only have integers, you can use 'clng() instead of cdbl(). varDN = Application.Match(TxtProjectname.Value, _ Worksheets("Projects").Range("c:c"), 0) If IsError(varDN) Then If IsNumeric(Me.TxtProjectname.Value) Then varDN = Application.Match(CDbl(TxtProjectname.Value), _ Worksheets("Projects").Range("c:c"), 0) End If End If If IsError(varDN) Then Me.Label1.Caption = "Non-Standard DN Value" Me.TxtProjectname.Value = "" Cancel = True End If If IsNumeric(Application.Match(Me.TxtProjectname.Valu e, _ .Range("C:C"), 0)) Then MsgBox "INFORMATION ALREADY IN DATA SHEET" Exit Sub End If ' Summarize 3 fields If IsNumeric(Me.TxtImplementation.Value) _ And IsNumeric(Me.TxtConsulting.Value) _ And IsNumeric(Me.TxtDevelopment.Value) Then Me.TxtGrossRevenue.Value = Me.TxtImplementation.Value _ + Me.TxtConsulting.Value + Me.TxtDevelopment.Value Else MsgBox "Please enter numbers in those textboxes" Exit Sub End If 'copy the data to the database .Cells(iRow, 3).Value = Me.TxtProjectname.Value .Cells(iRow, 2).Value = Me.TxtClient.Value .Cells(iRow, 8).Value = Me.TxtImplementation.Value .Cells(iRow, 9).Value = Me.TxtConsulting.Value .Cells(iRow, 10).Value = Me.TxtDevelopment.Value 'clear the data Me.TxtProjectname.Value = "" Me.TxtClient.Value = "" Me.TxtImplementation.Value = "" Me.TxtConsulting.Value = "" Me.TxtDevelopment.Value = "" Me.TxtGrossRevenue.Value = "" Me.TxtProjectname.SetFocus End With End Sub "Dave Peterson" wrote: #2 first. The values you type into a textbox is text--not a number. There is a difference between 99 and "99" when you use Application.match(). One way around it is to check twice -- that was in my previous post. #1. I don't see why you're not getting a value in the 4th textbox with this code: If IsNumeric(Me.TxtImplementation.Value) _ And IsNumeric(Me.TxtConsulting.Value) _ And IsNumeric(Me.TxtDevelopment.Value) Then Me.TxtGrossRevenue.Value = Me.TxtImplementation.Value _ + Me.TxtConsulting.Value + Me.TxtDevelopment.Value Else MsgBox "Please enter numbers in those textboxes" Exit Sub End If Are you seeing that error message? If you are, maybe you should do some more checks: if isnumeric(Me.TxtImplementation.Value) = false then msgbox "Me.TxtImplementation.Value not numeric!" end if Same with the other two. Eduardo wrote: Hi Dave, I apologize for not been specific enough here are my problems 1) I enter number in the first three fields (TxtImplementation, TxtConsulting & TxtDevelopment), i.e. 100, 50, 20 what I am looking for is to have in the fourth field(TxtGrossRevenue) a total of 170 which will match the total invoice. At this moment once entered the three numbers mentioned above no calculation is populated in the fourth field 2) With reference to the control in the project name, if I enter lets say AA1, when entering again AA1 it tells me that the project exist which is correct, however if I enter as Project name 99 when entering again 99 it will allow it and send it to the data base In my real world it might be cases where the project name is a number (i.e. 99) or a name (Apolos) or a combination of both (Apolos 11) Hope this clarify everything "Dave Peterson" wrote: What happens when you click the button? Are you entering numeric values in those 3 textboxes? If you think so, then share those specific values. If the code says that there isn't a match when you enter a name, then there isn't a match. Maybe you have extra spaces in either the textbox or in the range? But if you're entering numbers, you'll want to do something like this (I saved it from a previous post so you'll have to modify for your code): 'textbox3.value is text--not a number 'so if column O can contain either text or numbers 'we can look twice. 'And if you only have integers, you can use 'clng() instead of cdbl(). varDN = Application.Match(TextBox3.Value, _ Worksheets("CE Tables").Range("O:O"), 0) If IsError(varDN) Then If IsNumeric(Me.TextBox3.Value) Then varDN = Application.Match(CDbl(TextBox3.Value), _ Worksheets("CE Tables").Range("O:O"), 0) End If End If If IsError(varDN) Then Me.Label1.Caption = "Non-Standard DN Value" Me.TextBox3.Value = "" Cancel = True End If Eduardo wrote: Hi Dave, Thank you so much for answering, Sorry for not including the fields that are in the calculation but I was able to modify your code, however I have two problems once entered the information in TxtImplementation, TxtConsulting & TxtDevelopment it will not give me the total in the field called TxtGrossRevenue and the 2nd issue is that the project name might be names or number or a mix of both and I saw the code give me the message if I enter names. here is the code modified Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Projects") 'find first empty row in database With ws iRow = .Cells(.Rows.Count, 2).End(xlUp).Offset(1, 0).Row 'check for a project number If Trim(Me.TxtProjectname.Value) = "" Then Me.TxtProjectname.SetFocus MsgBox "Please enter a project number" Exit Sub End If If IsNumeric(Me.TxtImplementation.Value) _ And IsNumeric(Me.TxtConsulting.Value) _ And IsNumeric(Me.TxtDevelopment.Value) Then Me.TxtGrossRevenue.Value = Me.TxtImplementation.Value _ + Me.TxtConsulting.Value + Me.TxtDevelopment.Value Else MsgBox "Please enter numbers in those textboxes" Exit Sub End If If IsNumeric(Application.Match(Me.TxtProjectname.Valu e, _ .Range("C:C"), 0)) Then MsgBox "INFORMATION ALREADY IN DATA SHEET" Exit Sub End If |
userform duplicate control not working in my code. Thank you
Debra Dalgleish has a list of books at her site:
http://www.contextures.com/xlbooks.html John Walkenbach's books are very good to start. See if you can find them in your local bookstore/internet site and you can choose what one you like best. Eduardo wrote: Hi Dave, That was perfect, thank you so much, if you have any VBA book that you can recommend me please let me know Thank you again and have a Great Weekend "Dave Peterson" wrote: Glad you caught the typo. Sometimes my fingers think that I'm in a worksheet and want to use =isnumber(a1)... And that grossrevenue textbox is being updated. But it's too fast for you to see. You update the textbox, write to the worksheet, and then clear the contents of all those textboxes. Your eyes just aren't quick enough to catch it in the userform. But there is a bug in the code. I didn't test it and I thought that by adding something that looked like numbers, excel would do real arithmetic--not just string concatenation. Use this: If IsNumeric(Me.TxtImplementation.Value) _ And IsNumeric(Me.TxtConsulting.Value) _ And IsNumeric(Me.TxtDevelopment.Value) Then Me.TxtGrossRevenue.Value = cdbl(Me.TxtImplementation.Value) _ + cdbl(Me.TxtConsulting.Value) _ + cdbl(Me.TxtDevelopment.Value) else ..... cdbl will convert those string numbers to number numbers. Eduardo wrote: Hi Dave, You are awesome, that works perfectly controlling the project#. As I notice you keep track of your answers, in the untested code it was giving me an error but I figure it out, in the last statement instead of If Isnumber (res) should be If Isnumeric Still having problems with the field with the calculation, if I enter in the different fields 10,10,10 the total field should be showing 30 now nothing show up until I press the button, the information goes to the sheet and in this fields show up 10+10+10 The idea is that once they enter the amounts in the others fields the total show up immediately and they can control that total with the invoice. If what I am pretending cannot be done or is too much work for you I will delete the field Is there any good book you can recomend me to get more familiar with this kind of VBA. Thank you so much again "Dave Peterson" wrote: Untested. Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet dim res as variant Set ws = Worksheets("Projects") 'find first empty row in database With ws iRow = .Cells(.Rows.Count, 2).End(xlUp).Offset(1, 0).Row 'check for a project number If Trim(Me.TxtProjectname.Value) = "" Then Me.TxtProjectname.SetFocus MsgBox "Please enter a project number" Exit Sub End If res = Application.Match(me.TxtProjectname.Value, _ Worksheets("Projects").Range("c:c"), 0) If IsError(res) Then If IsNumeric(Me.TxtProjectname.Value) Then res = Application.Match(CDbl(me.TxtProjectname.Value), _ Worksheets("Projects").Range("c:c"), 0) End If End If If Isnumber(res) Then msgbox "already exists" Me.TxtProjectname.Value = "" exit sub end if ....rest of your code Eduardo wrote: Hi Dave, Sorry for comming back to you so late, yesterday was a terrible day with a dead line and today is another but as follow is what I got changing the code Still controlling if the project number start with a letter but not if it starts with a number I am attaching the hole code which include your code modified. I hope you can help thank you Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Projects") 'find first empty row in database With ws iRow = .Cells(.Rows.Count, 2).End(xlUp).Offset(1, 0).Row 'check for a project number If Trim(Me.TxtProjectname.Value) = "" Then Me.TxtProjectname.SetFocus MsgBox "Please enter a project number" Exit Sub End If 'textbox3.value is text--not a number 'so if column O can contain either text or numbers 'we can look twice. 'And if you only have integers, you can use 'clng() instead of cdbl(). varDN = Application.Match(TxtProjectname.Value, _ Worksheets("Projects").Range("c:c"), 0) If IsError(varDN) Then If IsNumeric(Me.TxtProjectname.Value) Then varDN = Application.Match(CDbl(TxtProjectname.Value), _ Worksheets("Projects").Range("c:c"), 0) End If End If If IsError(varDN) Then Me.Label1.Caption = "Non-Standard DN Value" Me.TxtProjectname.Value = "" Cancel = True End If If IsNumeric(Application.Match(Me.TxtProjectname.Valu e, _ .Range("C:C"), 0)) Then MsgBox "INFORMATION ALREADY IN DATA SHEET" Exit Sub End If ' Summarize 3 fields If IsNumeric(Me.TxtImplementation.Value) _ And IsNumeric(Me.TxtConsulting.Value) _ And IsNumeric(Me.TxtDevelopment.Value) Then Me.TxtGrossRevenue.Value = Me.TxtImplementation.Value _ + Me.TxtConsulting.Value + Me.TxtDevelopment.Value Else MsgBox "Please enter numbers in those textboxes" Exit Sub End If 'copy the data to the database .Cells(iRow, 3).Value = Me.TxtProjectname.Value .Cells(iRow, 2).Value = Me.TxtClient.Value .Cells(iRow, 8).Value = Me.TxtImplementation.Value .Cells(iRow, 9).Value = Me.TxtConsulting.Value .Cells(iRow, 10).Value = Me.TxtDevelopment.Value 'clear the data Me.TxtProjectname.Value = "" Me.TxtClient.Value = "" Me.TxtImplementation.Value = "" Me.TxtConsulting.Value = "" Me.TxtDevelopment.Value = "" Me.TxtGrossRevenue.Value = "" Me.TxtProjectname.SetFocus End With End Sub "Dave Peterson" wrote: #2 first. The values you type into a textbox is text--not a number. There is a difference between 99 and "99" when you use Application.match(). One way around it is to check twice -- that was in my previous post. #1. I don't see why you're not getting a value in the 4th textbox with this code: If IsNumeric(Me.TxtImplementation.Value) _ And IsNumeric(Me.TxtConsulting.Value) _ And IsNumeric(Me.TxtDevelopment.Value) Then Me.TxtGrossRevenue.Value = Me.TxtImplementation.Value _ + Me.TxtConsulting.Value + Me.TxtDevelopment.Value Else MsgBox "Please enter numbers in those textboxes" Exit Sub End If Are you seeing that error message? If you are, maybe you should do some more checks: if isnumeric(Me.TxtImplementation.Value) = false then msgbox "Me.TxtImplementation.Value not numeric!" end if Same with the other two. Eduardo wrote: Hi Dave, I apologize for not been specific enough here are my problems 1) I enter number in the first three fields (TxtImplementation, TxtConsulting & TxtDevelopment), i.e. 100, 50, 20 what I am looking for is to have in the fourth field(TxtGrossRevenue) a total of 170 which will match the total invoice. At this moment once entered the three numbers mentioned above no calculation is populated in the fourth field 2) With reference to the control in the project name, if I enter lets say AA1, when entering again AA1 it tells me that the project exist which is correct, however if I enter as Project name 99 when entering again 99 it will allow it and send it to the data base In my real world it might be cases where the project name is a number (i.e. 99) or a name (Apolos) or a combination of both (Apolos 11) Hope this clarify everything "Dave Peterson" wrote: What happens when you click the button? Are you entering numeric values in those 3 textboxes? If you think so, then share those specific values. If the code says that there isn't a match when you enter a name, then there isn't a match. Maybe you have extra spaces in either the textbox or in the range? But if you're entering numbers, you'll want to do something like this (I saved it from a previous post so you'll have to modify for your code): 'textbox3.value is text--not a number 'so if column O can contain either text or numbers 'we can look twice. 'And if you only have integers, you can use 'clng() instead of cdbl(). varDN = Application.Match(TextBox3.Value, _ Worksheets("CE Tables").Range("O:O"), 0) If IsError(varDN) Then If IsNumeric(Me.TextBox3.Value) Then varDN = Application.Match(CDbl(TextBox3.Value), _ Worksheets("CE Tables").Range("O:O"), 0) End If End If If IsError(varDN) Then Me.Label1.Caption = "Non-Standard DN Value" Me.TextBox3.Value = "" Cancel = True End If Eduardo wrote: Hi Dave, Thank you so much for answering, Sorry for not including the fields that are in the calculation but I was able to modify your code, however I have two problems once entered the information in TxtImplementation, TxtConsulting & TxtDevelopment it will not give me the total in the field called TxtGrossRevenue and the 2nd issue is that the project name might be names or number or a mix of both and I saw the code give me the message if I enter names. here is the code modified Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Projects") 'find first empty row in database With ws iRow = .Cells(.Rows.Count, 2).End(xlUp).Offset(1, 0).Row 'check for a project number If Trim(Me.TxtProjectname.Value) = "" Then Me.TxtProjectname.SetFocus MsgBox "Please enter a project number" Exit Sub End If If IsNumeric(Me.TxtImplementation.Value) _ And IsNumeric(Me.TxtConsulting.Value) _ And IsNumeric(Me.TxtDevelopment.Value) Then Me.TxtGrossRevenue.Value = Me.TxtImplementation.Value _ + Me.TxtConsulting.Value + Me.TxtDevelopment.Value Else MsgBox "Please enter numbers in those textboxes" Exit Sub End If If IsNumeric(Application.Match(Me.TxtProjectname.Valu e, _ .Range("C:C"), 0)) Then MsgBox "INFORMATION ALREADY IN DATA SHEET" Exit Sub End If -- Dave Peterson |
| All times are GMT +1. The time now is 10:33 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com