Macro for combo/drop down list

[ah]

Hi;

Need your help urgently.
I'm in the midst of developing a form. Can anyone help me with the following:

a) I wish to put the following into my form:
- Country
- Personnel Area
- Employee Group
-Employee Subgroup and etc

FYI, the above needs to have >25 selection list.
I wish to have different personnel area, employee group and employee
subgroup selection list when I select different country.Thus, it is going to
have different personnel area associated with the country that I select.

b) I tried to do the macro just now. However, I only managed to get the drop
down list mapped to the country field. By right, I need to use a combo box in
stead of a drop down list for personnel area, employee group and employee
subgroup, right?

c) Since there are so many fields in the form, does it mean that I need to
create multiple form in the Microsoft Visual Basic. I tried to map the
country with the personnel area list just now. Since there are 2 fields
involved, I've created 2 forms. Please advice whether I am doing the correct
things.

Below is the macro that I've put into the form. Appreciate if you could
guide me on this:

Form 1:
--------
Private Sub ComboBox1_Change()

ActiveDocument.FormFields("Country").Result = ComboBox1.Value

End Sub

----------------------------------------------------------------------

Private Sub UserForm_Initialize()

ComboBox1.ColumnCount = 1

'Load data into ComboBox

ComboBox1.List() = Array("AP", "EMEA", "USA")


End Sub
-----------------------------------------------------------


Form 2
--------

Private Sub ComboBox2_Change()

ActiveDocument.FormFields("PersonnelArea").Result = ComboBox2.Value

End Sub

--------------------------------------

Private Sub UserForm_Initialize()

ComboBox2.ColumnCount = 1

'Load data into ComboBox

ComboBox2.List() = Array("BER3 (DE03-Berlin )", "DIE3 (DE03-Dietzenbach
)", "DUS5 (DE05-Dusseldorf )", "FLE3 (DE03-Flensburg )")


End Sub

Macro:
-------



Private Sub ComboBox2_Change()

ActiveDocument.FormFields("PersonnelArea").Result = ComboBox2.Value

End Sub



Private Sub UserForm_Initialize()

ComboBox2.ColumnCount = 1

'Load data into ComboBox

ComboBox2.List() = Array("BER3 (DE03-Berlin )", "DIE3 (DE03-Dietzenbach
)", "DUS5 (DE05-Dusseldorf )", "FLE3 (DE03-Flensburg )")




End Sub

 

[ah]

Besides, for the country field, I can only select 1 exit macro. In the event
if all the other fields (personnel arae, employee group, employee subgroup)
will be listed depends on the country that I've selected, how am I going to
do this?How to select multiple macro for 1 field? Or do you have any other
solution for this? Please advice.

 

[Graham Mayor]

This sounds like Greg Maxey's sphere of interest - start at
http://gregmaxey.mvps.org/Linked_DropDown_Fields.htm

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[ah]

Hi;
Thanks for your kind reply.

however, I got the following error when I tried with the example given:

can't execute code in break mode

FYI, I've performed the following steps just now, please advice whether I'm
doing it right:

a) Create a drop down list which indicate the country (AP, EMEA, USA)
b) Create another drop down list for the personnel area, and no values are
being populated
c) Create a macro as follows:

Sub OnExitDDListA()
Dim oDD As DropDown
Set oDD = ActiveDocument.FormFields("PersonnelArea").DropDown
'Clear previous list
oDD.ListEntries.Clear
'Repopulate list based on user selection
Select Case ActiveDocument.FormFields("Country").Result
Case "AP"
With oDD.ListEntries
.Add "Apples"
.Add "Apricots"
.Add "Artichokes"
End With
Case "EMEA"
With oDD.ListEntries
.Add "Blueberries"
.Add "Beets"
.Add "Brocolli"
End With
Case "USA"
With oDD.ListEntries
.Add "Cherries"
.Add "Celery"
.Add "Cilantro"
End With
End Select
End Sub

d) Select an exit macro(OnExitDDListA) for the country field
e) Protect the file
f) Select the country
g) Click on the Personnel Area drop down button and the following error was
prompted to me

can't execute code in break mode

FYI, the system highlights the following code when the error occurs:
Set oDD = ActiveDocument.FormFields("PersonnelArea").DropDown

Thanks

 

[Graham Mayor]

