Solved: Conditional Drop downs and autofilled text fields

[dragon576]

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.

I have two attachements (dummy data, and attempted example), but they are just bigger than the allowed attachement size, so I can send them via mail if needed.

Thanks

Doug

[fumei]

Hi Doug. You can send them to me and I will take a look.

my handle AT telus DOT net

Are they ZIP files? Hope so.

[dragon576]

Thanks, I've send the files through (zipped)

Doug

[fumei]

Got the files.

Here you go. Working conditional formfields.

Have to say...hmmm...perhaps Excel may be better for this. However, as you can see, it certainly is possible in Word.

The document is protected for forms so you can see it in action. There is no password.

I would be inclined - if you are doing this in Word - to have all the action in/on a Userform, rather than formfields. Simply because of the requirement of formfields (using OnExit) macros for the user to actually exit. They must Tab to the next formfield.

If this was on a Userform, not only could the user do everything on the form, but they could easily REDO it. Shrug. Well, it is not all that hard to redo it using formfields really. I don't know, the Tab thing is a little annoying, because they MUST Tab to change the result.

On a Userform you could fire the _Change event and immediately change the other boxes.

Whatever.

[fumei]

Oh, and I am not posting the code as it is a bit long. Essentially though, I am using Select Case logic statements, rather than If..Then.

[fumei]

Ooops...that is what you get for cutting and pasting....

Ahem. London (ULO2 router) loaded the incorrect array.
Manchester (both UMA1 and UMA2 routers) loaded the incorrect array.

Sorry about that. Attached file is correct.

[fumei]

Hmmm

[fumei]

Hmmmm. Since I do all that whining about screen width...ahem...I had better make my OWN code fit...shouldn't I?

[fumei]

So, what the heck...here is the code. It could be a bit shorter, but I built expandability into it. You want to add more cities - just add them to the City array. You want to add more routers - just add them to the Router array. You want to add more Networks - just add them to the Network array. You want to add more IP Adress - just add them to the IPAddress array. Note though that if you want to have different Networks for each router you would have to change the general load of the Network array. Right now, all the Network options are identical for each router - see comment in the CountryExit Sub.Option Explicit

Sub CountryExit()
' loads the City dropdown
' load the Network array dropdown, as it is
' the same for all routers
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()
' loads the routers dropdown based on City
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()
' fills the text IPAdress formfield based on
' City / Router / Network logic
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 SubGetting it to fit was trickier that I thought. Hmmmmm.

[fumei]

Further note: Select Case ActiveDocument.FormFields("Country"). _
DropDown.Value
Case 1 In the CountryExit sub the Select Case uses the Value of the Country dropdown. Note that the Value of a dropdown is NOT the text of the item.

The dropdown has two items: DE and UK. The Value is 1, or 2 - not "DE" or "UK" You can not get the text of a dropdown item except by explicitly getting the Item(index).Name.

For the other dropdown I DO get the text name. This is done with first getting the Value, then passing that back to the Item(index). As in: DDValue = ActiveDocument.FormFields("City").DropDown.Value

Select Case ActiveDocument.FormFields("City").DropDown. _
ListEntries.Item(DDValue).Name
Case "Frankfurt" Getting the Item(index) allows use of the text of the selected dropdown item. For the country dropdown, I did not do this. i just used the index - 1 or 2. if you wanted to expand the countries, you would probably like to use text. In which case - say you had "Germany", "Spain", "France", "UK" - in the CountryExit sub:Sub CountryExit()
' yadda yadda yadda
' other declarations
Dim DDCountryValue As Integer
' yadda yadda yadda
' other instructions
DDCountryValue = ActiveDocument.FormFields("Country"). _
DropDown.Value
Select Case ActiveDocument.FormFields("Country"). _
DropDown.ListEntries.Item(DDCountryValue).Name
Case "Germany"
' yadda yadda yadda
' instructions
Case "Spain"
' yadda yadda yadda
' instructions

' etc etc etc

[fumei]

Well....that was fun.

[fumei]

Oh....and one other thing...I notice the IP Address could, in fact, be derived. 1.0.0.1, 1.0.1.1, 1.0.2.1, 1.0.3.1 etc.

