Copying Customized Tables...and Form Protection?

[kidego365]

Hello All,

I wanted to say first that I am learning a lot about VBA just by browsing this forum, there are some very smart folks here!

I am new to VBA and have just starting doing some tutorials and basic lessons in coding. Here is what I am trying to accomplish: the specialized private school I work for has to generate individual education plans for each student that is enrolled. I have created a FORM in Microsoft Word (2003) so that the staff here can't mess up the layout. The challenge is that each FORM needs to be slightly different with the number of goals per student. Instead of having a bunch of empty tables, I woud like to create a macro that would copy an additional table down beneath the one it copies so that the user can select a button to manually add these tables as needed.

The tables are specifically formatted so I don't think having VBA create the table each time would work unless I can specify all of the table options in VBA and then generate it. I am thinking that copying/pasting is the way to go.

Does that sound too confusing? If need be I can attach the document for you to see.

The second part of my question is that when I protect the document I am unable to run a basic macro that I am learning, it gives me the runtime error about the form being protected. Any way around this?

Thanks so much for offering a venue like this to learn and seek guidance.

[OTWarrior]

Welcome to the forums, always good to see a new face :)

If you could attach your document (taking out any confidential information) that would be excellent.

As for the macro error, I suspect that the macro you are trying to run requires parts of the document to be unprotected to modify them. A way around this is to unprotect the document in your macro, and to protect it again once you after finished.

here is a function you could call at the beginning and end of your macro:
NB: this is a toggle macro, so it will test if the document is protected or not.

Sub MegaUnprotect()
If ActiveDocument.ProtectionType <> wdNoProtection Then
ActiveDocument.Unprotect Password:="Password"
Else
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True, Password:="Password"
End If
End Sub

I hope that helps :)

[kidego365]

Awesome, thanks for the info so far, I am definitely going to play around with the code you posted!!

And thanks for your offer. I went ahead and attached the first portion of our forms that I am trying to create to this message. All of it is pretty straight forward, I think...what I would ultimately need would be the macro to copy/paste a portion of the table you will see under "Goal 1" and be able to replicate it with its formatting intact. Some children will only have one goal while other may have as many as 15 or so!! Instead of having 20 blank tables it would be nice to have a "clean" form where only the number of goals needed would be displayed because the users would have the option of adding additional goals.

The only way I can think of to do this would be a macro to copy/paste tables as such....but I would also be open if there is a better way. Thanks so much for offering to look at it and let me know if you need anything else!! :rotlaugh::rotlaugh::rotlaugh:

[fumei]

Not quite following.

By "goals", do you mean "objectives"?

Objectives:
Student Will

Does adding another goal mean adding another row, under "Student Will"? Or does it mean an entirely new table?

I do not like the idea of using the checkbox with "Click here to add another goal." Clicking simply puts a check into the checkbox. By itself it does not execute any instructions (like adding anything).

It COULD, by using an OnExit macro to the checkbox. However, you would have to check the box, AND then move on...in order to make the OnExit routine execute.

There are a number of options. You could use a shortcut key to fire a routine to add another goal. With the text: "Press Alt-A to add another goal."

You could put a button on a toolbar with the cation "Add Goal". Clicking the button would execute the instructions.

Depending on the security settings, you could put a commandbutton in the document. Clicking could execute the instructions.

I would strongly recommend using explicit names. So instead of "Text2" being the formfield for DOB, name the formfield...."DOB". Name the formfield for District "District"...rather than "Text4".

If you do want a new table, and the new table is going to have formfields in it, then copying and pasting is not a good idea. It is not a good idea to copy and paste formfields, as the copy has no name, nor does it have a bookmark assigned to it.

[OTWarrior]

I have removed your tick box, and added a downdown menu (highlighted in yellow). When you select the number, it will copy the first table, and paste the number of times of the number selected. Once this is done, it will delete the dropdown menu and replace it with text displaying the number of goals there are.

The reason why I get it to delete it is it is only designed to use once. If you want to try and make some code to delete any tables you don't need by all means give it a go (I have tried and failed in the past), but I hope what I have submitted is suitable for you.

(note, the password is blank in this document, if you want a password, put it into the code under the PasswordToggle sub)

