MS Word Cascading Combo Box Help

[jpabz]

Hello,


Can anyone please help me with the code I am currently using. What I am trying to do is have a Combo box that will show different types of texts all over the document depending on what is chosen in the combo box...it seems to work fine whenever i run it in the VBA editor but when I close it and open the document it does not run. Your help is greatly appreciated! (Pretty new to VBA) Thank you!

ActivX - Combo Box


Sub ComboBox1AddItems()
ComboBox1.Clear
ComboBox1.AddItem "Route 1"
ComboBox1.AddItem "Route 2"
ComboBox1.AddItem "Route 3"
End Sub

Private Sub ComboBox1_Change()
If ComboBox1.Value = "Route 1" Then
TextBox1.Value = "Finance"
TextBox2.Value = "Director"
TextBox3.Value = "1st Signatory"
TextBox4.Value = "2nd Signatory"
TextBox5.Value = "N/A"
TextBox8.Value = " ROUTE SLIP"
ElseIf ComboBox1.Value = "Route 2" Then
TextBox1.Value = "AVP"
TextBox1.Value = "VP"
TextBox3.Value = "Insurance"
TextBox4.Value = "Revenue"
TextBox8.Value = "ROUTE SLIP"
Else
TextBox1.Value = "AVP"
TextBox2.Value = "Manager"
TextBox3.Value = "N/A"
TextBox4.Value = "N/A"
TextBox5.Value = "N/A"
TextBox8.Value = " Route SLIP"
End If
End Sub

[gmayor]

You need to change the name of the first macro so that it runs when the document is opened.
There's typo in

ElseIf ComboBox1.Value = "Route 2" Then
TextBox1.Value = "AVP"
TextBox1.Value = "VP"
TextBox3.Value = "Insurance"
TextBox4.Value = "Revenue"
TextBox8.Value = "ROUTE SLIP"
Else and as TextBox is defined twice and there is no value for TextBox5 so it will stay the same as it was previously. I have made some small changes, so that it works - though check TextBox5 value in the above sequence.


Option Explicit

Sub Document_Open()
ComboBox1.Clear
ComboBox1.AddItem "[Select Route]"
ComboBox1.AddItem "Route 1"
ComboBox1.AddItem "Route 2"
ComboBox1.AddItem "Route 3"
ComboBox1.ListIndex = 0

End Sub

Private Sub ComboBox1_Change()
If ComboBox1.Value = "Route 1" Then
TextBox1.Value = "Finance"
TextBox2.Value = "Director"
TextBox3.Value = "1st Signatory"
TextBox4.Value = "2nd Signatory"
TextBox5.Value = "N/A"
TextBox8.Value = " ROUTE SLIP"
ElseIf ComboBox1.Value = "Route 2" Then
TextBox1.Value = "AVP"
TextBox2.Value = "VP"
TextBox3.Value = "Insurance"
TextBox4.Value = "Revenue"
TextBox5.Value = "N/A"
TextBox8.Value = "ROUTE SLIP"
ElseIf ComboBox1.Value = "Route 3" Then
TextBox1.Value = "AVP"
TextBox2.Value = "Manager"
TextBox3.Value = "N/A"
TextBox4.Value = "N/A"
TextBox5.Value = "N/A"
TextBox8.Value = " Route SLIP"
Else
TextBox1.Value = ""
TextBox2.Value = ""
TextBox3.Value = ""
TextBox4.Value = ""
TextBox5.Value = ""
TextBox8.Value = ""
End If
End Sub

ActiveX text and combo boxes are old technology and probably best avoided. It would be more elegant to use a VBA Userform and bookmarked locations or DOCVARIABLE fields to display the data. See http://www.gmayor.com/Userform.htm

[jpabz]

Thanks a lot gmayor!

I also added a second combox that is dependent on the first combo box.


Private Sub ComboBox2_Change()
If ComboBox1.Value = "Route 1" Then
ComboBox2.List = Array("Test 1", "Test 3")
ComboBox2.Clear
ElseIf ComboBox1.Value = "Route 2" Then
ComboBox2.List = Array("Test 2", ""Test 4")
ComboBox2.Clear
Else
ComboBox2.List = Array("Test 6, "Test 7)
End If
End Sub

The code works but how do I clear combobox2 whenever a user changes combobox1?

Awesome! I will look into the userform!

[gmayor]

You don't need that macro. You need to modify the original
Option Explicit

Sub Document_Open()
ComboBox1.Clear
ComboBox2.Clear
ComboBox1.AddItem "[Select Route]"
ComboBox1.AddItem "Route 1"
ComboBox1.AddItem "Route 2"
ComboBox1.AddItem "Route 3"
ComboBox1.ListIndex = 0
End Sub

Private Sub ComboBox1_Change()
If ComboBox1.Value = "Route 1" Then
TextBox1.Value = "Finance"
TextBox2.Value = "Director"
TextBox3.Value = "1st Signatory"
TextBox4.Value = "2nd Signatory"
TextBox5.Value = "N/A"
TextBox8.Value = " ROUTE SLIP"
ComboBox2.List = Array("Test 1", "Test 3")
ElseIf ComboBox1.Value = "Route 2" Then
TextBox1.Value = "AVP"
TextBox2.Value = "VP"
TextBox3.Value = "Insurance"
TextBox4.Value = "Revenue"
TextBox5.Value = "N/A"
TextBox8.Value = "ROUTE SLIP"
ComboBox2.List = Array("Test 2", "Test 4")
ElseIf ComboBox1.Value = "Route 3" Then
TextBox1.Value = "AVP"
TextBox2.Value = "Manager"
TextBox3.Value = "N/A"
TextBox4.Value = "N/A"
TextBox5.Value = "N/A"
TextBox8.Value = " Route SLIP"
ComboBox2.List = Array("Test 6", "Test 7")
Else
TextBox1.Value = ""
TextBox2.Value = ""
TextBox3.Value = ""
TextBox4.Value = ""
TextBox5.Value = ""
TextBox8.Value = ""
ComboBox2.Clear
End If
End Sub

[jpabz]

So I created a Userform just like you said but for a different document. I created a submit button and cancel. And what I would like to do is if a user leaves a textbox blank, it will show a message. This is my current code.

It works only on one textbox..how would I add it to the other textboxes? Thanks again!


Private Sub CommandButton1_Click()
With ActiveDocument
If Me.TextBox1.Value = "" Then
MsgBox "Client Legal Name Required"
End If
'Ref#
.SelectContentControlsByTitle("Ref#").Item(1).Range.Text = Me.TextBox10
'Agreement Date
.SelectContentControlsByTitle("Day").Item(1).Range.Text = Me.TextBox11
.SelectContentControlsByTitle("Month").Item(1).Range.Text = Me.ComboBox4
.SelectContentControlsByTitle("Year").Item(1).Range.Text = Me.ComboBox5
'Expiry Date
.SelectContentControlsByTitle("Day2").Item(1).Range.Text = Me.TextBox12
.SelectContentControlsByTitle("Month2").Item(1).Range.Text = Me.ComboBox6
.SelectContentControlsByTitle("Year2").Item(1).Range.Text = Me.ComboBox7
'SAIT Information
.SelectContentControlsByTitle("SAITDepartment").Item(1).Range.Text = Me.ComboBox3
.SelectContentControlsByTitle("SAITContact").Item(1).Range.Text = Me.TextBox8
.SelectContentControlsByTitle("SAITContact2").Item(1).Range.Text = Me.TextBox20
.SelectContentControlsByTitle("SAITEmail").Item(1).Range.Text = Me.TextBox9
'Client Information
.SelectContentControlsByTitle("CompanyName").Item(1).Range.Text = Me.TextBox1
.SelectContentControlsByTitle("CompanyName2").Item(1).Range.Text = Me.TextBox1
.SelectContentControlsByTitle("CompanyName3").Item(1).Range.Text = Me.TextBox1
.SelectContentControlsByTitle("Address").Item(1).Range.Text = Me.TextBox2
.SelectContentControlsByTitle("City").Item(1).Range.Text = Me.TextBox3
.SelectContentControlsByTitle("City2").Item(1).Range.Text = Me.TextBox3
.SelectContentControlsByTitle("Province").Item(1).Range.Text = Me.ComboBox2
.SelectContentControlsByTitle("Province2").Item(1).Range.Text = Me.ComboBox2
.SelectContentControlsByTitle("Country1").Item(1).Range.Text = Me.ComboBox8
.SelectContentControlsByTitle("PostalCode").Item(1).Range.Text = Me.TextBox4
'Client Contact Information
.SelectContentControlsByTitle("ClientContact").Item(1).Range.Text = Me.TextBox5
.SelectContentControlsByTitle("ClientContact2").Item(1).Range.Text = Me.TextBox5
.SelectContentControlsByTitle("PositionTitle").Item(1).Range.Text = Me.TextBox6
.SelectContentControlsByTitle("PositionTitle2").Item(1).Range.Text = Me.TextBox6
.SelectContentControlsByTitle("Email").Item(1).Range.Text = Me.TextBox7
'TextBox1 - Compnay name repeat
.SelectContentControlsByTitle("CompanyName").Item(1).Range.Text = Me.TextBox1
.SelectContentControlsByTitle("CompanyName2").Item(1).Range.Text = Me.TextBox1
.SelectContentControlsByTitle("CompanyName3").Item(1).Range.Text = Me.TextBox1
'TextBox2 - City name repeat
.SelectContentControlsByTitle("City").Item(1).Range.Text = Me.TextBox3
.SelectContentControlsByTitle("City2").Item(1).Range.Text = Me.TextBox3
.SelectContentControlsByTitle("Country1").Item(1).Range.Text = Me.ComboBox8
End With
End Sub

Private Sub CommandButton2_Click()
'User has cancelled so unload the form
Unload Me
End Sub

[gmayor]

You would need a trap for each required missing item e.g.


If Me.TextBox1.Text = "" Then
MsgBox "Client Legal Name Required"
Me.TextBox1.SetFocus
Exit Sub
End If
If Me.TextBox2.Text = "" Then
MsgBox "Address Required"
Me.TextBox2.SetFocus
Exit Sub
End If
If Me.TextBox3.Text = "" Then
MsgBox "City Required"
Me.TextBox3.SetFocus
Exit Sub
End If
If Me.TextBox4.Text = "" Then
MsgBox "Post Code Required"
Me.TextBox4.SetFocus
Exit Sub
End If
If Me.TextBox5.Text = "" Then
MsgBox "Client Contact Required"
Me.TextBox5.SetFocus
Exit Sub
End If
If Me.TextBox6.Text = "" Then
MsgBox "Position Title Required"
Me.TextBox6.SetFocus
Exit Sub
End If
If Me.TextBox6.Text = "" Then
MsgBox "Position Title Required"
Me.TextBox6.SetFocus
Exit Sub
End If

[gmaxey]

Private Sub CommandButton1_Click()
'Validate
'or
ValidateII
End Sub
Sub Validate()
Dim lngIndex As Long
'Say you have six indexed textboxes
For lngIndex = 1 To 6
If Controls("Textbox" & lngIndex) = vbNullString Then
Controls("Textbox" & lngIndex).SetFocus
MsgBox "You can't leave this field blank"
Exit For
End If
Next
End Sub
Sub ValidateII()
Dim oCtr As Control
'Say you have a bunch of non-indexed textboxes
For Each oCtr In Controls
If TypeName(oCtr) = "TextBox" Then
With oCtr
'If .Tag = "I can't be blank" Then 'optional condition
If .Text = vbNullString Then
.SetFocus
MsgBox "You can't leave this field blank"
Exit For
End If
'End If
End With
End If
Next
End Sub