However, while the logic is certainly possible to code, it would take more string manipulation that I was willing to do right now.

[fumei]

I just can't leave this one alone.... Another improvement would be to use formfield objects. That way you can get rid of all those "ActiveDocument.Formfields("Blah").

Make Public object for the document and formfields.Option Explicit
Public ThisDoc As Word.Document
Public ffCountry As Word.FormField
Public ffCity As Word.FormField
Public ffRouter As Word.FormField
Public ffNetwork As Word.FormField
Public ffIPAddress As Word.FormField

' make a Sub to create the objects
Sub SetFormfieldObjects()
Set ThisDoc = ActiveDocument
Set ffCountry = ThisDoc.FormFields("Country")
Set ffCity = ThisDoc.FormFields("City")
Set ffRouter = ThisDoc.FormFields("Routers")
Set ffNetwork = ThisDoc.FormFields("Network")
Set ffIPAddress = ThisDoc.FormFields("IPAddress")
End Sub

' and a Sub to destroy them
Sub CleanUp()
Set ffCountry = Nothing
Set ffCity = Nothing
Set ffRouter = Nothing
Set ffNetwork = Nothing
Set ffIPAddress = Nothing
Set ThisDoc = Nothing
End Sub

' now you can call the creation routine in the CountryExit Sub' creates formfield objects
' loads the City dropdown
' load the Network array dropdown, as it is the same for all routers
' call the object creation routine
Call SetFormfieldObjects

' yadda yadda yadda other stuff
' now instead of ActiveDocument.Formfields("County")
' you can use ffCountry
Select Case ffCountry.DropDown.Value
Case 1
' and instead of ActiveDocument.Formfields("City")
' you can use ffCity
ffCity.DropDown.ListEntries.Clear
For var = 0 To UBound(DECities)
ffCity.DropDown. _
ListEntries.Add Name:=DECities(i)
i = i + 1
Next
ffCity.DropDown.Value = 1The same with all the other formfields' for example, instead of
ActiveDocument.FormFields("Routers").DropDown. _
ListEntries.Add Name:=BerlinRouters(i)

' you can use
ffRouter.Dropdown.ListEntries.Add _
Name:=BerlinRouters(i)You need to destroy the objects. So at the end of NetworkExit - where you fill the textbox with the IP Address: ' yadda yadda yadda other instructions
Case "Manchester"
Select Case ffRouter.Result
Case "UMA1"
ffIPAddress.Result = _
Manchester_1_IP(CInt(NetworkLastDigit) - 1)
Case "UMA2"
ffIPAddress.Result = _
Manchester_2_IP(CInt(NetworkLastDigit) - 1)
End Select
End Select
' call the object destroying routine
Call CleanUp
End Sub


OK...I'm done.

[fumei]

OK...maybe not. I thought I would upload the version using the formfield objects.

NOW I'm done.

[dragon576]

Wow.

I looked at the replies and thought a few other people had ideas, but I had no idea this would wet your creative appetite so much :yes

Many thanks for this, using objects looks a lot simpler and more efficient to update.

Do not read anything into the structure of the IP addresses, it was just simpler to change one number and copy and paste for the example, so there is no usable structure to derive the next one.

I have to say I never expected such a dedicated effort. It is very much appreciated.

:bow:

Thanks

Doug
</IMG></IMG>

[fumei]

Creative? No, not really. It is not any different than the conditional formfield demo you had. OK, a little different, but not much. A deeper level of logic, but the logic is only different as a quantity, essentially the HOW is pretty much the same.

I was just curious as to how fast I could put it together.

Still have to say that it COULD be done better. And that...hmmmm....this sort of stuff is more in Excel's backyard.

It could also be done with Fields. However, I am not a real fan of hugely nested IF statements. I get lost too easily. I much prefer Select Case where possible, and for that you need to do it in VBA.

Hope it helped. It should at least give a solid idea of what you CAN do.

As I stated...it was kind of fun. And yes - it is much better to use objects. More explicit, and in many ways (once you get the idea of objects) a LOT cleaner.