Solved: Conditional Drop downs and autofilled text fields

[PKisielewski]

I used the code for a previous question about conditional drop downs. Works great and I am using it but I now have come across a new constraint and I need to have more that 25 items in my list. The form fields combo box only allows 25 items.
The conditional drop downs were that if you select from the list in field A you would get a list that corresponded with that selection in field B. For each selection in A you would have a different list populated in field B. Also, I am not using an userform.
Any help or suggestions would be great.
Thanks in advance.

[PKisielewski]

In my previous message I forgot to mention that I would like to incorporate the code that used the form fields and change it to use the ActiveX combo box. Is that possible?

[lucas]

could you post the file your are working with at present?

[PKisielewski]

This is the code that I copied from a message dated 6-14-06 from another individual. It will show you what code I am using. My question is that I want to do the same thing that this code does but I want to use the Control Toolbox combo boxes. (ActiveX). Thanks.


Option Explicit
Sub CountryExit()
Dim DECities()
Dim UKCities()
Dim NetworkArray()
Dim var
Dim i As Integer
DECities = Array("Frankfurt", "Berlin")
UKCities = Array("London", "Manchester")
NetworkArray = Array("Network1", "Network2", "Network3", "Network4", "Network5", _
"Network6")

Select Case ActiveDocument.FormFields("Country").DropDown.Value
Case 1
ActiveDocument.FormFields("City").DropDown.ListEntries.Clear
For var = 0 To UBound(DECities)
ActiveDocument.FormFields("City").DropDown.ListEntries.Add _
Name:=DECities(i)
i = i + 1
Next
ActiveDocument.FormFields("City").DropDown.Value = 1

Case 2
ActiveDocument.FormFields("City").DropDown.ListEntries.Clear
For var = 0 To UBound(UKCities)
ActiveDocument.FormFields("City").DropDown.ListEntries.Add _
Name:=UKCities(i)
i = i + 1
Next
ActiveDocument.FormFields("City").DropDown.Value = 1
End Select

i = 0
ActiveDocument.FormFields("Network").DropDown.ListEntries.Clear

For var = 0 To UBound(NetworkArray)
ActiveDocument.FormFields("Network").DropDown.ListEntries.Add _
Name:=NetworkArray(i)
i = i + 1
Next
ActiveDocument.FormFields("Network").DropDown.Value = 1
End Sub

Sub CityExit()
Dim FrankfurtRouters()
Dim BerlinRouters()
Dim LondonRouters()
Dim ManchesterRouters()
Dim DDValue As Integer
Dim i As Integer
Dim var
FrankfurtRouters = Array("DFR1", "DFR2")
BerlinRouters = Array("DBE1", "DBE2")
LondonRouters = Array("ULO1", "ULO2")
ManchesterRouters = Array("UMA1", "UMA2")
DDValue = ActiveDocument.FormFields("City").DropDown.Value

Select Case ActiveDocument.FormFields("City").DropDown.ListEntries. _
Item(DDValue).Name
Case "Frankfurt"
ActiveDocument.FormFields("Routers").DropDown.ListEntries.Clear
For var = 0 To UBound(FrankfurtRouters)
ActiveDocument.FormFields("Routers").DropDown.ListEntries.Add _
Name:=FrankfurtRouters(i)
i = i + 1
Next
ActiveDocument.FormFields("Routers").DropDown.Value = 1

Case "Berlin"
ActiveDocument.FormFields("Routers").DropDown.ListEntries.Clear
For var = 0 To UBound(BerlinRouters)
ActiveDocument.FormFields("Routers").DropDown.ListEntries.Add _
Name:=BerlinRouters(i)
i = i + 1
Next
ActiveDocument.FormFields("Routers").DropDown.Value = 1

Case "London"
ActiveDocument.FormFields("Routers").DropDown.ListEntries.Clear
For var = 0 To UBound(LondonRouters)
ActiveDocument.FormFields("Routers").DropDown.ListEntries.Add _
Name:=LondonRouters(i)
i = i + 1
Next
ActiveDocument.FormFields("Routers").DropDown.Value = 1

