Field validation

Tutorials Win: Microsoft Windows Forum

Hi;

Appreciate if someone could help me with the following:

I've 2 text fields called "Bank Name" & "Bank Account Number" in my form now.
Can anyone guide me on how to add the the following validation checking into
my form?

a) When Bank A is being selected, in the Bank Name field, check whether the
user has key in 10 digits into the Bank Account Number field. If the user has
key in something > or < than 10 digits, prompt out an error message that ask
them to key in the correct bank account number
b) When Bank B is being selected, check whether the user has key in 12
digits into the Bank Account Number field.
and etc
Top

Re: Field validation by Greg

Greg
Wed Jan 23 21:01:46 PST 2008

ah,

Something like this should do. Run the onExit macro from the
Bank_Account_Number field. Run the onEntry macro from all fields:

Option Explicit
Private mstrFF As String
Sub aFFOnExit()
With GetCurrentFF
Select Case ActiveDocument.FormFields("Bank_Name").Result
Case Is = "A"
If Len(.Result) <> 10 Or Not IsNumeric(.Result) Then
MsgBox "Your message"
mstrFF = .Name
End If
Case Is = "B"
If Len(.Result) <> 12 Or Not IsNumeric(.Result) Then
MsgBox "Your message"
mstrFF = .Name
End If
End Select
End With
End Sub
Public Sub AOnEntry()
Dim strCurrentFF As String
If LenB(mstrFF) > 0 Then
ActiveDocument.FormFields(mstrFF).Select
mstrFF = vbNullString
End If
End Sub
Private Function GetCurrentFF() As Word.FormField
With Selection
If .FormFields.Count = 1 Then
Set GetCurrentFF = .FormFields(1)
ElseIf .FormFields.Count = 0 And .Bookmarks.Count > 0 Then
Set GetCurrentFF = ActiveDocument.FormFields _
(.Bookmarks(.Bookmarks.Count).Name)
End If
End With
End Function


--
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.


ah wrote:
> Hi;
>
> Appreciate if someone could help me with the following:
>
> I've 2 text fields called "Bank Name" & "Bank Account Number" in my
> form now. Can anyone guide me on how to add the the following
> validation checking into my form?
>
> a) When Bank A is being selected, in the Bank Name field, check
> whether the user has key in 10 digits into the Bank Account Number
> field. If the user has key in something > or < than 10 digits, prompt
> out an error message that ask them to key in the correct bank account
> number
> b) When Bank B is being selected, check whether the user has key in 12
> digits into the Bank Account Number field.
> and etc


Top

Re: Field validation by ah

ah
Wed Jan 23 21:45:01 PST 2008

Hi Greg;

Thanks for your kind help. I tried, but it doesn't work at my side. below
are the steps that I've performed, please advice whether I've done it
correctly

a) Create a macro called "BankCheckingMacro"
b) Copy your code as follows: (Note: I've changed to BankName in stead of
Bank_Name to follow my field's name

Option Explicit
Private mstrFF As String
Sub aFFOnExit()
With GetCurrentFF
Select Case ActiveDocument.FormFields("BankName").Result
Case Is = "Citibank(7214)"
If Len(.Result) <> 10 Or Not IsNumeric(.Result) Then
MsgBox "Your message"
mstrFF = .Name
End If
Case Is = "Development-Bank-of-Singapore(7171)"
If Len(.Result) <> 12 Or Not IsNumeric(.Result) Then
MsgBox "Your message"
mstrFF = .Name
End If
End Select
End With
End Sub
Public Sub AOnEntry()
Dim strCurrentFF As String

If LenB(mstrFF) > 0 Then
ActiveDocument.FormFields(mstrFF).Select
mstrFF = vbNullString
End If
End Sub
Private Function GetCurrentFF() As Word.FormField
With Selection
If .FormFields.Count = 1 Then
Set GetCurrentFF = .FormFields(1)
ElseIf .FormFields.Count = 0 And .Bookmarks.Count > 0 Then
Set GetCurrentFF = ActiveDocument.FormFields _
(.Bookmarks(.Bookmarks.Count).Name)
End If
End With
End Function

c) Go to the "Bank_Account_Number" text field, select "AOnEntry" as the
Entry MAcro and "AffOnExit" as the exit macro

d) Tick "Calculate on exit"

Then, I tried to select the respective bank, but I did not get any error
prompt.

Please advice

"Greg Maxey" wrote:

> ah,
>
> Something like this should do. Run the onExit macro from the
> Bank_Account_Number field. Run the onEntry macro from all fields:
>
> Option Explicit
> Private mstrFF As String
> Sub aFFOnExit()
> With GetCurrentFF
> Select Case ActiveDocument.FormFields("Bank_Name").Result
> Case Is = "A"
> If Len(.Result) <> 10 Or Not IsNumeric(.Result) Then
> MsgBox "Your message"
> mstrFF = .Name
> End If
> Case Is = "B"
> If Len(.Result) <> 12 Or Not IsNumeric(.Result) Then
> MsgBox "Your message"
> mstrFF = .Name
> End If
> End Select
> End With
> End Sub
> Public Sub AOnEntry()
> Dim strCurrentFF As String
> If LenB(mstrFF) > 0 Then
> ActiveDocument.FormFields(mstrFF).Select
> mstrFF = vbNullString
> End If
> End Sub
> Private Function GetCurrentFF() As Word.FormField
> With Selection
> If .FormFields.Count = 1 Then
> Set GetCurrentFF = .FormFields(1)
> ElseIf .FormFields.Count = 0 And .Bookmarks.Count > 0 Then
> Set GetCurrentFF = ActiveDocument.FormFields _
> (.Bookmarks(.Bookmarks.Count).Name)
> End If
> End With
> End Function
>
>
> --
> Greg Maxey/Word MVP
> See:
> http://gregmaxey.mvps.org/word_tips.htm
> For some helpful tips using Word.
>
>
> ah wrote:
> > Hi;
> >
> > Appreciate if someone could help me with the following:
> >
> > I've 2 text fields called "Bank Name" & "Bank Account Number" in my
> > form now. Can anyone guide me on how to add the the following
> > validation checking into my form?
> >
> > a) When Bank A is being selected, in the Bank Name field, check
> > whether the user has key in 10 digits into the Bank Account Number
> > field. If the user has key in something > or < than 10 digits, prompt
> > out an error message that ask them to key in the correct bank account
> > number
> > b) When Bank B is being selected, check whether the user has key in 12
> > digits into the Bank Account Number field.
> > and etc
>
>
>
Top

Re: Field validation by Greg

Greg
Wed Jan 23 22:00:45 PST 2008

ah,

You don't need to create a macro. The three macros you need were provided.

You need to open your document. Then open the VB Editor. You can paste the
code in the "This Document" module or create a new module in project.

I am not exactly sure what our sequence is. I assumed that you have a
dropdown with the entries "A" and "B." You select A or B then you go to the
AccountNumberField and enter the 10 or 12 digit number. If the number isn't
the right number of digits or not numeric then you want a message box and
the user to be trapped in the AccountNumberField until they get it right.

So you want to run the aFFOnExit Procedrue "on exit" from the
AccountNumberField and run the AOnEntry Procedure in all fields.

--
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.


ah wrote:
> Hi Greg;
>
> Thanks for your kind help. I tried, but it doesn't work at my side.
> below are the steps that I've performed, please advice whether I've
> done it correctly
>
> a) Create a macro called "BankCheckingMacro"
> b) Copy your code as follows: (Note: I've changed to BankName in
> stead of Bank_Name to follow my field's name
>
> Option Explicit
> Private mstrFF As String
> Sub aFFOnExit()
> With GetCurrentFF
> Select Case ActiveDocument.FormFields("BankName").Result
> Case Is = "Citibank(7214)"
> If Len(.Result) <> 10 Or Not IsNumeric(.Result) Then
> MsgBox "Your message"
> mstrFF = .Name
> End If
> Case Is = "Development-Bank-of-Singapore(7171)"
> If Len(.Result) <> 12 Or Not IsNumeric(.Result) Then
> MsgBox "Your message"
> mstrFF = .Name
> End If
> End Select
> End With
> End Sub
> Public Sub AOnEntry()
> Dim strCurrentFF As String
>
> If LenB(mstrFF) > 0 Then
> ActiveDocument.FormFields(mstrFF).Select
> mstrFF = vbNullString
> End If
> End Sub
> Private Function GetCurrentFF() As Word.FormField
> With Selection
> If .FormFields.Count = 1 Then
> Set GetCurrentFF = .FormFields(1)
> ElseIf .FormFields.Count = 0 And .Bookmarks.Count > 0 Then
> Set GetCurrentFF = ActiveDocument.FormFields _
> (.Bookmarks(.Bookmarks.Count).Name)
> End If
> End With
> End Function
>
> c) Go to the "Bank_Account_Number" text field, select "AOnEntry" as
> the Entry MAcro and "AffOnExit" as the exit macro
>
> d) Tick "Calculate on exit"
>
> Then, I tried to select the respective bank, but I did not get any
> error prompt.
>
> Please advice
>
> "Greg Maxey" wrote:
>
>> ah,
>>
>> Something like this should do. Run the onExit macro from the
>> Bank_Account_Number field. Run the onEntry macro from all fields:
>>
>> Option Explicit
>> Private mstrFF As String
>> Sub aFFOnExit()
>> With GetCurrentFF
>> Select Case ActiveDocument.FormFields("Bank_Name").Result
>> Case Is = "A"
>> If Len(.Result) <> 10 Or Not IsNumeric(.Result) Then
>> MsgBox "Your message"
>> mstrFF = .Name
>> End If
>> Case Is = "B"
>> If Len(.Result) <> 12 Or Not IsNumeric(.Result) Then
>> MsgBox "Your message"
>> mstrFF = .Name
>> End If
>> End Select
>> End With
>> End Sub
>> Public Sub AOnEntry()
>> Dim strCurrentFF As String
>> If LenB(mstrFF) > 0 Then
>> ActiveDocument.FormFields(mstrFF).Select
>> mstrFF = vbNullString
>> End If
>> End Sub
>> Private Function GetCurrentFF() As Word.FormField
>> With Selection
>> If .FormFields.Count = 1 Then
>> Set GetCurrentFF = .FormFields(1)
>> ElseIf .FormFields.Count = 0 And .Bookmarks.Count > 0 Then
>> Set GetCurrentFF = ActiveDocument.FormFields _
>> (.Bookmarks(.Bookmarks.Count).Name)
>> End If
>> End With
>> End Function
>>
>>
>> --
>> Greg Maxey/Word MVP
>> See:
>> http://gregmaxey.mvps.org/word_tips.htm
>> For some helpful tips using Word.
>>
>>
>> ah wrote:
>>> Hi;
>>>
>>> Appreciate if someone could help me with the following:
>>>
>>> I've 2 text fields called "Bank Name" & "Bank Account Number" in my
>>> form now. Can anyone guide me on how to add the the following
>>> validation checking into my form?
>>>
>>> a) When Bank A is being selected, in the Bank Name field, check
>>> whether the user has key in 10 digits into the Bank Account Number
>>> field. If the user has key in something > or < than 10 digits,
>>> prompt out an error message that ask them to key in the correct
>>> bank account number
>>> b) When Bank B is being selected, check whether the user has key in
>>> 12 digits into the Bank Account Number field.
>>> and etc


Top

Re: Field validation by ah

ah
Wed Jan 23 22:25:00 PST 2008

Hi Greg;

Yup, I've copied your code into the module, and select "aOffOnExit" on the
exit field of my "Bank_Account_Number" and "AOnEntry" as the entry macro for
the next text field that I've, but it doesn't work and I did not get any
prompt when I key in <10 digits into the Bank_Account_Number ffield.

FYI, I've a user form that run at the BankName field as follows, is this the
issue?

Private Sub UserForm_Initialize()
Dim myArray1() As String 'AUSTRALIA
Dim myArray5() As String 'CHINA
Dim myArray8() As String 'HONG KONG
Dim myArray9() As String 'INDIA/SRI LANGKA/BANGLADESH
Dim myArray10() As String 'INDONESIA
Dim myArray11() As String 'JAPAN
Dim myArray12() As String 'KOREA
Dim myArray14() As String 'MALAYSIA
Dim myArray15() As String 'PHILIPPINES
Dim myArray16() As String 'SINGAPORE
Dim myArray17() As String 'TAIWAN
Dim myArray18() As String 'THAILAND
Dim myArray21() As String 'VIETNAM
Dim myArray22() As String 'NEW ZEALAND

Dim i As Long

myArray1 = Split("ADL-ADELAIDE-BANK-LIMITED ADV-ADVANCE-BANK-AUSTRALIA-LTD
ANZ-AUSTRALIA-AND-NEW-ZEALAND-BANKING-GROUP-LIMITED BBL-BENDIGO-BANK-LIMITED
BML-BANK-OF-MELBOURNE BQL-BANK-OF-QUEENSLAND-LIMITED
BSA-BANK-OF-SOUTH-AUSTRALIA-- BWA-BANK-OF-WESTERN-AUSTRALIA-LIMITED
CBA-COMMONWEALTH-BANK-OF-AUSTRALIA CMB-CHASE-MANHATTAN-BANK-(THE)
CRU-CREDIT-UNION-FINANCIAL-SERVICES--LIMITED CST-COLONIAL-STATE-BANK--
CTI-CITIBANK-LIMITED CUS-CREDIT-UNION-SETTLEMENT-SERVICES-LTD
GBS-GREATER-BUILDING-SOCIETY-LIMITED GIO-GIO-BUILDING-SOCIETY-LIMITED
HBA-HSBC-BANK-AUSTRALIA-LIMITED HBS-HERITAGE-BUILDING-SOCIETY-LIMITED
HOM-HOME-BUILDING-SOCIETY-LIMITED ING-ING-BANK-AUSTRALIA-LIMITED
MBL-MACQUARIE-BANK-LIMITED MET-METWAY-(SUNCORP-METWAY-BANK-LTD)
NAB-NATIONAL-AUSTRALIA-BANK-LIMITED STG-ST-GEORGE-BANK-LIMITED
WBC-WESTPAC-BANKING-CORPORATION ASB-ASB-BANK-LIMITED BNZ-BANK-OF-NEW-ZEALAND
NBNZ-THE-NATIONAL-BANK-OF-NEW-ZEALAND TSB-TSB-BANK-LIMITED")

'myArray5 = Split("NA")

'myArray8 = Split("NA)")

'myArray9 = Split("NA")

'myArray10 = Split("NA")

'myArray11 = Split("NA")

myArray12 = Split("Korea-Develpoment-Bank(02) Industrial-Bank-of-Korea(03)
Kook-Min-Bank(04) Korea-Exchange-Bank(05) Fisheries-Coorperatives(07)
Agri-(Joogang)(11) Agricultural-Cooperatives(12) Woori-Bank(20)
Scfirst-Bank(23) Citi-Bank(27) Daegu-Bank(31) Pusan-Bank(32) Kwangju-Bank(34)
Jeju-Bank(35) Jeon-Buk-Bank(37) KyoungNam-Bank(39)
Community-Credt-Cooperatives(45) Korea-Post(71) Hana-Bank(81) Shinhan(88)")

myArray14 = Split("Bank-Simpanan-National(BSN) Public-Bank(PBB) Citibank(CB)
Maybank(MBB) HongKong-Bank(HSBC) Commerce-Int.Merchant-Bank(CIMB))")

'myArray15 = Split("NA")

myArray16 = Split("Citibank(7214) Development-Bank-of-Singapore(7171)
HongKong&Shanghai-Banking-Co(7232) Keppel-Tatlee-Bank-of-Singapore(7029)
Oversea-Chinese-Banking-Corp-Ltd.(7339) Overseas-Union-Bank-Limited(7348)
Post-Office-Saving-Bank(7171081) Standard-Chartered-Bank(7144)
United-Overseas-Bank-Limited(7375)")

'myArray17 = Split("NA")

'myArray18 = Split("NA")

'myArray21 = Split("NA")

myArray22 = Split("ADL-ADELAIDE-BANK-LIMITED ADV-ADVANCE-BANK-AUSTRALIA-LTD
ANZ-AUSTRALIA-AND-NEW-ZEALAND-BANKING-GROUP-LIMITED BBL-BENDIGO-BANK-LIMITED
BML-BANK-OF-MELBOURNE BQL-BANK-OF-QUEENSLAND-LIMITED
BSA-BANK-OF-SOUTH-AUSTRALIA-- BWA-BANK-OF-WESTERN-AUSTRALIA-LIMITED
CBA-COMMONWEALTH-BANK-OF-AUSTRALIA CMB-CHASE-MANHATTAN-BANK-(THE)
CRU-CREDIT-UNION-FINANCIAL-SERVICES--LIMITED CST-COLONIAL-STATE-BANK--
CTI-CITIBANK-LIMITED CUS-CREDIT-UNION-SETTLEMENT-SERVICES-LTD
GBS-GREATER-BUILDING-SOCIETY-LIMITED GIO-GIO-BUILDING-SOCIETY-LIMITED
HBA-HSBC-BANK-AUSTRALIA-LIMITED HBS-HERITAGE-BUILDING-SOCIETY-LIMITED
HOM-HOME-BUILDING-SOCIETY-LIMITED ING-ING-BANK-AUSTRALIA-LIMITED
MBL-MACQUARIE-BANK-LIMITED MET-METWAY-(SUNCORP-METWAY-BANK-LTD)
NAB-NATIONAL-AUSTRALIA-BANK-LIMITED STG-ST-GEORGE-BANK-LIMITED
WBC-WESTPAC-BANKING-CORPORATION ASB-ASB-BANK-LIMITED BNZ-BANK-OF-NEW-ZEALAND
NBNZ-THE-NATIONAL-BANK-OF-NEW-ZEALAND TSB-TSB-BANK-LIMITED")

Me.BankName.Clear

Select Case ActiveDocument.FormFields("Country_Name").Result
Case "AUSTRALIA"
Me.BankName.AddItem "Please select one"
Me.BankName.ListIndex = 0
Me.BankName.List = myArray1

Case "CHINA"
Me.BankName.AddItem "Type your bank name here"
Me.BankName.ListIndex = 0
Me.BankName.SetFocus
Me.BankName.SelStart = 0
Me.BankName.SelLength = Len(Me.BankName.Text)

Case "HONG KONG"
Me.BankName.AddItem "Type your bank name here"
Me.BankName.ListIndex = 0
Me.BankName.SetFocus
Me.BankName.SelStart = 0
Me.BankName.SelLength = Len(Me.BankName.Text)

Case "INDIA/SRI LANGKA/BANGLADESH"
Me.BankName.AddItem "Please provide bank name and branch"
Me.BankName.ListIndex = 0
Me.BankName.SetFocus
Me.BankName.SelStart = 0
Me.BankName.SelLength = Len(Me.BankName.Text)

Case "INDONESIA"
Me.BankName.AddItem "Type your bank name here"
Me.BankName.ListIndex = 0
Me.BankName.SetFocus
Me.BankName.SelStart = 0
Me.BankName.SelLength = Len(Me.BankName.Text)

Case "JAPAN"
Me.BankName.AddItem "Please fill up Bank Update form"
Me.BankName.ListIndex = 0
Me.BankName.SetFocus
Me.BankName.SelStart = 0
Me.BankName.SelLength = Len(Me.BankName.Text)

Case "KOREA"
Me.BankName.AddItem "Please select one"
Me.BankName.ListIndex = 0
Me.BankName.List = myArray12

Case "MALAYSIA"
Me.BankName.AddItem "Please select one"
Me.BankName.ListIndex = 0
Me.BankName.List = myArray14

Case "PHILIPPINES"
Me.BankName.AddItem "Please provide bank name and branch"
Me.BankName.ListIndex = 0
Me.BankName.SetFocus
Me.BankName.SelStart = 0
Me.BankName.SelLength = Len(Me.BankName.Text)

Case "SINGAPORE"
Me.BankName.AddItem "Please from the list"
Me.BankName.ListIndex = 0
Me.BankName.List = myArray16

Case "TAIWAN"
Me.BankName.AddItem "Type your bank name here. Bank Name (Code) â??
Branch Name (Code)"
Me.BankName.ListIndex = 0
Me.BankName.SetFocus
Me.BankName.SelStart = 0
Me.BankName.SelLength = Len(Me.BankName.Text)

Case "THAILAND"
Me.BankName.AddItem "Type your bank name here. Provide Branch code:
BBLxxx.TFBxxx,SCBxxx,BAYxxx"
Me.BankName.ListIndex = 0
Me.BankName.SetFocus
Me.BankName.SelStart = 0
Me.BankName.SelLength = Len(Me.BankName.Text)

Case "VIETNAM"
Me.BankName.AddItem "Please provide bank name and branch"
Me.BankName.ListIndex = 0
Me.BankName.SetFocus
Me.BankName.SelStart = 0
Me.BankName.SelLength = Len(Me.BankName.Text)

Case "NEW ZEALAND"
Me.BankName.AddItem "Please select one"
Me.BankName.ListIndex = 0
Me.BankName.List = myArray22


End Select
End Sub

Private Sub OK_Click()
ActiveDocument.FormFields("BankName").Result = Me.BankName.Text
ActiveDocument.Bookmarks("BankName").Range.Fields(1).Result.Select
Unload Me
End Sub


Private Sub Reset_Click()


ActiveDocument.FormFields("BankName").Result = " "
ActiveDocument.Bookmarks("BankName").Range.Fields(1).Result.Select
Unload Me
End Sub




"Greg Maxey" wrote:

> ah,
>
> You don't need to create a macro. The three macros you need were provided.
>
> You need to open your document. Then open the VB Editor. You can paste the
> code in the "This Document" module or create a new module in project.
>
> I am not exactly sure what our sequence is. I assumed that you have a
> dropdown with the entries "A" and "B." You select A or B then you go to the
> AccountNumberField and enter the 10 or 12 digit number. If the number isn't
> the right number of digits or not numeric then you want a message box and
> the user to be trapped in the AccountNumberField until they get it right.
>
> So you want to run the aFFOnExit Procedrue "on exit" from the
> AccountNumberField and run the AOnEntry Procedure in all fields.
>
> --
> Greg Maxey/Word MVP
> See:
> http://gregmaxey.mvps.org/word_tips.htm
> For some helpful tips using Word.
>
>
> ah wrote:
> > Hi Greg;
> >
> > Thanks for your kind help. I tried, but it doesn't work at my side.
> > below are the steps that I've performed, please advice whether I've
> > done it correctly
> >
> > a) Create a macro called "BankCheckingMacro"
> > b) Copy your code as follows: (Note: I've changed to BankName in
> > stead of Bank_Name to follow my field's name
> >
> > Option Explicit
> > Private mstrFF As String
> > Sub aFFOnExit()
> > With GetCurrentFF
> > Select Case ActiveDocument.FormFields("BankName").Result
> > Case Is = "Citibank(7214)"
> > If Len(.Result) <> 10 Or Not IsNumeric(.Result) Then
> > MsgBox "Your message"
> > mstrFF = .Name
> > End If
> > Case Is = "Development-Bank-of-Singapore(7171)"
> > If Len(.Result) <> 12 Or Not IsNumeric(.Result) Then
> > MsgBox "Your message"
> > mstrFF = .Name
> > End If
> > End Select
> > End With
> > End Sub
> > Public Sub AOnEntry()
> > Dim strCurrentFF As String
> >
> > If LenB(mstrFF) > 0 Then
> > ActiveDocument.FormFields(mstrFF).Select
> > mstrFF = vbNullString
> > End If
> > End Sub
> > Private Function GetCurrentFF() As Word.FormField
> > With Selection
> > If .FormFields.Count = 1 Then
> > Set GetCurrentFF = .FormFields(1)
> > ElseIf .FormFields.Count = 0 And .Bookmarks.Count > 0 Then
> > Set GetCurrentFF = ActiveDocument.FormFields _
> > (.Bookmarks(.Bookmarks.Count).Name)
> > End If
> > End With
> > End Function
> >
> > c) Go to the "Bank_Account_Number" text field, select "AOnEntry" as
> > the Entry MAcro and "AffOnExit" as the exit macro
> >
> > d) Tick "Calculate on exit"
> >
> > Then, I tried to select the respective bank, but I did not get any
> > error prompt.
> >
> > Please advice
> >
> > "Greg Maxey" wrote:
> >
> >> ah,
> >>
> >> Something like this should do. Run the onExit macro from the
> >> Bank_Account_Number field. Run the onEntry macro from all fields:
> >>
> >> Option Explicit
> >> Private mstrFF As String
> >> Sub aFFOnExit()
> >> With GetCurrentFF
> >> Select Case ActiveDocument.FormFields("Bank_Name").Result
> >> Case Is = "A"
> >> If Len(.Result) <> 10 Or Not IsNumeric(.Result) Then
> >> MsgBox "Your message"
> >> mstrFF = .Name
> >> End If
> >> Case Is = "B"
> >> If Len(.Result) <> 12 Or Not IsNumeric(.Result) Then
> >> MsgBox "Your message"
> >> mstrFF = .Name
> >> End If
> >> End Select
> >> End With
> >> End Sub
> >> Public Sub AOnEntry()
> >> Dim strCurrentFF As String
> >> If LenB(mstrFF) > 0 Then
> >> ActiveDocument.FormFields(mstrFF).Select
> >> mstrFF = vbNullString
> >> End If
> >> End Sub
> >> Private Function GetCurrentFF() As Word.FormField
> >> With Selection
> >> If .FormFields.Count = 1 Then
> >> Set GetCurrentFF = .FormFields(1)
> >> ElseIf .FormFields.Count = 0 And .Bookmarks.Count > 0 Then
> >> Set GetCurrentFF = ActiveDocument.FormFields _
> >> (.Bookmarks(.Bookmarks.Count).Name)
> >> End If
> >> End With
> >> End Function
> >>
> >>
> >> --
> >> Greg Maxey/Word MVP
> >> See:
> >> http://gregmaxey.mvps.org/word_tips.htm
> >> For some helpful tips using Word.
> >>
> >>
> >> ah wrote:
> >>> Hi;
> >>>
> >>> Appreciate if someone could help me with the following:
> >>>
> >>> I've 2 text fields called "Bank Name" & "Bank Account Number" in my
> >>> form now. Can anyone guide me on how to add the the following
> >>> validation checking into my form?
> >>>
> >>> a) When Bank A is being selected, in the Bank Name field, check
> >>> whether the user has key in 10 digits into the Bank Account Number
> >>> field. If the user has key in something > or < than 10 digits,
> >>> prompt out an error message that ask them to key in the correct
> >>> bank account number
> >>> b) When Bank B is being selected, check whether the user has key in
> >>> 12 digits into the Bank Account Number field.
> >>> and etc
>
>
>
Top

Re: Field validation by Greg

Greg
Thu Jan 24 04:10:23 PST 2008

ah,

The little snippet of code I sent to you works here, but if it doesn't work
for you something in your complete code or the way it is being applied is at
fault. I really can't say what without seeing it and if it didn't provide
you with enough information to work through it yourself then you will have
to let me have a look at the whole thing. I will probably have some time
later today so if you will e-mail it to me I will have a look.

--
Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

"ah" <ah@discussions.microsoft.com> wrote in message
news:A2E5F5BA-3939-4DDD-BF8F-796465A8F485@microsoft.com...
> Hi Greg;
>
> Yup, I've copied your code into the module, and select "aOffOnExit" on the
> exit field of my "Bank_Account_Number" and "AOnEntry" as the entry macro
> for
> the next text field that I've, but it doesn't work and I did not get any
> prompt when I key in <10 digits into the Bank_Account_Number ffield.
>
> FYI, I've a user form that run at the BankName field as follows, is this
> the
> issue?
>
> Private Sub UserForm_Initialize()
> Dim myArray1() As String 'AUSTRALIA
> Dim myArray5() As String 'CHINA
> Dim myArray8() As String 'HONG KONG
> Dim myArray9() As String 'INDIA/SRI LANGKA/BANGLADESH
> Dim myArray10() As String 'INDONESIA
> Dim myArray11() As String 'JAPAN
> Dim myArray12() As String 'KOREA
> Dim myArray14() As String 'MALAYSIA
> Dim myArray15() As String 'PHILIPPINES
> Dim myArray16() As String 'SINGAPORE
> Dim myArray17() As String 'TAIWAN
> Dim myArray18() As String 'THAILAND
> Dim myArray21() As String 'VIETNAM
> Dim myArray22() As String 'NEW ZEALAND
>
> Dim i As Long
>
> myArray1 = Split("ADL-ADELAIDE-BANK-LIMITED ADV-ADVANCE-BANK-AUSTRALIA-LTD
> ANZ-AUSTRALIA-AND-NEW-ZEALAND-BANKING-GROUP-LIMITED
> BBL-BENDIGO-BANK-LIMITED
> BML-BANK-OF-MELBOURNE BQL-BANK-OF-QUEENSLAND-LIMITED
> BSA-BANK-OF-SOUTH-AUSTRALIA-- BWA-BANK-OF-WESTERN-AUSTRALIA-LIMITED
> CBA-COMMONWEALTH-BANK-OF-AUSTRALIA CMB-CHASE-MANHATTAN-BANK-(THE)
> CRU-CREDIT-UNION-FINANCIAL-SERVICES--LIMITED CST-COLONIAL-STATE-BANK--
> CTI-CITIBANK-LIMITED CUS-CREDIT-UNION-SETTLEMENT-SERVICES-LTD
> GBS-GREATER-BUILDING-SOCIETY-LIMITED GIO-GIO-BUILDING-SOCIETY-LIMITED
> HBA-HSBC-BANK-AUSTRALIA-LIMITED HBS-HERITAGE-BUILDING-SOCIETY-LIMITED
> HOM-HOME-BUILDING-SOCIETY-LIMITED ING-ING-BANK-AUSTRALIA-LIMITED
> MBL-MACQUARIE-BANK-LIMITED MET-METWAY-(SUNCORP-METWAY-BANK-LTD)
> NAB-NATIONAL-AUSTRALIA-BANK-LIMITED STG-ST-GEORGE-BANK-LIMITED
> WBC-WESTPAC-BANKING-CORPORATION ASB-ASB-BANK-LIMITED
> BNZ-BANK-OF-NEW-ZEALAND
> NBNZ-THE-NATIONAL-BANK-OF-NEW-ZEALAND TSB-TSB-BANK-LIMITED")
>
> 'myArray5 = Split("NA")
>
> 'myArray8 = Split("NA)")
>
> 'myArray9 = Split("NA")
>
> 'myArray10 = Split("NA")
>
> 'myArray11 = Split("NA")
>
> myArray12 = Split("Korea-Develpoment-Bank(02) Industrial-Bank-of-Korea(03)
> Kook-Min-Bank(04) Korea-Exchange-Bank(05) Fisheries-Coorperatives(07)
> Agri-(Joogang)(11) Agricultural-Cooperatives(12) Woori-Bank(20)
> Scfirst-Bank(23) Citi-Bank(27) Daegu-Bank(31) Pusan-Bank(32)
> Kwangju-Bank(34)
> Jeju-Bank(35) Jeon-Buk-Bank(37) KyoungNam-Bank(39)
> Community-Credt-Cooperatives(45) Korea-Post(71) Hana-Bank(81)
> Shinhan(88)")
>
> myArray14 = Split("Bank-Simpanan-National(BSN) Public-Bank(PBB)
> Citibank(CB)
> Maybank(MBB) HongKong-Bank(HSBC) Commerce-Int.Merchant-Bank(CIMB))")
>
> 'myArray15 = Split("NA")
>
> myArray16 = Split("Citibank(7214) Development-Bank-of-Singapore(7171)
> HongKong&Shanghai-Banking-Co(7232) Keppel-Tatlee-Bank-of-Singapore(7029)
> Oversea-Chinese-Banking-Corp-Ltd.(7339) Overseas-Union-Bank-Limited(7348)
> Post-Office-Saving-Bank(7171081) Standard-Chartered-Bank(7144)
> United-Overseas-Bank-Limited(7375)")
>
> 'myArray17 = Split("NA")
>
> 'myArray18 = Split("NA")
>
> 'myArray21 = Split("NA")
>
> myArray22 = Split("ADL-ADELAIDE-BANK-LIMITED
> ADV-ADVANCE-BANK-AUSTRALIA-LTD
> ANZ-AUSTRALIA-AND-NEW-ZEALAND-BANKING-GROUP-LIMITED
> BBL-BENDIGO-BANK-LIMITED
> BML-BANK-OF-MELBOURNE BQL-BANK-OF-QUEENSLAND-LIMITED
> BSA-BANK-OF-SOUTH-AUSTRALIA-- BWA-BANK-OF-WESTERN-AUSTRALIA-LIMITED
> CBA-COMMONWEALTH-BANK-OF-AUSTRALIA CMB-CHASE-MANHATTAN-BANK-(THE)
> CRU-CREDIT-UNION-FINANCIAL-SERVICES--LIMITED CST-COLONIAL-STATE-BANK--
> CTI-CITIBANK-LIMITED CUS-CREDIT-UNION-SETTLEMENT-SERVICES-LTD
> GBS-GREATER-BUILDING-SOCIETY-LIMITED GIO-GIO-BUILDING-SOCIETY-LIMITED
> HBA-HSBC-BANK-AUSTRALIA-LIMITED HBS-HERITAGE-BUILDING-SOCIETY-LIMITED
> HOM-HOME-BUILDING-SOCIETY-LIMITED ING-ING-BANK-AUSTRALIA-LIMITED
> MBL-MACQUARIE-BANK-LIMITED MET-METWAY-(SUNCORP-METWAY-BANK-LTD)
> NAB-NATIONAL-AUSTRALIA-BANK-LIMITED STG-ST-GEORGE-BANK-LIMITED
> WBC-WESTPAC-BANKING-CORPORATION ASB-ASB-BANK-LIMITED
> BNZ-BANK-OF-NEW-ZEALAND
> NBNZ-THE-NATIONAL-BANK-OF-NEW-ZEALAND TSB-TSB-BANK-LIMITED")
>
> Me.BankName.Clear
>
> Select Case ActiveDocument.FormFields("Country_Name").Result
> Case "AUSTRALIA"
> Me.BankName.AddItem "Please select one"
> Me.BankName.ListIndex = 0
> Me.BankName.List = myArray1
>
> Case "CHINA"
> Me.BankName.AddItem "Type your bank name here"
> Me.BankName.ListIndex = 0
> Me.BankName.SetFocus
> Me.BankName.SelStart = 0
> Me.BankName.SelLength = Len(Me.BankName.Text)
>
> Case "HONG KONG"
> Me.BankName.AddItem "Type your bank name here"
> Me.BankName.ListIndex = 0
> Me.BankName.SetFocus
> Me.BankName.SelStart = 0
> Me.BankName.SelLength = Len(Me.BankName.Text)
>
> Case "INDIA/SRI LANGKA/BANGLADESH"
> Me.BankName.AddItem "Please provide bank name and branch"
> Me.BankName.ListIndex = 0
> Me.BankName.SetFocus
> Me.BankName.SelStart = 0
> Me.BankName.SelLength = Len(Me.BankName.Text)
>
> Case "INDONESIA"
> Me.BankName.AddItem "Type your bank name here"
> Me.BankName.ListIndex = 0
> Me.BankName.SetFocus
> Me.BankName.SelStart = 0
> Me.BankName.SelLength = Len(Me.BankName.Text)
>
> Case "JAPAN"
> Me.BankName.AddItem "Please fill up Bank Update form"
> Me.BankName.ListIndex = 0
> Me.BankName.SetFocus
> Me.BankName.SelStart = 0
> Me.BankName.SelLength = Len(Me.BankName.Text)
>
> Case "KOREA"
> Me.BankName.AddItem "Please select one"
> Me.BankName.ListIndex = 0
> Me.BankName.List = myArray12
>
> Case "MALAYSIA"
> Me.BankName.AddItem "Please select one"
> Me.BankName.ListIndex = 0
> Me.BankName.List = myArray14
>
> Case "PHILIPPINES"
> Me.BankName.AddItem "Please provide bank name and branch"
> Me.BankName.ListIndex = 0
> Me.BankName.SetFocus
> Me.BankName.SelStart = 0
> Me.BankName.SelLength = Len(Me.BankName.Text)
>
> Case "SINGAPORE"
> Me.BankName.AddItem "Please from the list"
> Me.BankName.ListIndex = 0
> Me.BankName.List = myArray16
>
> Case "TAIWAN"
> Me.BankName.AddItem "Type your bank name here. Bank Name (Code) -
> Branch Name (Code)"
> Me.BankName.ListIndex = 0
> Me.BankName.SetFocus
> Me.BankName.SelStart = 0
> Me.BankName.SelLength = Len(Me.BankName.Text)
>
> Case "THAILAND"
> Me.BankName.AddItem "Type your bank name here. Provide Branch code:
> BBLxxx.TFBxxx,SCBxxx,BAYxxx"
> Me.BankName.ListIndex = 0
> Me.BankName.SetFocus
> Me.BankName.SelStart = 0
> Me.BankName.SelLength = Len(Me.BankName.Text)
>
> Case "VIETNAM"
> Me.BankName.AddItem "Please provide bank name and branch"
> Me.BankName.ListIndex = 0
> Me.BankName.SetFocus
> Me.BankName.SelStart = 0
> Me.BankName.SelLength = Len(Me.BankName.Text)
>
> Case "NEW ZEALAND"
> Me.BankName.AddItem "Please select one"
> Me.BankName.ListIndex = 0
> Me.BankName.List = myArray22
>
>
> End Select
> End Sub
>
> Private Sub OK_Click()
> ActiveDocument.FormFields("BankName").Result = Me.BankName.Text
> ActiveDocument.Bookmarks("BankName").Range.Fields(1).Result.Select
> Unload Me
> End Sub
>
>
> Private Sub Reset_Click()
>
>
> ActiveDocument.FormFields("BankName").Result = " "
> ActiveDocument.Bookmarks("BankName").Range.Fields(1).Result.Select
> Unload Me
> End Sub
>
>
>
>
> "Greg Maxey" wrote:
>
>> ah,
>>
>> You don't need to create a macro. The three macros you need were
>> provided.
>>
>> You need to open your document. Then open the VB Editor. You can paste
>> the
>> code in the "This Document" module or create a new module in project.
>>
>> I am not exactly sure what our sequence is. I assumed that you have a
>> dropdown with the entries "A" and "B." You select A or B then you go to
>> the
>> AccountNumberField and enter the 10 or 12 digit number. If the number
>> isn't
>> the right number of digits or not numeric then you want a message box and
>> the user to be trapped in the AccountNumberField until they get it right.
>>
>> So you want to run the aFFOnExit Procedrue "on exit" from the
>> AccountNumberField and run the AOnEntry Procedure in all fields.
>>
>> --
>> Greg Maxey/Word MVP
>> See:
>> http://gregmaxey.mvps.org/word_tips.htm
>> For some helpful tips using Word.
>>
>>
>> ah wrote:
>> > Hi Greg;
>> >
>> > Thanks for your kind help. I tried, but it doesn't work at my side.
>> > below are the steps that I've performed, please advice whether I've
>> > done it correctly
>> >
>> > a) Create a macro called "BankCheckingMacro"
>> > b) Copy your code as follows: (Note: I've changed to BankName in
>> > stead of Bank_Name to follow my field's name
>> >
>> > Option Explicit
>> > Private mstrFF As String
>> > Sub aFFOnExit()
>> > With GetCurrentFF
>> > Select Case ActiveDocument.FormFields("BankName").Result
>> > Case Is = "Citibank(7214)"
>> > If Len(.Result) <> 10 Or Not IsNumeric(.Result) Then
>> > MsgBox "Your message"
>> > mstrFF = .Name
>> > End If
>> > Case Is = "Development-Bank-of-Singapore(7171)"
>> > If Len(.Result) <> 12 Or Not IsNumeric(.Result) Then
>> > MsgBox "Your message"
>> > mstrFF = .Name
>> > End If
>> > End Select
>> > End With
>> > End Sub
>> > Public Sub AOnEntry()
>> > Dim strCurrentFF As String
>> >
>> > If LenB(mstrFF) > 0 Then
>> > ActiveDocument.FormFields(mstrFF).Select
>> > mstrFF = vbNullString
>> > End If
>> > End Sub
>> > Private Function GetCurrentFF() As Word.FormField
>> > With Selection
>> > If .FormFields.Count = 1 Then
>> > Set GetCurrentFF = .FormFields(1)
>> > ElseIf .FormFields.Count = 0 And .Bookmarks.Count > 0 Then
>> > Set GetCurrentFF = ActiveDocument.FormFields _
>> > (.Bookmarks(.Bookmarks.Count).Name)
>> > End If
>> > End With
>> > End Function
>> >
>> > c) Go to the "Bank_Account_Number" text field, select "AOnEntry" as
>> > the Entry MAcro and "AffOnExit" as the exit macro
>> >
>> > d) Tick "Calculate on exit"
>> >
>> > Then, I tried to select the respective bank, but I did not get any
>> > error prompt.
>> >
>> > Please advice
>> >
>> > "Greg Maxey" wrote:
>> >
>> >> ah,
>> >>
>> >> Something like this should do. Run the onExit macro from the
>> >> Bank_Account_Number field. Run the onEntry macro from all fields:
>> >>
>> >> Option Explicit
>> >> Private mstrFF As String
>> >> Sub aFFOnExit()
>> >> With GetCurrentFF
>> >> Select Case ActiveDocument.FormFields("Bank_Name").Result
>> >> Case Is = "A"
>> >> If Len(.Result) <> 10 Or Not IsNumeric(.Result) Then
>> >> MsgBox "Your message"
>> >> mstrFF = .Name
>> >> End If
>> >> Case Is = "B"
>> >> If Len(.Result) <> 12 Or Not IsNumeric(.Result) Then
>> >> MsgBox "Your message"
>> >> mstrFF = .Name
>> >> End If
>> >> End Select
>> >> End With
>> >> End Sub
>> >> Public Sub AOnEntry()
>> >> Dim strCurrentFF As String
>> >> If LenB(mstrFF) > 0 Then
>> >> ActiveDocument.FormFields(mstrFF).Select
>> >> mstrFF = vbNullString
>> >> End If
>> >> End Sub
>> >> Private Function GetCurrentFF() As Word.FormField
>> >> With Selection
>> >> If .FormFields.Count = 1 Then
>> >> Set GetCurrentFF = .FormFields(1)
>> >> ElseIf .FormFields.Count = 0 And .Bookmarks.Count > 0 Then
>> >> Set GetCurrentFF = ActiveDocument.FormFields _
>> >> (.Bookmarks(.Bookmarks.Count).Name)
>> >> End If
>> >> End With
>> >> End Function
>> >>
>> >>
>> >> --
>> >> Greg Maxey/Word MVP
>> >> See:
>> >> http://gregmaxey.mvps.org/word_tips.htm
>> >> For some helpful tips using Word.
>> >>
>> >>
>> >> ah wrote:
>> >>> Hi;
>> >>>
>> >>> Appreciate if someone could help me with the following:
>> >>>
>> >>> I've 2 text fields called "Bank Name" & "Bank Account Number" in my
>> >>> form now. Can anyone guide me on how to add the the following
>> >>> validation checking into my form?
>> >>>
>> >>> a) When Bank A is being selected, in the Bank Name field, check
>> >>> whether the user has key in 10 digits into the Bank Account Number
>> >>> field. If the user has key in something > or < than 10 digits,
>> >>> prompt out an error message that ask them to key in the correct
>> >>> bank account number
>> >>> b) When Bank B is being selected, check whether the user has key in
>> >>> 12 digits into the Bank Account Number field.
>> >>> and etc
>>
>>
>>


Top