vba will go into break mode if you run code containing an error. Reset and
run the code from the vba editor and see which line is highlighted.
If you have any problems with Greg's code, contact him via the feedback link
on his site.

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[ah]

Hi;

Thanks al lot.
Just a query, since I have various field that needs to be populated based on
the Country value that I've selected and I can only select 1 exit macro for
the country field, can you guide me on how to go about this?

I managed to populate the personnel area value based on the country name
that I've selected now. Please advice how can I do the same for the other
field, for example employee group. The value appears for the employee group
depends on the country name that i've selected

Below is the code that is in place for the user form now:

Private Sub StateProv_Click()

End Sub

Private Sub UserForm_Initialize()
Dim myArray1() As String
Dim myArray2() As String
Dim myArray3() As String
Dim i As Long
myArray1 = Split("AHE1(IN01-Ahmedabad) BAN1IN01-Bangalore BAN2IN02-Bangalore
BAN4IN04-Bangalore ")
myArray2 = Split("BER3(DE03-Berlin) DIE3DE03-Dietzenbach DUS5DE05-Dusseldorf
FLE3DE03-Flensburg FRA5DE05-Frankfurt NEU3DE03-Neubiberg
NIE3DE03-Niederkassel TAU3DE03-Taunusstein" _
& " AdditionalList" _
& " AdditionalList1")
myArray3 = Split("AAKR0(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)" _
& " AdditionalList" _
& " AdditionalList1")
Me.ListBox1.Clear
Select Case ActiveDocument.FormFields("Country_Name").Result
Case "AP"
Me.ListBox1.List = myArray1
Case "EMEA"
Me.ListBox1.List = myArray2
Case "USA"
Me.ListBox1.List = myArray3
End Select
End Sub

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


Please advice how to add in the other code for another field that needs to
link back to the same country name field as I can only select 1 exit macro
for the country field.

Thanks in advance.

 

[Greg Maxey]

If you are going to the trouble to create a UserForm (well worth the
trouble) then why bother with the protected form at all? Why not do
everything with the UserForm?

 

[ah]

Sorry, I'm totally new to this, and I've lost now.

FYI, I've created the form previously, but there is a limit of 25 selection
list if I use the drop down list. Besides, I'm not be able to have different
selection list based on the country that I've selected.

I followed the steps stated in your web page
(http://gregmaxey.mvps.org/Linked_DropDown_Fields.htm) and it works for 1 of
the field (personnel area) now. the personnel area listing was relected based
on the country that I've selected. However, I still have a few fields that
need to have the same setting as what i've done for the personnel area field.
For example, the employee group selection list should be associated with the
country that I selected as well.

Can you please help me with this?
Thanks in advance.

 

[Greg Maxey]

Just change the declaration of oDD to oDDs as FormFields and add statements
to your existing Select Case:

Sub OnExitDDListA()
Dim oDDs As FormFields
Set oDDs = ActiveDocument.FormFields
Select Case ActiveDocument.FormFields("Country").Result
Case "AP"
With oDDs("PersonnelArea").DropDown.ListEntries
.Clear
.Add "Apples"
.Add "Apricots"
.Add "Artichokes"
End With
With oDDs("Company").DropDown.ListEntries 'This is added for the
company dropdown.
.Clear
.Add "IBM"
.Add "Cisco"
.Add "Ford Motor Company"
End With
Case "EMEA"
With oDDs("PersonnelArea").DropDown.ListEntries
.Add "Blueberries"
.Add "Beets"
.Add "Brocolli"
End With
Case "USA"
With oDDs("PersonnelArea").DropDown.ListEntries
.Add "Cherries"
.Add "Celery"
.Add "Cilantro"
End With
End Select
End Sub

 

[ah]

Thanks a lot. It is working in my laptop now.
However, when I send the form to the user, I can't see the macro from the
user's PC.

Can you guide me to make the macro works once the user open the form?

Thanks a lot!

 

[ah]

For your information, I'm going to distribute the forms to our clients, which
are not residing within the same network. Is there a way to make the macro
available once the client open the form?

 

[Greg Maxey]

Now that you have your form working, you should probably repost your
question on distribution. I really don't know the best way. I do know that
unless your distribution is coordinated with your clients IT personnel that
you could have difficulties with user security settings. Good luck,

 

[ah]

Thanks a lot Greg. You're really helpful.
I've repost my question for the issue that I faced.