Case "Manchester"
ActiveDocument.FormFields("Routers").DropDown.ListEntries.Clear
For var = 0 To UBound(ManchesterRouters)
ActiveDocument.FormFields("Routers").DropDown.ListEntries.Add _
Name:=ManchesterRouters(i)
i = i + 1
Next
ActiveDocument.FormFields("Routers").DropDown.Value = 1
End Select
End Sub
Sub NetworkExit()
Dim Frankfurt_1_IP()
Dim Frankfurt_2_IP()
Dim Berlin_1_IP()
Dim Berlin_2_IP()
Dim London_1_IP()
Dim London_2_IP()
Dim Manchester_1_IP()
Dim Manchester_2_IP()
Dim DDNetworkValue As Integer
Dim DDCityValue As String
Dim NetworkLastDigit As String


Frankfurt_1_IP = Array("1.0.0.1", "1.0.1.1", "1.0.2.1", "1.0.3.1", "1.0.4.1", _
"1.0.5.1")
Frankfurt_2_IP = Array("1.0.0.2", "1.0.1.2", "1.0.2.2", "1.0.3.2", "1.0.4.2", _
"1.0.5.2")
Berlin_1_IP = Array("1.0.0.3", "1.0.1.3", "1.0.2.3", "1.0.3.3", "1.0.4.3", _
"1.0.5.3")
Berlin_2_IP = Array("1.0.0.4", "1.0.1.4", "1.0.2.4", "1.0.3.4", "1.0.4.4", _
"1.0.5.4")
London_1_IP = Array("1.0.0.5", "1.0.1.5", "1.0.2.5", "1.0.3.5", "1.0.4.5", _
"1.0.5.5")
London_2_IP = Array("1.0.0.6", "1.0.1.6", "1.0.2.6", "1.0.3.6", "1.0.4.6", _
"1.0.5.6")
Manchester_1_IP = Array("1.0.0.7", "1.0.1.7", "1.0.2.7", "1.0.3.7", "1.0.4.7", _
"1.0.5.7")
Manchester_2_IP = Array("1.0.0.8", "1.0.1.8", "1.0.2.8", "1.0.3.8", "1.0.4.8", _
"1.0.5.8")

DDNetworkValue = ActiveDocument.FormFields("Network").DropDown.Value
NetworkLastDigit = Right(ActiveDocument.FormFields("Network").DropDown. _
ListEntries.Item(DDNetworkValue).Name, 1)
DDCityValue = ActiveDocument.FormFields("City").DropDown.Value

Select Case ActiveDocument.FormFields("City").DropDown.ListEntries. _
Item(CInt(DDCityValue)).Name
Case "Frankfurt"
Select Case ActiveDocument.FormFields("Routers").Result
Case "DFR1"
ActiveDocument.FormFields("IPAddress").Result = _
Frankfurt_1_IP(CInt(NetworkLastDigit) - 1)
Case "DFR2"
ActiveDocument.FormFields("IPAddress").Result = _
Frankfurt_2_IP(CInt(NetworkLastDigit) - 1)
End Select

Case "Berlin"
Select Case ActiveDocument.FormFields("Routers").Result
Case "DBE1"
ActiveDocument.FormFields("IPAddress").Result = _
Berlin_1_IP(CInt(NetworkLastDigit) - 1)
Case "DBE2"
ActiveDocument.FormFields("IPAddress").Result = _
Berlin_2_IP(CInt(NetworkLastDigit) - 1)
End Select

