Using VBA attached to buttons to copy content and paste it WITH FORMATTING! HELP!!!

[GraphicDesig]

Hi There,

I need some help on some MS Word VBA!

I have created a document (included as attachment!) and I would like to be able to do the following things using VBA!


I would like to be able to 'lock down' the document so that Only the Rich Text Controls are editable, but would like to have a button on the front page to be able to enter a password of my choosing to unlock the file to make changes?
I would also like a button at the bottom of page 1 (page after cover page, so technically page 2, but numbered page 1) which when clicked, will copy the TABLE ONLY from the 'Subject Overview(s)' section, and paste that in below the existing table, with 1 HR inbetween. I need the formatting etc. all to be identical, if it spans to a new page, I need the header and footer for that section to continue, exactly as is on source page. If possible, before this task is completed, I would like a pop up box to come up asking how many times I would like to copy the table, and then repeat the task the number of times entered by the user into the box?
I would like the same task as above to be possible, in exactly the same way, but with a button at the bottom of the LAST PAGE, which would copy that last page in it's entirety, all formatting etc. the same, and past at the end of the doc, with headers, footers etc still following on.


I need all these buttons to be non-printing as well please!

I hope this is possible first of all, and that one of you can help me!! Thanks so much!!!



C

[gmayor]

The business of protecting documents that have content controls is covered in some depth at http://gregmaxey.mvps.org/word_tip_pages/content_control_fillin_form.html.

As for the rest, it would make things a lot simpler if you add the buttons to a custom ribbon tab saved in the document itself. If you make your tab the first tab, the tab will be opened automatically when the document is opened. Buttons on the ribbon are always available and don't print.

The bigger issue is the subject of macros. Macros will tend to trip Word's security measures, so unless you can convince your users to run the macros there project is dead in the water. You cannot force a user to run the macros.

The macros themselves sound straighforward, I'll come back later on that.

[gmayor]

OK - the macros are as follows. You will have to add in the code to unprotect and reprotect, based on Gregs web page.
You will find instructions for editing the ribbon on Greg's site, but I have saved you the bother in the attached. I'll leave it to you to make any cosmetic changes.: pray2:


Sub CopyTable()
Dim oTable As Table
Dim oRng As Range
Dim oTarget As Range
Set oTable = ActiveDocument.Tables(3)
Set oRng = oTable.Range
oRng.MoveStartUntil "S", wdBackward
oRng.Start = oRng.Start - 1
Set oTarget = ActiveDocument.Sections(1).Range
oTarget.End = oTarget.End - 1
oTarget.Collapse wdCollapseEnd
oTarget.InsertParagraphBefore
oTarget.FormattedText = oRng.FormattedText
Set oRng = Nothing
Set oTarget = Nothing
End Sub

Sub CopyLastPage()
Dim oTable As Table
Dim oCell As Range
Dim oRng As Range
Dim oTarget As Range
Set oRng = ActiveDocument.Range
oRng.Collapse wdCollapseEnd
oRng.Select
ActiveDocument.Bookmarks("\page").Range.Select
Set oRng = Selection.Range
ActiveDocument.Range.InsertParagraphAfter
Set oTarget = ActiveDocument.Range
oTarget.Collapse wdCollapseEnd
oTarget.InsertParagraphBefore
oTarget.FormattedText = oRng.FormattedText
Set oTable = ActiveDocument.Tables(ActiveDocument.Tables.Count)
Set oCell = oTable.Rows(2).Cells(1).Range
With oCell.Find
Do While .Execute(FindText:="^#")
oCell.Text = CInt(oCell.Text) + 1
Exit Do
Loop
End With
End Sub

[gmayor]

On further reflection I returned the test file as a template. It would probably be better to Save As as a macro enabled document if you are distributing it.

[GraphicDesig]

GMayor...you are a genius, great idea with the Ribbon editing rather than a button on the page. Very Good!

Just so I can understand a little, if I wanted to change the name of the Buttons on the ribbon, i.e. change the one that says 'Add Options Table' to say, 'Add Another Subject Overview' where would I change this in the code?

Also, the final page works wonderfully, copied in it's entirety, and thanks so much for making the Day Number 'tick' up aswell! Very useful! I was wondering if there was anything you could add to the code, which would mean that when copying the first table (the Subject Overview Table), if it were going to wrap to another page, rather than have some of the table on one page, and some on another, whether it could all move to the next page? Keeping it all together? If so, what would the new code be for this?

