One content control updating another

jeffreybrown · #1 09-16-2018, 06:29 AM

I found this thread, but can't figure out two things.

1) How to edit this code for only one additional entry
2) How to know the index number of a control

In the first dropdown, there are only be two variables: 150.7 and 185

If 150.7 is selected, I want the other dropdown to read 1.077
If 185 is selected, I need the other dropdown to read 1.044

I have set the first dropdown with the display name as 150.7 and the value as 1.077, but can't figure out adjusting the code below to not need the Replace part.

I'm using content control instead of form fields because I read they don't play well together. Also, there are many other content controls on this doc.

The code runs fine with the loop, but not really needing the loop and figuring out the index of the content control.

Code:

Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
Dim i As Long, StrDetails As String
With ContentControl
  If .Title = "Client" Then
  For i = 1 To .DropdownListEntries.Count
    If .DropdownListEntries(i).Text = .Range.Text Then
      StrDetails = Replace(.DropdownListEntries(i).Value, "|", Chr(11))
      Exit For
    End If
  Next
  ActiveDocument.ContentControls(2).Range.Text = StrDetails
  End If
End With
End Sub

macropod · #2 09-16-2018, 03:21 PM

Quote:

Originally Posted by jeffreybrown

I found this thread, but can't figure out two things.

1) How to edit this code for only one additional entry
2) How to know the index number of a control

In the first dropdown, there are only be two variables: 150.7 and 185

If 150.7 is selected, I want the other dropdown to read 1.077
If 185 is selected, I need the other dropdown to read 1.044

I have set the first dropdown with the display name as 150.7 and the value as 1.077, but can't figure out adjusting the code below to not need the Replace part.

You could leave the line:
StrDetails = Replace(.DropdownListEntries(i).Value, "|", Chr(11))
as is and not have any | separators, or change it to:
StrDetails = .DropdownListEntries(i).Value

Later in the same thread (https://www.msofficeforums.com/word-...html#post68709) there is advice on how to reference a content control via its title rather than its index #. If you really want to know the index #, select the content control and run:

Code:

Sub GetIndex()
MsgBox ActiveDocument.Range(0, Selection.Range.End).ContentControls.Count
End Sub

PS: Please post Word programming threads in the Word VBA forum.

jeffreybrown · #3 09-16-2018, 05:55 PM

Thanks Paul and sorry about posting in the wrong forum.

Since I have many different content controls in this one doc, I need to update the entire doc no matter what control is updated. I added the part in blue to the event below, but after the event fires, instead of the whole doc being selected, can I select maybe the cell below? The content control is in a table. What about if the user doesn't tab, but instead they just select some other cell?

Code:

Private Sub Document_ContentControlOnExit(ByVal ContentControl As ContentControl, Cancel As Boolean)
Dim i As Long, StrDetails As String
With ContentControl
  If .Title = "MyCC_MAF" Then
    For i = 1 To .DropdownListEntries.Count
      If .DropdownListEntries(i).Text = .Range.Text Then
        StrDetails = Replace(.DropdownListEntries(i).Value, "|", Chr(11))
        Exit For
      End If
    Next
  ActiveDocument.ContentControls(2).Range.Text = StrDetails
  End If
End With
With Selection
    .WholeStory
    .Fields.Update
End With
End Sub

macropod · #4 09-16-2018, 06:04 PM

Quote:

Originally Posted by jeffreybrown

Since I have many different content controls in this one doc, I need to update the entire doc no matter what control is updated.

That's a bit ambiguous - what updates to the whole document are necessitated by updating a single content control?

Quote:

Originally Posted by jeffreybrown

I added the part in blue to the event below, but after the event fires, instead of the whole doc being selected, can I select maybe the cell below? The content control is in a table. What about if the user doesn't tab, but instead they just select some other cell?

The code you added is for updating fields, not content controls. Whether one exits a content control by tabbing or via the mouse is inconsequential.

jeffreybrown · #5 09-16-2018, 07:44 PM

There are multiple tables, each with content controls and calculated fields.

Each of those tables has a final calculated field which feeds a final overall calculated field in the very first table.

If any content control is updated, it will affect that table, but also the overall calculated field. This Attachment 4 is just one section of a larger document.

macropod · #6 09-16-2018, 10:47 PM

For what you've attached, all you need for the macro is:

Code:

Private Sub Document_ContentControlOnExit(ByVal CCtrl As ContentControl, Cancel As Boolean)
Dim i As Long, r As Long, c As Long, StrOLF As String
With CCtrl
  If .Title = "MyCC_MAF" Then
    For i = 1 To .DropdownListEntries.Count
      If .DropdownListEntries(i).Text = .Range.Text Then
        StrOLF = .DropdownListEntries(i).Value
        Exit For
      End If
    Next
    With .Range.Cells(1)
      r = .RowIndex
      c = .ColumnIndex
    End With
    With .Range.Tables(1)
      .Cell(r, c + 1).Range.Text = StrOLF
      .Cell(r, c + 2).Range.Text = Format(StrOLF * CCtrl.Range.Text, "0.0")
    End With
  End If
End With
ActiveDocument.Fields.Update
End Sub

You don't need a content control for OLF or a field for MAF+OLF. Since the latter is referenced in subsequent formulae, though, you can get the same result by replacing:
{=B6/MyMAF \# "0.00" }
with:
{=B6/SUM(MyTotalReq D2) \# "0.00"}

jeffreybrown · #7 09-17-2018, 02:56 PM

Thank again for your time and help.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Content Control content deleted when re-uploading to SharePoint	Peterson	Word	5	06-27-2018 08:13 PM
One Content Control Checkbox checks another Content Control Checkbox	DEsh	Word VBA	2	10-06-2017 08:23 PM
Clicking the selected Content Control checkbox returns wrong control in vba event	DougsGraphics	Word VBA	2	06-24-2015 07:31 AM
Deleting a table from a content control -- preserving the content control	BrainSlugs83	Word Tables	8	11-14-2013 03:06 AM
Automatically Updating Content in Text	dumbass0101	Word	4	10-11-2012 07:50 AM