Case "London"
Select Case ActiveDocument.FormFields("Routers").Result
Case "ULO1"
ActiveDocument.FormFields("IPAddress").Result = _
London_1_IP(CInt(NetworkLastDigit) - 1)
Case "ULO2"
ActiveDocument.FormFields("IPAddress").Result = _
London_2_IP(CInt(NetworkLastDigit) - 1)
End Select
Case "Manchester"
Select Case ActiveDocument.FormFields("Routers").Result
Case "UMA1"
ActiveDocument.FormFields("IPAddress").Result = _
Manchester_1_IP(CInt(NetworkLastDigit) - 1)
Case "UMA2"
ActiveDocument.FormFields("IPAddress").Result = _
Manchester_2_IP(CInt(NetworkLastDigit) - 1)
End Select
End Select
End Sub

Edited 29-Aug-06 by geekgirlau. Reason: insert vba tags and line breaks

[lucas]

could you post the file? zipped up please

[PKisielewski]

Okay, I was afraid that you were going to ask that. I did not know how but now I do.
Thanks.

[lucas]

I hate to ask but could you post this file so we can have a starting point?


I used the code for a previous question about conditional drop downs. Works great and I am using it but I now have come across a new constraint and I need to have more that 25 items in my list.

[PKisielewski]

I hope that I have answered your question - The following quote is from Dragon576 and fumei worked on this issue.


Solved: Conditional Drop downs and autofilled text fields

Hi,
Having not touched VBA for a few years, I need a bit of help on something.

I am trying to do the following. Conditional drop down lists where you first select the Country, then the town and then a router and a Network. I want to have an IP Address field populated automatically based on the selection in the conditional dropdown lists.

Based on Fumei's conditional2 example and a fillform example I have got a similar working example.

As I have a lot of data (8 countries, each with two towns, each town has two routers, and each router is connected to six identical networks, but have an unique IP address), I'm not sure if this will be the best way forward due to the amount of cases required.

Also as the first unique identifier is the name of the router, I believe I would need either an If...and type statement, or varible lists per network, per router, but I have reached the limit of my knowledge and time, so any help would be appreciated.



Actually, let me explain all that I am looking for is a total of 2 fields. You select one of the items in field A and depending on what is in A the list in B will change, but since the form fields can only hold 25 items for lists I need to find a way to use the ActiveX combo boxes.

I thought that fumei would see my question and since they worked on this issue before they would understand it. Thanks for your time and help and I hope that you can understand what I am asking for more clearly.

[PKisielewski]

Attached is the file from fumei.
Thanks.

[PKisielewski]

Sorry I am having trouble with my zip progam. Here is the file from fumei.

[lucas]

If your looking for dependent comboboxes.....Hope I understand what your looking for...try this and let us know if its in line with your question:

[PKisielewski]

Thanks so much for your message.
I reviewed the file and the first field is okay and it has a list but I need to have the second combo box to populate a list of items when the corresponding item is selected in field 1. For instance in combobox 1 a list contains - list: Dept RD, Dept Plant, Dept Surg,.... and if I select in the first combobox Dept RD then the second combobox contains a list for the user to select RD 1 or RD2 or RD3..... and if I select in the first combobox Dept Plant then the second combobox populates a completely new list with Plant 1, Plant 2.... and so on.
Hopefully I have explained it more clearly and please let me know if you need any more clarification.
Thanks.

[lucas]

Try this one. I did only the first one for you, if you run into problems let us know.

[PKisielewski]

Thanks so much - It works and it is easier than I thought. I really appreciate all of your time that you spent.I thought that is would more complicated. Just to let you know I added a ComboBox2.Clear because everytime I click on the dropdown in Combobox 1 it would duplicate the list. Again thanks, and there are many others that thank you also.
Hope to talk to again soon.

[lucas]

Glad we finally got it worked out. Don't forget to mark your thread solved.

ps. combobox2.clear......good idea....it wasn't a finished product when I turned it over to you...just a starting point. You might post your finished product here when you get it sorted.

[fumei]

Hi there. ALWAYS use .Clear to empty any control that is being repopulated.

[PKisielewski]

Thanks again.

[lucas]

Glad to be able to help....
ps. You can mark your own thread solved using the thread tools at the top of the page.....I will mark this one solved for you.