Thanks so much!

C

[gmayor]

To edit the ribbon, you will need the CustomUI editor - http://openxmldeveloper.org/blog/b/openxmldeveloper/archive/2009/08/06/7293.aspx

I have only used the 2007 schema (which is compatible with all Word versions up to and including 2013).


<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<ribbon>
<tabs>
<tab id="CoreTab01" insertBeforeMso="TabHome" label="Core Skills" >
<group id="Grp0" label="Batch Processes" >
<button id="Grp0Btn1" label="Add Options Table"
image="GroupInsertTables"
size="large"
getSupertip="RibbonControl.GetSupertip"
onAction="RibbonControl.ButtonOnAction" />

<button id="Grp0Btn2" label="Add Schedule Table"
image="calendar3"
size="large"
getSupertip="RibbonControl.GetSupertip"
onAction="RibbonControl.ButtonOnAction" />

<button id="Grp0Btn4" label="Print Form"
image="Print"
size="large"
getSupertip="RibbonControl.GetSupertip"
onAction="RibbonControl.ButtonOnAction" />

<button id="Grp0Btn3" label="Edit Form"
image="AdvancedFileProperties"
size="large"
getSupertip="RibbonControl.GetSupertip"
onAction="RibbonControl.ButtonOnAction" />

</group>
</tab>
</tabs>
</ribbon>
</customUI>




If you want to get more adventurous with the ribbon, my old friend Greg Maxey has lots of information on his web site http://gregmaxey.mvps.org/word_tip_pages/customize_ribbon_main.html

Open the template/document and view the content - Make sure the template is not open in Word (and do not make changes to the template in Word while the template is open in the editor).

As for the wrapping of the table, it appears from the sample that it will always be split, so you might as well insert a page break rather than a paragraph break, between the tables e.g.


Sub CopyTable()
Dim oTable As Table
Dim oRng As Range
Dim oTarget As Range
Set oTable = ActiveDocument.Tables(3)
Set oRng = oTable.Range
oRng.MoveStartUntil "S", wdBackward
oRng.Start = oRng.Start - 1
Set oTarget = ActiveDocument.Sections(1).Range
With oTarget
.End = .End - 1
.Collapse wdCollapseEnd
.InsertBreak wdPageBreak
.End = oTarget.End - 1
.Collapse wdCollapseEnd
.FormattedText = oRng.FormattedText
.Collapse wdCollapseEnd
.Select
End With
Set oRng = Nothing
Set oTable = Nothing
Set oTarget = Nothing
lbl_Exit:
Exit Sub
End Sub

[GraphicDesig]

Hi GMayor,

Thanks for the help, don't worry about the code, I can deal with that, but I don't suppose you could change the names of the buttons on the ribbon for me and re-upload the file could you? It's just that I work in an office where installations of non-standard programs are prohibited, so wouldn;t be able to do it for myself?

I would like them to change as per below:

'Add Options Table' changes to 'Add Subject Overview'
'Add Schedule Table' changes to 'Add Session Delivery Plan'
'Edit Form' remove completley, have figured out how to stop changes using web link from earlier.
'Print Form' remains unchanged

Thanks so much and sorry for being cheeky and asking you to do that for me!

C

[GraphicDesig]

Sorry, one more thing...If I activate WORD's Protection Settings, would the user be able to use your buttons created on the ribbon? As at present all mine are greyed out? They would need to be able to add the subject overview and session delivery plans, but just not edit the rest of the document? Can your buttons over ride the security, or do we need to come up with a way around that?

C

[gmayor]

There is no way around it. If you have macros in a document, you cannot force users to run them. That would leave them wide open to malicious software.
If the document is protected (see my first reply) you will need to unprotect before you can run the macros. You will need to add code to unprotect then re-protect.

[GraphicDesig]

Hi GMayor, Yeah I realise I can't force them to run them, that's down to my own internal procedure to ensure they do so.

In which case, could you add 2 codes, with buttons on the ribbon. One to add protection to the document allowing only The Forms to be edited, which asks for a password to be entered to protect. Then another code and accompanying button to remove that security following correct password being entered?

That should then be me completley done?! I 'll be out of your hair then! Haha! I do really appreciate all your expertise and help!

C

[gmayor]

Do you need a pair of buttons for that? As it will be only for admin use, I would suggest using the developer tab.
You could remove the edit form button by editing the ribbon XML code.
If you have enough control to get users to run macros, then frankly I wouldn't bother with protection. If you must then you will have to add code to the macros e.g.




