Transfer data from one Content Control to another Word 2007 VBA

[Ailish]

Hi Everyone,

My quest is for a code snippet that would grab what has been selected in one
combo box content control (Word 2007), transfer it to an identical combo box
content control in another section of my document and return the first
content control to placeholder text.

For example: transfer data from a combo box content control with the unique
title cmbx1MobAid to cmbx3MobAid

To complicate matters, because this is a combo box, the users of the
document may have (for example):
(a) selected "Zimmer frame" from the dropdown list
(b) adapted the dropdown selection of "Zimmer frame" as above by adding
their own text "Uses" before and "at night only" after. So the print-out
reads: "Uses Zimmer frame at night only" (This was relevant to a recent
patient who could walk independantly by day but was far less capable at night
and illustrates a nurse's need to be able to manipulate the information
provided in a content control to fit the individual.)
(c) not selected anything from the dropdown list and typed in the name of a
new mobility aid that was never on the dropdown list at all.

Here's a code snippet that works for transferring plain text controls from
room 1 to room 3 - I thought it might give you an idea of what I'm trying to
do with combo boxes, dropdown lists and date pickers:

Dim str1PtName as String
'declares string variable = whatever has been selected in content control
with title 1PtName
str1PtName =
ActiveDocument.SelectContentControlsByTitle("1PtName").Item(1).Range.Text
'transfers patient's name from room 1 to room 3
ActiveDocument.SelectContentControlsByTitle("3PtName").Item(1).Range.Text =
str1PtName
'removes name from room 1
ActiveDocument.SelectContentControlsByTitle("1PtName").Item(1).Range.Text = ""

Actually the above has only been a partial success in that it replaces the
placeholder text which is in a pale grey font that is not intrusive on the
print-out to a black font which would be intrusive - I don't like that.

The background to this is that I'm a nurse (no IT qualifications so please
bear that in mind where jargon is concerned), working for a hospice charity
in the UK, with an interest in making all those expensive computers we have
sitting in our offices actually work for us! What I'm working on is a form
which provides up-to-date info about our in-patients to nurses coming on duty.

My document is laid out according to room location on each ward but
sometimes patients get transferred from say a shared room to a single room
when there is a great need for privacy or vice versa if they feel a bit
lonely and would like a bit of company. What's good for the patient and
family can totally muck up the layout of my form and give my colleagues a lot
more typing to do - not popular, I can tell you! Up till now I've told my
colleagues that automating the transfer is in the "too hard" basket but
thanks to help from this (thanks Jay!) and other forums, I've achieved other
things that I thought were impossible. It would be very cool to achieve this
one too.

Thanks for reading this far and thanks for any help you can offer.

Ailish

 

[Jay Freedman]

Hi Ailish,

I'm still here...

The snippet you showed can be simplified. First, declare two variables to
represent the content controls themselves, and set those variables to point to
the controls for the two rooms. Then you can just assign the text of one control
directly to the text of the other control, without bothering to go through a
string variable. Exactly the same code works for the visible text in a combo box
-- whether or not it has been edited -- as for a text control.

I set up a sample document with a text control and a combo box for each of two
rooms, with titles "1PtName" and "1PtCondition" for one room and "3PtName" and
"3PtCondition" for the other room. This macro transfers the contents of room 1
to the controls for room 3, and returns room 1's controls to placeholder text:

Sub demo()
Dim cc1 As ContentControl
Dim cc2 As ContentControl
Dim cc3 As ContentControl
Dim cc4 As ContentControl

' Transferring between text boxes
Set cc1 = ActiveDocument.SelectContentControlsByTitle("1PtName").Item(1)
Set cc3 = ActiveDocument.SelectContentControlsByTitle("3PtName").Item(1)

If Not cc1.ShowingPlaceholderText Then
'transfers patient's name from room 1 to room 3
cc3.Range.Text = cc1.Range.Text
'removes name from room 1
cc1.Range.Text = ""
End If

' Transferring between combo boxes
Set cc2 = ActiveDocument.SelectContentControlsByTitle("1PtCondition").Item(1)
Set cc4 = ActiveDocument.SelectContentControlsByTitle("3PtCondition").Item(1)

If Not cc2.ShowingPlaceholderText Then
'transfers patient's name from room 1 to room 3
cc4.Range.Text = cc2.Range.Text
'removes name from room 1
cc2.Range.Text = ""
End If
End Sub

For each transfer, if the room 1 control is showing placeholder text already,
the If statements ensure that nothing is done. That's to avoid the situation you
mentioned about the placeholder text changing from gray to black -- you were
replacing actual (gray) placeholder text with regular (black) text that just
happened to be the same characters as the placeholder text.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so all
may benefit.

 

[Ailish]

Muah! Jay!

You are an absolute angel!

It's gone 4am here but I couldn't resist a quick check to see if there was
any answer to my query - meanwhile I've been baking cakes for my son's
birthday party this weekend. I can hardly see straight at this stage
(probably has more to do with the slurps of wine than the cake baking!) but I
set up a new doc and tried out your code. It does exactly what it says on
the the tin (= a slogan used in certain adverts over here) and looks like
just what I needed.

I'm way too tired to put it to work on my form right now but I almost can't
wait to wake up!

Thank you sooooooo much
Ailish

 

[Jay Freedman]

My pleasure. I hope the cakes and the party turned out well. (I don't know of
any wine-based birthday cakes!)

Jay

 

[Ailish]

Dear Jay,

I've had success transferring data from all my plain text, combo and date
picker CCs but drop-down list CCs are causing a run-time error:

Run-time error '6124':
You are not allowed to edit this selection because it is protected.

I know I asked about combo box CCs originally - I thought what would work
for them would work for dropdown list CCs because the combo box incorporates
a dropdown list.

I've tried all evening to find something that would work myself but I'm
stuck... again!

I can even see why my best attempt (copied below) is a no go... the line
where I set ccLE3PtMedication = the first item in DropDownListEntries is
making the PtMedication CC in room 3 show the placeholder text instead of
reflecting what was selected in cc1PtMedication. But knowing where I'm going
wrong is not helping me get it right.

Could you help me please?
Ailish

Sub demo2()
Dim cc1PtMedication As ContentControl
Dim ccLE1PtMedication As ContentControlListEntry
Dim cc3PtMedication As ContentControl
Dim ccLE3PtMedication As ContentControl

Set cc1PtMedication =
ActiveDocument.SelectContentControlsByTitle("1PtMedication").Item(1)
'Set ccLE1PtMedication =
ActiveDocument.SelectContentControlsByTitle("1PtMedication").Item(1).DropdownListEntries.Item(1)

Set cc3PtMedication =
ActiveDocument.SelectContentControlsByTitle("3PtMedication").Item(1)
'Set ccLE3PtMedication =
ActiveDocument.SelectContentControlsByTitle("1PtMedication").Item(1).DropdownListEntries.Item(1)

If Not cc1PtMedication.ShowingPlaceholderText Then
'transfers contents from PtMedication section room 1 to PtMedication
section room 3
ccLE3PtMedication =
ActiveDocument.SelectContentControlsByTitle("1PtMedication").Item(1).DropdownListEntries.Item(1).Value



'room 1 PtMedication CC is cleared (= shows placeholder text because CC
property is
'set so CC can't be deleted)
cc1PtMedication.DropdownListEntries.Item(1).Select

End If
End Sub

 

[Greg Maxey]

While I am not Jay, I don't think he will mind.

If I understand your objective, you have two dropdowns. You want to select
an item in the first and have it replicated in the second while setting the
first to show placeholder text. Something like this might work:

Sub demo2()
Dim cc1PtMedication As ContentControl
Dim cc3PtMedication As ContentControl
Dim i As Long
Dim j As Long
Set cc1PtMedication =
ActiveDocument.SelectContentControlsByTitle("1PtMedication").Item(1)
Set cc3PtMedication =
ActiveDocument.SelectContentControlsByTitle("3PtMedication").Item(1)
If Not cc1PtMedication.ShowingPlaceholderText Then
'Clear existing entries in cc3
cc3PtMedication.DropdownListEntries.Clear
'Add each list entry in cc1 to cc3
For i = 1 To cc1PtMedication.DropdownListEntries.Count
cc3PtMedication.DropdownListEntries.Add
cc1PtMedication.DropdownListEntries(i)
Next i
'Determine which entry in cc1 is selected
For j = 1 To cc1PtMedication.DropdownListEntries.Count
If cc1PtMedication.Range.Text = cc1PtMedication.DropdownListEntries(j)
Then Exit For
Next j
'Show placeholder text in cc1
cc1PtMedication.DropdownListEntries.Item(1).Select
'Show item selected in cc1 in cc3
cc3PtMedication.DropdownListEntries.Item(j).Select
End If
End Sub

 

[Jay Freedman]

Thanks, Greg, couldn't have said it better myself.

I have just one tweak: If both dropdowns contain the same entries already
(because you design the template that way), then you can remove these
statements:

'Clear existing entries in cc3
cc3PtMedication.DropdownListEntries.Clear
'Add each list entry in cc1 to cc3
For i = 1 To cc1PtMedication.DropdownListEntries.Count
cc3PtMedication.DropdownListEntries.Add
cc1PtMedication.DropdownListEntries(i)
Next i

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so all
may benefit.

 

[Ailish]

Thanks Greg,

May I say that I've found your Microsoft Word Tips have been extremely
helpful over the past few weeks whilst I've been working on this project,
thank you.

Being a dropdown list, I had an idea that the answer would have something to
do with the value i.e. a number. But I was a long way off thinking up the
code you just gave me.

It worked very well except that clearing cc3, then adding items
unfortunately cleared the grey placeholder text and turned it black and I
need it to stay pale grey because eventually this patient's data will be
deleted too. Jay's "tweak" has solved that problem.

Thanks a lot for your help. You guys rock!
Ailish

 

[Ailish]

Thanks Jay,

Your tweak made everything perfect.

I'm off now to type up code for 56 x 7 x 8 contentcontrols. That should
keep me out of mischief for a while. Thank heavens for copy, paste, find,
replace and you guys!

Ailish