| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Refer to control on userform from code module | Excel Programming | |||
| working hotkey even if any control has focus in userform | Excel Programming | |||
| Code not working when transfered into Userform | Excel Programming | |||
| Using code to create a control on an Excel userform | Excel Programming | |||
| Control code behavior with userform ? | Excel Programming | |||
Hybrid Mode