Enjoy :)

NB: I know it's a copy and paste routine Fumei, but unless the formfields are going to be reference later, I don't see a problem with them not have valid names. I personally would name all formfields with something, but if they do need to be reference the index numbers can always be used. I recently had to wrote a proceedure to copy an official document into our database, and the document had "text1" for all 500 formfields!.....I am so glad I could use the index numbers :D)

PS: Good to see you again Fumei :)

[OTWarrior]

I had another thought, you could dynamically assign names to the formfields in the tables as you create them.

Example
ActiveDocument.FormFields(6).Name = "Goal1Text"

I will let you figure out how to do it for all of the formfields you create, but do ask if you get stuck :)

[kidego365]

By "goals", do you mean "objectives"?

That's a good point, I would actually need BOTH, because there may be multiple goals and multiple objectives...YIKES!


Does adding another goal mean adding another row, under "Student Will"? Or does it mean an entirely new table?

I think the best thing to do would to be able to copy the entire table (form fields and all) from the row beginning with "Goal 1" and down to the row beneath "Student Will". It is a little confusing, I know...which is why I like your suggestions below a LOT.


I do not like the idea of using the checkbox with "Click here to add another goal." Clicking simply puts a check into the checkbox. By itself it does not execute any instructions (like adding anything).

It COULD, by using an OnExit macro to the checkbox. However, you would have to check the box, AND then move on...in order to make the OnExit routine execute.

There are a number of options. You could use a shortcut key to fire a routine to add another goal. With the text: "Press Alt-A to add another goal."

It is obvious that you have a lot of experience doing this, because as soon as you made this point I slapped myself right on the forehead. You are totally correct. The thing is I am so new to this that I don't have the foggiest idea how to go about making this happen!


I would strongly recommend using explicit names. So instead of "Text2" being the formfield for DOB, name the formfield...."DOB". Name the formfield for District "District"...rather than "Text4".

I didn't even think to do that, thank you. I am sure this will make finding these objects a lot easier in VBA.


If you do want a new table, and the new table is going to have formfields in it, then copying and pasting is not a good idea. It is not a good idea to copy and paste formfields, as the copy has no name, nor does it have a bookmark assigned to it.
Ok, your points are great...what should I do next? I am a total newbie. :bow::bow::bow:

[kidego365]

awesomeness



man you rock!!! let me play around with this and let you know how it goes. we have an early release day today (another plus of working for a school!) so I may not have time to look at it until Monday. I will let you all know how it gos.

Thanks so much for giving me pointers, examples and ideas....can't begin to tell you how encouraging that is because learning VB seems to be a daunting task for a newbie!!!

:bow::bow::bow:

[kidego365]

Just a quick note to let you know that OT Warrior's code works great so far, and I haven't even looked at the code yet!! They only challenge I would have with it is that if users begin to fill in the form fields and THEN try to add more goals, it gets a little strange: sometimes working, sometimes not and always copying/pasting the data that is already in the form fields (a problem we all knew about anyway).

The problem I see coming is that the teachers and staff here will NOT follow directions, haha. They would start filling out the form fields and then realize after the fact that they need to add more goals or objectives and mess everything up!!

Thanks again for this, I am going to tinker around for a while and let you all know.

[fumei]

Hi OT.

"If you want to try and make some code to delete any tables you don't need by all means give it a go (I have tried and failed in the past)"

Interesting. How did you try and go about it? Particularly, how did you approach identifying which table to delete? If it was whatever table the Selection is in, that should be easy. If the tables are within their own bookmarks then you can delete any table you wish, by name.

Care to post a separate thread on this subject?

[kidego365]

If you create another thread, please post the link so I can try to absorb as much as I can on this!!

[fumei]

Always a good idea. Thanks for mentioning it.

[OTWarrior]

Hi OT.

"If you want to try and make some code to delete any tables you don't need by all means give it a go (I have tried and failed in the past)"

Interesting. How did you try and go about it? Particularly, how did you approach identifying which table to delete? If it was whatever table the Selection is in, that should be easy. If the tables are within their own bookmarks then you can delete any table you wish, by name.

Care to post a separate thread on this subject?

