Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Word > Word VBA

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 09-16-2018, 06:29 AM
jeffreybrown jeffreybrown is offline Windows Vista Office 2007
Expert
 
Join Date: Apr 2016
Posts: 448
jeffreybrown will become famous soon enough
Default One content control updating another

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
Reply With Quote
  #2  
Old 09-16-2018, 03:21 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 18,249
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Quote:
Originally Posted by jeffreybrown View Post
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 (http://www.msofficeforums.com/word-v...l#post68709)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.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #3  
Old 09-16-2018, 05:55 PM
jeffreybrown jeffreybrown is offline Windows Vista Office 2007
Expert
 
Join Date: Apr 2016
Posts: 448
jeffreybrown will become famous soon enough
Default

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
Reply With Quote
  #4  
Old 09-16-2018, 06:04 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 18,249
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Quote:
Originally Posted by jeffreybrown View Post
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 View Post
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.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #5  
Old 09-16-2018, 07:44 PM
jeffreybrown jeffreybrown is offline Windows Vista Office 2007
Expert
 
Join Date: Apr 2016
Posts: 448
jeffreybrown will become famous soon enough
Default

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.
Attached Files
File Type: docm Total Req.docm (43.3 KB, 1 views)
Reply With Quote
  #6  
Old 09-16-2018, 10:47 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 18,249
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

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"}
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #7  
Old 09-17-2018, 02:56 PM
jeffreybrown jeffreybrown is offline Windows Vista Office 2007
Expert
 
Join Date: Apr 2016
Posts: 448
jeffreybrown will become famous soon enough
Default

Thank again for your time and help.
Reply With Quote
Reply

Thread Tools
Display Modes


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


All times are GMT -7. The time now is 03:52 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft