• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Validation Help

H

HanSam

Member
Hi guys

I need some help on writing the validation for the attached file. Here are what I want to do but has no success doing so.

1) Format txtProjectNumber to show as ####-##### as you type in it.
2) Validate that all textboxes and comboboxes have value before running the script that copies the user inputs to Excel
3) If the Project Number input is already on the DataEntry list, instead of duplicating, it will update the file with the latest input.

I would really appreciate the help. Thanks!
 

Attachments

  • CCDB Entry Form.xlsm
    34.2 KB · Views: 5
Is there specific logic to -#####?
Can it be just sequential starting from 10001 and auto generated instead of forcing manual entry?
 
Chihiro said:
Is there specific logic to -#####?
Can it be just sequential starting from 10001 and auto generated instead of forcing manual entry?
Click to expand...

Actually yes, it is a standard format and it is not auto generated. There are already auto-generated numbers from another tool and it is just being copied. I just want to make sure that the entries are being validated because of users who refuse to use -
 
See attached.

For pattern validation for Project Number...
Following UDF has been added to Module1.

In regular module.
Code: 
Public Function prjNumCheck(eStr As String) As Boolean

Dim regex As Object
Set regex = CreateObject("VBScript.RegExp")

With regex
    .Pattern = "[0-9]{4}-[0-9]{5}"
End With

prjNumCheck = regex.Test(eStr)

End Function

And added following to cmdAdd_Click()
Code: 
If prjNumCheck(txtProjectNumber.Value) = False Then
    MsgBox "Only ####-##### format allowed for Project Number"
    txtProjectNumber.SetFocus
    Exit Sub
End If

for 2) added following to cmdAdd_Click()
Code: 
Dim ctrl As Control
For Each ctrl In Me.Controls
    If TypeName(ctrl) = "ComboBox" Or TypeName(ctrl) = "TextBox" Then
        If ctrl.Value = "" And ctrl.Name <> "txtRejectReason" Then
            MsgBox "Value is missing"
            ctrl.SetFocus
            Exit Sub
        End If
    End If
Next

for 3) added Public Object and sub in Module1
Code: 
Public prjDict as Object
Sub listPrjCell()
Dim i As Long
Set prjDict = CreateObject("Scripting.Dictionary")

For i = 2 To Sheets("DataEntry").Cells(Rows.Count, 1).End(xlUp).Row
    prjDict.Add Item:=Sheets("DataEntry").Cells(i, 1), Key:=Sheets("DataEntry").Cells(i, 1).Value
Next

End Sub

Also following in UserForm_Initialize()
Code: 
If prjDict Is Nothing Then
    Call listPrjCell
End If

And following in cmdAdd_Click()
Code: 
If prjDict.exists(txtProjectNumber.Value) Then
    emptyRow = prjDict.Item(txtProjectNumber.Value).Row
End If

I didn't do extensive testing. So, test it and let me know if you need further help with modification.
 

Attachments

  • CCDB Entry Form (1).xlsm
    37.9 KB · Views: 3
Chihiro said:
See attached.

For pattern validation for Project Number...
Following UDF has been added to Module1.

In regular module.
Code: 
Public Function prjNumCheck(eStr As String) As Boolean

Dim regex As Object
Set regex = CreateObject("VBScript.RegExp")

With regex
    .Pattern = "[0-9]{4}-[0-9]{5}"
End With

prjNumCheck = regex.Test(eStr)

End Function

And added following to cmdAdd_Click()
Code: 
If prjNumCheck(txtProjectNumber.Value) = False Then
    MsgBox "Only ####-##### format allowed for Project Number"
    txtProjectNumber.SetFocus
    Exit Sub
End If

for 2) added following to cmdAdd_Click()
Code: 
Dim ctrl As Control
For Each ctrl In Me.Controls
    If TypeName(ctrl) = "ComboBox" Or TypeName(ctrl) = "TextBox" Then
        If ctrl.Value = "" And ctrl.Name <> "txtRejectReason" Then
            MsgBox "Value is missing"
            ctrl.SetFocus
            Exit Sub
        End If
    End If
Next

for 3) added Public Object and sub in Module1
Code: 
Public prjDict as Object
Sub listPrjCell()
Dim i As Long
Set prjDict = CreateObject("Scripting.Dictionary")

For i = 2 To Sheets("DataEntry").Cells(Rows.Count, 1).End(xlUp).Row
    prjDict.Add Item:=Sheets("DataEntry").Cells(i, 1), Key:=Sheets("DataEntry").Cells(i, 1).Value
Next

End Sub

Also following in UserForm_Initialize()
Code: 
If prjDict Is Nothing Then
    Call listPrjCell
End If

And following in cmdAdd_Click()
Code: 
If prjDict.exists(txtProjectNumber.Value) Then
    emptyRow = prjDict.Item(txtProjectNumber.Value).Row
End If

I didn't do extensive testing. So, test it and let me know if you need further help with modification.
Click to expand...

Awesome work mate!!! It's working how I imagined it to be. I did some quick few tests and it is working well. If I wanted to put an alertbox asking the user if he/she is sure to update the record, where should it be? OK for update and Cancel if not.
 
hi chichiro,

I am now trying to create a search function and I am trying to use the subs that you used on the file.

I am using an inputbox as search query and I would call your subs to look for the record I am trying to look for. Am I on the right track or your subs are of
totally different function?

I know it is really out of the thread subject - I just look ridiculous reporting something you already did :)

Thanks
 
Depend on what you are searching for. If it's project number. Yes, you can use the "prjDict" object. Otherwise, I'd recommend going with standard Range.Find method.

Code will change depending on whether you are looking to do exact match only, or want partial match as well. How do you plan on using search result?

Since this is separate question from original, post a new thread with link to this thread.
 
I actually got it to work!!! But I need validation on it again for possible errors that my code may run into. Can you instead check my code and see if I need to change anything?

Also is it possible to force the users to enter date values on mm/dd/yyyy format on txtRequestDate and txtProcessDate? I forgot to include that on my first post
 

Attachments

  • CCDB Entry Form.xlsm
    50.9 KB · Views: 2
I found a problem with my code. It seems that the search function is working, but after the values have loaded, the update function that you build don't work. Instead of updating the records, they are being added at the bottom. I can't seem to get it right.
 
? Works fine on my end.

You may need to initialize "listPrjCell" if there was data submitted prior to search (i.e. just remove IF statement and Call listPrjCell).

As for date, why not just use calendar form?

One provided by Deepak.
http://chandoo.org/forum/threads/excel-userform-vba-calendar-control.26095/#post-156118

ActiveX Calendar Control
http://activex.microsoft.com/controls/vb6/MSCOMCT2.CAB

Here's another one.
http://chandoo.org/forum/threads/ge...-cell-in-excel-using-macro.25939/#post-155134
 
Thanks!! I figured users will be entering other texts on the date textbox other than dates according to the process rules. haha thanks!
 
You must log in or register to reply here.
Back
Top