Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Macro for combo/drop down list
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 |
#2
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Macro for combo/drop down list
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. "ah" wrote: 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 |
#3
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Macro for combo/drop down list
This sounds like Greg Maxey's sphere of interest - start at
http://gregmaxey.mvps.org/Linked_DropDown_Fields.htm -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org ah wrote: 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 |
#4
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Macro for combo/drop down list
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").DropDow n '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").DropDow n Thanks "Graham Mayor" wrote: This sounds like Greg Maxey's sphere of interest - start at http://gregmaxey.mvps.org/Linked_DropDown_Fields.htm -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org ah wrote: 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 |
#5
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Macro for combo/drop down list
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 My web site www.gmayor.com Word MVP web site http://word.mvps.org ah wrote: 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").DropDow n '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").DropDow n Thanks "Graham Mayor" wrote: This sounds like Greg Maxey's sphere of interest - start at http://gregmaxey.mvps.org/Linked_DropDown_Fields.htm -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org ah wrote: 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 |
#6
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Macro for combo/drop down list
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. "Graham Mayor" wrote: 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 My web site www.gmayor.com Word MVP web site http://word.mvps.org ah wrote: 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").DropDow n '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").DropDow n Thanks "Graham Mayor" wrote: This sounds like Greg Maxey's sphere of interest - start at http://gregmaxey.mvps.org/Linked_DropDown_Fields.htm -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org ah wrote: 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 |
#7
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Macro for combo/drop down list
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? -- Greg Maxey/Word MVP See: http://gregmaxey.mvps.org/word_tips.htm For some helpful tips using Word. "ah" wrote in message ... 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. "Graham Mayor" wrote: 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 My web site www.gmayor.com Word MVP web site http://word.mvps.org ah wrote: 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").DropDow n '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").DropDow n Thanks "Graham Mayor" wrote: This sounds like Greg Maxey's sphere of interest - start at http://gregmaxey.mvps.org/Linked_DropDown_Fields.htm -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org ah wrote: 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 |
#8
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Macro for combo/drop down list
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" wrote: 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? -- Greg Maxey/Word MVP See: http://gregmaxey.mvps.org/word_tips.htm For some helpful tips using Word. "ah" wrote in message ... 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. "Graham Mayor" wrote: 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 My web site www.gmayor.com Word MVP web site http://word.mvps.org ah wrote: 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").DropDow n '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").DropDow n Thanks "Graham Mayor" wrote: This sounds like Greg Maxey's sphere of interest - start at http://gregmaxey.mvps.org/Linked_DropDown_Fields.htm -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org ah wrote: 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 |
#9
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Macro for combo/drop down list
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 -- Greg Maxey/Word MVP See: http://gregmaxey.mvps.org/word_tips.htm For some helpful tips using Word. ah wrote: 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" wrote: 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? -- Greg Maxey/Word MVP See: http://gregmaxey.mvps.org/word_tips.htm For some helpful tips using Word. "ah" wrote in message ... 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. "Graham Mayor" wrote: 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 My web site www.gmayor.com Word MVP web site http://word.mvps.org ah wrote: 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").DropDow n '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").DropDow n Thanks "Graham Mayor" wrote: This sounds like Greg Maxey's sphere of interest - start at http://gregmaxey.mvps.org/Linked_DropDown_Fields.htm -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org ah wrote: 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 |
#10
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Macro for combo/drop down list
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! "Greg Maxey" wrote: 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 -- Greg Maxey/Word MVP See: http://gregmaxey.mvps.org/word_tips.htm For some helpful tips using Word. ah wrote: 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" wrote: 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? -- Greg Maxey/Word MVP See: http://gregmaxey.mvps.org/word_tips.htm For some helpful tips using Word. "ah" wrote in message ... 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. "Graham Mayor" wrote: 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 My web site www.gmayor.com Word MVP web site http://word.mvps.org ah wrote: 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").DropDow n '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").DropDow n Thanks "Graham Mayor" wrote: This sounds like Greg Maxey's sphere of interest - start at http://gregmaxey.mvps.org/Linked_DropDown_Fields.htm -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org ah wrote: 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 |
#11
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Macro for combo/drop down list
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? "ah" wrote: 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! "Greg Maxey" wrote: 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 -- Greg Maxey/Word MVP See: http://gregmaxey.mvps.org/word_tips.htm For some helpful tips using Word. ah wrote: 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" wrote: 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? -- Greg Maxey/Word MVP See: http://gregmaxey.mvps.org/word_tips.htm For some helpful tips using Word. "ah" wrote in message ... 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. "Graham Mayor" wrote: 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 My web site www.gmayor.com Word MVP web site http://word.mvps.org ah wrote: 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").DropDow n '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").DropDow n Thanks "Graham Mayor" wrote: This sounds like Greg Maxey's sphere of interest - start at http://gregmaxey.mvps.org/Linked_DropDown_Fields.htm -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org ah wrote: 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 ----------------------------------------------------------- |
#12
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Macro for combo/drop down list
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, -- Greg Maxey/Word MVP See: http://gregmaxey.mvps.org/word_tips.htm For some helpful tips using Word. ah wrote: 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? "ah" wrote: 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! "Greg Maxey" wrote: 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 -- Greg Maxey/Word MVP See: http://gregmaxey.mvps.org/word_tips.htm For some helpful tips using Word. ah wrote: 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" wrote: 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? -- Greg Maxey/Word MVP See: http://gregmaxey.mvps.org/word_tips.htm For some helpful tips using Word. "ah" wrote in message ... 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. "Graham Mayor" wrote: 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 My web site www.gmayor.com Word MVP web site http://word.mvps.org ah wrote: 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").DropDow n '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").DropDow n Thanks "Graham Mayor" wrote: This sounds like Greg Maxey's sphere of interest - start at http://gregmaxey.mvps.org/Linked_DropDown_Fields.htm -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org ah wrote: 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 ----------------------------------------------------------- |
#13
Posted to microsoft.public.word.docmanagement
|
|||
|
|||
Macro for combo/drop down list
Thanks a lot Greg. You're really helpful.
I've repost my question for the issue that I faced. "Greg Maxey" wrote: 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, -- Greg Maxey/Word MVP See: http://gregmaxey.mvps.org/word_tips.htm For some helpful tips using Word. ah wrote: 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? "ah" wrote: 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! "Greg Maxey" wrote: 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 -- Greg Maxey/Word MVP See: http://gregmaxey.mvps.org/word_tips.htm For some helpful tips using Word. ah wrote: 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" wrote: 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? -- Greg Maxey/Word MVP See: http://gregmaxey.mvps.org/word_tips.htm For some helpful tips using Word. "ah" wrote in message ... 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. "Graham Mayor" wrote: 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 My web site www.gmayor.com Word MVP web site http://word.mvps.org ah wrote: 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").DropDow n '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").DropDow n Thanks "Graham Mayor" wrote: This sounds like Greg Maxey's sphere of interest - start at http://gregmaxey.mvps.org/Linked_DropDown_Fields.htm -- Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org ah wrote: 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: -------- |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to add items to combo box (drop down box)? | Microsoft Word Help | |||
Macro coding: Drop down list value display description in a text f | Microsoft Word Help | |||
Combo Box in a Userform for Images (Macro Help) | Microsoft Word Help | |||
Combo boxes and drop down lists- basic question | New Users | |||
Macro or drop down list ? | New Users |