Const strPassword As String = "password"
Dim bProtected As Boolean
'Unprotect the document
If ActiveDocument.ProtectionType <> wdNoProtection Then
bProtected = True
ActiveDocument.Unprotect Password:=strPassword
End If

'Do stuff

'Reprotect the document.
If bProtected = True Then
ActiveDocument.Protect _
Type:=wdAllowOnlyFormFields, _
NoReset:=True, _
Password:=strPassword
End If
lbl_Exit:
Exit Sub

[GraphicDesig]

What I meant by control is that they need to copy the sections, i.e. run the macro, in order to complete the form to such a standard that it meets their own needs when it comes to teaching material to students. So they will run it by choice. What I just don't want is them having the ability to edit anything other than add in those sections where appropriate? These forms will be being used by other people aside from the creator once completed (in printed paper form) and they must all look uniform, hence the protecting of the sheets aside from the fields?

Could you factor the necessary into your macros you kindly wrote for me then please? so I have them as a whole in their entirety??

I suggested the two buttons for my own use really? I'm going to be the only admin for now, but because for the general users it seems easier and safer to have the protection factored into the macros, I was wondering if I could have the two buttons on the ribbon, so that I could just deactivate the protection without running any of the other part of the macro, make whatever changes I may need, and then re-activate the protection with the other button again? This means if other admins are added, again, they only need those buttons on that one tab, rather than chopping and changing between those buttons and Word's restrict editing settings?

Would you be able to provide another file as you did before with those amended macros saved in, and the buttons on the ribbon added for me as previoulsy mentioned, I cannot install the UI editor here?

Once again, thanks so much for all your help on this matter, massive thank you and appreciation!

C

[gmayor]

If the document is protected (and provided you add the password to the two macros) the version attached includes the ability to unprotect and protect.
You don't need the CustomUI editor to edit the ribbon. You can do that with Notepad. The DOCM/DOTM files are XML in a zipped format. If you change the extension to ZIP you can open them from Windows Explorer (with ZIP utilities such as WinZip/WinRar you don't even need to do that). Locate CustomUI.xml and edit it with Notepad.

I don't think the two buttons are sensible. If you include the password then any user will be able to access the content. Better to use the Developer, but I have compromised and added a LOCK/UNLOCK button to the QAT (Quick Access Toolbar) . It will prompt you for the password. Note that form password protection is very weak.

[GraphicDesig]

Hi There GMayor,

Firstly, thank you so much for all your help those few weeks ago, it was a GREAT help and the document has been working wonderfully thus far.

As per usual, amends have been suggested, which I attempted to make, and I seemed to royally fudge up all the work you had done, so just didn;t save any changes, made the changes in layout etc. to the original word doc I gave you and now here I am once again asking for your help.

I was wondering if you could help me with the following changes:

I have altered the layout of the Contents page (page 1 by numbering, page 2 sequentially), and I was wondering if you could put the Macro back in place to copy the 'Subject Overview' table, and paste it on the next page but BEFORE the section break between the contents and Timetable?
I was also wondering if you could add another button doing the same, for the 'Aims, Objectives and Topics Covered Table'? If the Subject Overview hasn't been copied already, then obviously copy and paste the 'aim...' table to the next page, once again, BEFORE the section break.


Because I fudged the last document up the one attached to this reply has none of your previous work in it? If you can make these changes to the previous version you gave me, then by all means do so and potentially save yourself work?!



I have been asked if a button could be added, which when clicked would add a pre-formated page directly after one of the Session Delivery Day Pages of the users choice? I was thinking perhaps a pop up box appears asking which Day the user would like to insert the page after? Then once clicked Add or OK whatever the button on the pop up says, the page is added? Would that be possible? If so, I just want the new page to say "Additional Notes" and then a normal space, and then pick up the DAY from the page that this new page follows i.e. Day 1, Day 2, Day 3 etc. and insert that so that Title says "Addition Notes Day 1" for example? Could this appear in the header as per the other titles currently do?!
Very Finally, would it be possible to have a button to delete a page, in it's entirety without leaving any blank pages, anything like that? Again perhaps with a pop up box which appears asking which page the user would like to delete?


Sorry to ask so many questions, you really have been a stunning help on this, and I assure you that this should be the last round of queries I have for you on this. Thank you so much once again!




Chris