The problem I have always had with it is identifying the number of tables. If I had a way of working out the number of tables in a document, then you can just delete the table with it's index number using:

ActiveDocument.Tables(1).Delete

The times when I have wanted to use this (and not get it working) I have just changed the design to work around it.

Your reference to bookmarks is a good point Fumei, as one could unprotect the document, select the whole table, assign it a bookmark, then that could be used to delete it later. The problem with that is you would have to make sure that the bookmark is unique for every table.

Hmmm....I might try something with that document of yours kidego365. I'll be back later.

[kidego365]

Hmmm....I might try something with that document of yours kidego365. I'll be back later.

Can't thank you guys enough. I monkeyed around with your code so far and (me being a total newbie) tried to add support for up to 10 tables as opposed to the 5 you wrote code for...I ran into all sorts of issues, tee hee.

So far the code is pretty close to accomplishing what I need, so thanks very much for taking a crack at it!

:thumb

[fumei]

Hi OT.

"Your reference to bookmarks is a good point Fumei, as one could unprotect the document, select the whole table, assign it a bookmark, then that could be used to delete it later. The problem with that is you would have to make sure that the bookmark is unique for every table."

Hmmm, you are talking about creating bookmarks AFTER there is protection. Why not make the bookmarks BEFORE? I guess it depends on the situation. I make bookmarks for tables as a matter of course. In other words, I always bookmark tables. Simply because once bookmarked, you can, in fact, make reference to THAT table by name. No matter where it is in the document. It can be moved freely and it makes no difference, as the bookmark stays with it - or rather it (the table) stays with the bookmark.

By bookmarking, again, you can refer to the table by name, not Index number, which, as you know, can be problematic.

Yes, true, bookmark names must be unique. Two points regarding that.

1. IF the tables are not going to be moved around, then you could simply create the bookmarks as "Table1", "Table2", "Table3" etc.

Dim oTable As Table
Dim j As Long
j = 1
For Each oTable In ActiveDocument.Tables
oTable.Select
ActiveDocument.Bookmarks.Add "Table" & j
j = j + 1
Selection.Collapse 0
Next
Now each table is bookmarked, with the current order of the tables.

Again, IF the tables are static (they are not being moved around, or new tables inserted), this is not a problem.

ActiveDocument.Tables(1) now can be called as "Table1"
ActiveDocument.Tables(5) now can be called as "Table5"


Dim myTable5 As Table
Set myTable5 = ActiveDocument.Bookmarks("Table5") _
.Range.Tables(1)

' the myTable5 object can now be actioned
' as an OBJECT
' e.g. myTable5.Cell(3,2).Range.Text = "yadda"
' or whatever else you want to do with it

The best thing about using a table object is that all methods and properties are avilable, so you can action them without ever having to select the table.

2. It would better, quite true, if you could give the bookmark created for each table a meaningful name. I sometimes do that with:

Dim oTable As Table
Dim strTable As String

For Each oTable In ActiveDocument.Tables
oTable.Select
strTable = Inputbox("Table name?")
ActiveDocument.Bookmarks.Add strTable
Selection.Collapse 0
Next

which allows - ummm....demands - a given name for each table.

A variety of this approach could be to extract a name from, say, the first cell of each table. Assuming of course that the first cell has a meaningful name. Maybe the table has title text in the first cell.

From a long term perspective, it is better to have meaningful names. However, requiring that depends on the document and the needs of that document.

In any case, from the perspective of this thread, say the original table was bookmarked as "GoalTable1". The user wants to add another goal.

Depending on the situation the following may (or may not) be possible.

IMO, the best way would be:

1. have the structure of a basic table as an AutoText
2. the user wants to add a new goal - or in your example, say, selects 3 new goals via the dropdown.
3. the code uses the AutoText to insert a new table
4. the new table is bookmarked and named "GoalTable" + 1 (1.e. GoalTable2, GoalTable3....)
5. a new formfield is inserted into the new table at the appropriate place

I am not sure why there is a formfield in the table. What is this for? Are you going to get the contents via code for something? If not, why use a formfield? Oh.....never mind, I see. So you can get text IN, as the document must be protected to use the dropdowns.

[kidego365]

:bug::wot:bug: