form question

A

ah

Hi;

Appreciate if someone could help me with the following:

An user form with the list box selection list will be prompted when I click
on the "Bank" field. The bank selection list differs when I select different
country. However, there are some countries that do not have the bank
associated with it, and I want the user to key in the bank name manually.
Please advice whether there is a way for the word to prompt out a list box
when I select the country called "Malaysia", "Singapore" and "UK", and prompt
out a text box for the user key in their bank name manually when I select a
country called " Taiwan", "China" and etc.

Or else, if I add in another text box into the user form, can the bank field
being configured to capture the text box info when the user key in the bank
detail into the text box?

Below is the code that is currently in place when I click on the "OK" button
now:

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


Note: BankName is the name given to the list box, while BankName1 is the
name given to the text box. Is there a way to configure the form to either
capture the BankName or BankName1 info into the Bank field?
 
G

Greg Maxey

Ah,

Why don't you use a ComboBox instead of a Listbox. Populate the ComboBox
with the various back names and a prompt "Enter bank name here." If the
user selects a country without an associated bank in your list then in the
UserForm initalize event have that combobox display the prompt.
 
A

ah

Hi;

Thanks for your prompt response.
Can you guide me on how to change the code below to accomodate this?

Private Sub UserForm_Initialize()
Dim myArray1() As String 'AUSTRALIA / NEW ZEALAND
Dim myArray2() As String 'BRAZIL
Dim myArray3() As String 'CANADA
Dim myArray4() As String 'CCM
Dim myArray5() As String 'CHINA
Dim myArray6() As String 'FRANCE
Dim myArray7() As String 'GERMANY
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 myArray13() As String 'LACC
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 myArray19() As String 'UNITED KINGDOM
Dim myArray20() As String 'UNITED STATES
Dim myArray21() As String 'VIETNAM


Dim i As Long

myArray1 = Split("NA") ' Need to key in bank manually

myArray2 = Split("NA") ' Need to key in bank manually

myArray3 = Split("NA")

myArray4 = Split("NA")

myArray5 = Split("NA")

myArray6 = Split("NA")

myArray7 = 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)")

myArray13 = Split("NA")

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")

myArray19 = Split("NA")

myArray20 = Split("NA")

myArray21 = Split("NA")

Me.BankName.Clear

Select Case ActiveDocument.FormFields("Country_Name").Result
Case "AUSTRALIA / NEW ZEALAND"
Me.BankName.List = myArray1
Case "BRAZIL"
Me.BankName.List = myArray2
Case "CANADA"
Me.BankName.List = myArray3
Case "CCM"
Me.BankName.List = myArray4
Case "CHINA"
Me.BankName.List = myArray5
Case "FRANCE"
Me.BankName.List = myArray6
Case "GERMANY"
Me.BankName.List = myArray7
Case "HONG KONG"
Me.BankName.List = myArray8
Case "INDIA/SRI LANGKA/BANGLADESH"
Me.BankName.List = myArray9
Case "INDONESIA"
Me.BankName.List = myArray10
Case "JAPAN"
Me.BankName.List = myArray11
Case "KOREA"
Me.BankName.List = myArray12
Case "LACC"
Me.BankName.List = myArray13
Case "MALAYSIA"
Me.BankName.List = myArray14
Case "PHILIPPINES"
Me.BankName.List = myArray15
Case "SINGAPORE"
Me.BankName.List = myArray16
Case "TAIWAN"
Me.BankName1.Text = ""
Case "THAILAND"
Me.BankName.List = myArray18
Case "UNITED KINGDOM"
Me.BankName.List = myArray19
Case "UNITED STATES"
Me.BankName.List = myArray20
Case "VIETNAM"
Me.BankName.List = myArray21




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
 
G

Greg Maxey

Ah,

You don't need all those arrays to hold the single value "NA".

Replace your existing ListBox with a combobox of the same name. See the
code for Case TAIWAN:

Private Sub UserForm_Initialize()
'Dim myArray1() As String 'AUSTRALIA / NEW ZEALAND
'Dim myArray2() As String 'BRAZIL
'Dim myArray3() As String 'CANADA
'Dim myArray4() As String 'CCM
'Dim myArray5() As String 'CHINA
'Dim myArray6() As String 'FRANCE
'Dim myArray7() As String 'GERMANY
'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 myArray13() As String 'LACC
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 myArray19() As String 'UNITED KINGDOM
Dim myArray20() As String 'UNITED STATES
Dim myArray21() As String 'VIETNAM

Dim i As Long
'myArray1 = Split("NA") ' Need to key in bank manually
'myArray2 = Split("NA") ' Need to key in bank manually
'myArray3 = Split("NA")
'myArray4 = Split("NA")
'myArray5 = Split("NA")
'myArray6 = Split("NA")
'myArray7 = 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)")
'myArray13 = Split("NA")
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")
'myArray19 = Split("NA")
'myArray20 = Split("NA")
'myArray21 = Split("NA")
With Me.BankName
.Clear
Select Case ActiveDocument.FormFields("Country_Name").Result
Case "AUSTRALIA / NEW ZEALAND"
.AddItem "NA"
Case "BRAZIL"
.AddItem "NA"
Case "CANADA"
.AddItem "NA"
Case "CCM"
.AddItem "NA"
Case "CHINA"
.AddItem "NA"
Case "FRANCE"
.AddItem "NA"
Case "GERMANY"
.AddItem "NA"
Case "HONG KONG"
.AddItem "NA"
Case "INDIA/SRI LANGKA/BANGLADESH"
.AddItem "NA"
Case "INDONESIA"
.AddItem "NA"
Case "JAPAN"
.AddItem "NA"
Case "KOREA"
.List = myArray12
Case "LACC"
.AddItem "NA"
Case "MALAYSIA"
.List = myArray14
Case "PHILIPPINES"
.AddItem "NA"
Case "SINGAPORE"
.List = myArray16
Case "TAIWAN"
.AddItem "Type your bank name here"
.AddItem "NA"
.ListIndex = 0
.SetFocus
.SelStart = 0
.SelLength = Len(.Text)
Case "THAILAND"
.AddItem "NA"
Case "UNITED KINGDOM"
.AddItem "NA"
Case "UNITED STATES"
.AddItem "NA"
Case "VIETNAM"
.AddItem "NA"
End Select
End With
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Dynamic Naming of Form Button 3
Link a text box based on drop down box with four options MS Word 365 0
Generated text copies into another template 0
Template with fields and premade texts.. 0
Dependent validation for a contact directory 4
Unable to display values in 2nd list box from 1st list box selection 2
Create a new set of data from 2 data sets 13
ASK Field Formatting 1

Top