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.Value, _
.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 said:
#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.
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
:
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.Value, _
.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
:
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.Value, _
.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
