User form question

[ah]

Hi;

Can anyone help me wih the following:

- I've created a few fields in a form, for example: country, location and
employee group. In order for me to have different selection list for location
and employee group when I select different country, I've created a macro and
a user form for the location and employee group field.

Everything is working fine now. The only problem that I faced is that once I
click on the location selection list which is located at the 2nd page, the
location list will be prompted and bring me back to the 1st page(FYI, the
country field residing in the 1st page).

Is there any way for the user form selection list to be prmpted on the same
page in which the particular field reside? As it does not sounds logic for
the user to scroll to the correct page after they have make the selection in
the user form.

Thanks in advance

 

[ah]

Below is the macro that is currently in place:

Sub PersonnelAreaMacro()
PersonnelArea.Show
End Sub
----------------------------------------

User Form:

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("AUC1(NZ01-Auckland) BRI2(AU02-Brisbane)
CAN2(AU02-Canberra) MEL2(AU02-Melbourne) MEL3(AU03-Melbourne)
PER2(AU02-Perth) SYD2(AU02-Sydney) SYD3(AU03-Sydney) ")

myArray2 = Split("CUR2(BR02-Curitiba) JAG2(BR02-Jaguariuna)
SAO2(BR02-Sao-Paulo) SAO4(BR04-Sao-Paulo)")

myArray3 = Split("CAL2(CA02-Calgary) HAL2(CA02-Halifax)
MIS2(CA02-Mississaugua) MIS4(CA04-Mississaugua) MON2(CA02-Montreal)
MON3(CA03-Montreal) OTT2(CA02-Ottawa) SLA2(CA02-St.Laurent)
SLA4(CA04-St.Laurent) TOR2(CA02-Toronto)")

myArray4 = Split("AGD1(MA01-Agdal-Rabat) ALM1(KZ01-Almaty) " _
& "AMM1(AE01-Amman) ATH1(GR01-Athens) ATH2(GR02-Athens)
BRA1(SK01-Bratislava) BRN2(CZ02-Brno) BRT1(AE01-Beirut) BRU2(BE02-Brussels)
BRU3(BE03-Brussels) BUC1(RO01-Bucharest) BUD2(HU02-Budapest) CAI1(EG01-Cairo)
CAP2(ZA02-Cape-Town) CEN3(ZA03-Century-City) COC1(BO01-Cochabamba)
COR2(IE02-Cork) DBI1(AE01-Dubai) DID2(NL02-Didam) DID4(NL04-Didam)
DOH1(AE01-Doha) DUB2(IE02-Dublin) ESP1(FI01-Espoo) GLO2(DK02-Glostrup)
GOU2(NL02-Gouda) GUT1(GT01-Guatemala-City) HSH4(DK04-Horsholm)
ISL1(PK01-Islamabad) IST1(TR01-Istanbul) JOH2(ZA02-Johannesburg)
JOH3(ZA03-Johannesburg) KAR1(PK01-Karachi) KIE1(UA01-Kiev) KIE2(UA02-Kiev)
KIS2(SE02-Kista) KIS4(SE04-Kista) KRA1(PL01-Krakow) KRA2(PL02-Krakow)
KUW1(AE01-Kuwait) LAG1(NG01-Lagos) LAH1(PK01-Lahore)
LEV1(TR01-Levent-Istanbul) LIN4(SE04-Linkoping) LIS2(PT02-Lisbon)
MAD2(ES02-Madrid) MAD3(ES03-Madrid) MAD4(ES04-Madrid) MIL4(IT04-Milan)
MIN1(BY01-Minsk) MNO2(IT02-Milano) MOS2(RU02-Moscow) MOS3(RU03-Moscow)
MUS1(AE01-Muscat) NOE2(DK02-Noerresundby) " _
& "NUW2(NL02-Nieuwegein) OSL2(NO02-Oslo) POR2(PT02-Porto) PRA2(CZ02-Prague)
PRH1(CZ01-Praha) RIY1(AE01-Riyadh) ROM2(IT02-Rome) ROM3(IT03-Rome)
SAN1(CR01-San-Jose) SCL4(CH04-Schlieren) SDW2(ZA02-Sandown) SOF1(BG01-Sofia)
SOL2(SE02-Solna) STO5(SE05-Stockholm) STP2(RU02-St.Petersburg)
STP3(RU03-St.Petersburg) TAS1(UZ01-Tashkent) TBI1(GE01-TBILISI)
TEL0(IL00-Tel-Aviv) TEL4(IL04-Tel-Aviv) TEL5(IL05-Tel-Aviv) TRN2(IT02-Torino)
TRN3(IT03-Torino) UTR2(NL02-Utrecht) UTR4(NL04-Utrecht) VAN1(FI01-Vantaa)
VIB2(DK02-Viby) VIE3(AT03-Vienna) VIE5(AT05-Vienna) VIL1(LT01-Vilnius)
WAR2(PL02-Warsaw) ZAG1(HR01-Zagreb) ZAV2(BE02-Zaventem)
99LV(LV-Separated/Non-Employee) 99MA(MA-Separated/Non-Employee)
99SA(SA-Separated/Non-Employee) 99SI(SI-Separated/Non-Employee)")

myArray5 = Split("BEI2(CN02-Beijing) BEI3(CN03-Beijing) CHE2(CN02-Chengdu)
CHG2(CN02-Changsha) FUZ2(CN02-Fuzhou) GAO2(CN02-Gaoxin) GUA2(CN02-Guangzhou)
HAZ2(CN02-Hangzhou) HAZ3(CN03-Hangzhou) JIN2(CN02-Jinan) NAN2(CN02-Nanjing)
QUA2(CN02-Quanzhou) SHA2(CN02-Shanghai) SHE2(CN02-Shenzhen)
SHY2(CN02-Shenyang) TAY2(CN02-Taiyuan) TIA2(CN02-Tianjin) WUH2(CN02-Wuhan)
XIA2(CN02-Xian) ZHE2(CN02-Zhengzhou)")

myArray6 = Split("AN10(FR10-Antony1) IS11(FR11-Issy-Les-Moulineaux)
NIC3(FR03-Nice) NIC9(FR09-Nice) PAR3(FR03-Paris) REN8(FR08-Rennes)
TOU3(FR03-Toulouse)")

myArray7 = Split("BER3(DE03-Berlin) DIE3(DE03-Dietzenbach)
DUS5(DE05-Dusseldorf) FLE3(DE03-Flensburg) FRA5(DE05-Frankfurt)
NEU3(DE03-Neubiberg) NIE3(DE03-Niederkassel) TAU3(DE03-Taunusstein)")

myArray8 = Split("HKO3(HK03-HongKong) HKO4(HK04-HongKong)")

myArray9 = Split("AHE1(IN01-Ahmedabad) BAN1(IN01-Bangalore)
BAN2(IN02-Bangalore) BAN4(IN04-Bangalore) BAN7(IN07-Bangalore)
CHN1(IN01-Chennai) DHA1(BD01-Dhaka) GUR1(IN01-Gurgaon) HYD1(IN01-Hyderabad)
HYD2(IN02-Hyderabad) KOL1(IN01-Kolkata) MUM1(IN01-Mumbai) MUM2(IN02-Mumbai)
PUN1(IN01-Pune) RAJ2(LK02-Rajagiriya)")

myArray10 = Split("JAK1(ID01-Jakarta)")

myArray11 = Split("KAW3(JP03-Kawasaki) OSA3(JP03-Osaka) OSA6(JP06-Osaka)
TOK3(JP03-Tokyo) TOK6(JP06-Tokyo) YOK3(JP03-Yokohama)")

myArray12 = Split("SEO1(KR01-Seoul) SNG1(KR01-Sungnam)")

myArray13 = Split("BOG1(CO01-Bogota) BUE1(AR01-Buenos-Aires)
BUE2(AR02-Buenos-Aires) CCS1(VE01-Caracas) CRD1(AR01-Cordoba)
ECU1(EC01-Ecuador) ELS1(SV01-El-Salvador) HAT1(PR01-Hatorey)
JUA6(MX06-Juarez) LIM1(PE01-Lima) MEX3(MX03-Mexico-City) MOY3(MX03-Monterrey)
NOG4(MX04-Nogales) REY7(MX07-Reynosa) STA1(CL01-Santiago)
99DO(DO-Separated/Non-Employee) 99EC(EC-Separated/Non-Employee)
99SV(SV-Separated/Non-Employee)")

myArray14 = Split("CYB2(MY02-Cyberjaya) CYB5(MY05-Cyberjaya)
PEN2(MY02-Penang) PEN3(MY03-Penang) PEN5(MY05-Penang)
PET2(MY02-Petaling-Jaya) PET3(MY03-Petaling-Jaya) PET5(MY05-Petaling-Jaya)")

myArray15 = Split("MAK1(PH01-Makati-City)")

myArray16 = Split("SIN2(SG02-Singapore) SIN3(SG03-Singapore)
SIN5(SG05-Singapore) SIN6(SG06-Singapore)")

myArray17 = Split("TAC1(TW01-Taipei-County) TAI1(TW01-Taipei)")

myArray18 = Split("BKK1(TH01-Bangkok) PAT1(TH01-Pattanakarn)
RAN1(TH01-Rangsit) SAL1(TH01-Salaya) SRI1(TH01-Sriracha)")

myArray19 = Split("ASH1(GB01-Ashburton) ASH3(GB03-Ashburton)
BAS1(GB01-Basingstoke) BAS4(GB04-Basingstoke) BAS5(GB05-Basingstoke)
BRM1(GB01-Birmingham) LIV1(GB01-Livingston) LOU1(GB01-Loughborough)
MBO4(GB04-Melbourn) SWI1(GB01-Swindon) WIN1(GB01-Winnersh)
WOK1(GB01-Wokingham)")

myArray20 = Split("AKR0(M000-Akron) ALB0(M000-Albuquerque)
ALP0(M000-Alpharetta) ANA0(M000-Anaheim)" _
& "AND0(M000-Andover) ARL0(M000-Arlington-Heights) ATL0(M000-Atlanta)
AUS0(M000-Austin) BAT0(M000-Baton-Rouge) BED0(M000-Bedminster)
BEN0(M000-Bentonville) BIR0(M000-Birmingham) BOU0(M000-Boulder)
BOX0(M000-Boxborough) BRK0(M000-Brookfield) BRO0(M000-Brownsville) " _
& "BUR0(M000-Burnsville) CAR0(M000-Carrollton) CHI0(M000-Chicago)
CHL0(M000-Chandler) CHM0(M000-Champaign) CHR0(M000-Charlotte)
CHS0(M000-Chesterfield) CLB0(M000-Columbus) CMA0(M000-ColumbiaSC)
CMR0(M000-Colmar) COL0(M000-ColumbiaMD) COS0(M000-Costa-Mesa)
DAL0(M000-Dallas) DEA0(M000-Dearborn) DEE0(M000-Deer-Park)
EDE0(M000-Eden-Praire) ELG0(M000-Elgin) ELM0(M000-Elma) ELP0(M000-El-Paso)
ENG0(M000-Englewood) FAI0(M000-Fairport) FAR0(M000-Farmington-Hills)
FKL0(M000-Franklin) FRM0(M000-Framingham) FTW0(M000-Fort-Worth)
GLE0(M000-Glen-Rock) HAC0(M000-Hackensack) HAR0(M000-Harrisburg)
HAU0(M000-Hauppauge) HAV0(M000-Hanover) HOL0(M000-Holtsville) " _
& "HON0(M000-Honolulu) HOR0(M000-Horsham " _
& "HOU0(M000-Houston) IND0(M000-Indianapolis) IRV0(M000-Irvine)
LAK0(M000-Lake-Mary) LAN0(M000-Lanham) LAU0(M000-Laurel)
LAW0(M000-Lawrenceville) LEX0(M000-Lexington) LIB0(M000-Libertyville)
LIT0(M000-Little-Rock) LKO0(M000-Lake-Oswego) LON0(M000-Longwood)
LOS0(M000-Los-Angeles) LSV0(M000-Louisville) MAR0(M000-Marlborough)
MCA0(M000-McAllen) MCL0(M000-McLean) MEM0(M000-Memphis) MIA0(M000-Miami)
NEW0(M000-Newington) NOR0(M000-Northbrook) NYO0(M000-NewYork)
OAK0(M000-Oakland) ORL0(M000-Orlando) OVE0(M000-Overland-Park)
PIT0(M000-Pittsburgh) PLA0(M000-Plantation) PLY0(M000-Plymouth-Meeting)
PRM0(M000-Parma) RED0(M000-Redmond) RES0(M000-Reston) RIC0(M000-Richmond)
ROC0(M000-Rockville) ROH0(M000-Rohnert-Park) ROS0(M000-Rosemount)
SAC0(M000-Sacramento) SAJ0(M000-San-Jose) SCH0(M000-Schaumburg)
SDI0(M000-San-Diego) SEA0(M000-Seattle) SEG0(M000-Seguin) SHM0(M000-Shalimar)
SNR0(M000-Sunrise) SOU0(M000-Southborough) SPL0(M000-South-Plainfield)
STH0(M000-Southbury) STL0(M000-Salt-Lake-City) " _
& "SUN0(M000-Sunnyvale) TAM0(M000-Tamuning) TEM0(M000-Tempe)
TEW0(M000-Tewksbury) TLD0(M000-Toledo) TOL0(M000-Toluca-Lake)
TUC0(M000-Tucson) WAS0(M000-Washington) WES0(M000--Westerville)
WHE0(M000-Wheeling) WIL0(M000-Wilbur) WSC0(M000-Waseca)
WSN0(M000-West-Seneca)")


myArray21 = Split("HAN1(VN01-Hanoi) HOC1(VN01-Ho-Chi-Minh-City)")

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

End Select
End Sub

Private Sub OK_Click()
ActiveDocument.FormFields("PersonnelArea").Result = Me.PersonnelArea.Text
Unload Me
End Sub

Private Sub Reset_Click()


ActiveDocument.FormFields("PersonnelArea").Result = " "
Unload Me
End Sub

 

[Greg Maxey]

Ah,

Change the OK_Click macro to something like this:

Private Sub OK_Click()
ActiveDocument.FormFields("PersonnelArea").Result = Me.PersonnelArea.Text
ActiveDocument.Bookmarks("Text?").Range.Fields(1).Result.Select 'Where
"Text?" is the bookmark name of the field you want selected next after the
UF processes.
Unload Me
End Sub

 

[ah]

thanks a lot!
you're so helpful

Ah,

Change the OK_Click macro to something like this:

Private Sub OK_Click()
ActiveDocument.FormFields("PersonnelArea").Result = Me.PersonnelArea.Text
ActiveDocument.Bookmarks("Text?").Range.Fields(1).Result.Select 'Where
"Text?" is the bookmark name of the field you want selected next after the
UF processes.
Unload Me
End Sub




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